Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > INSERT Data from SELECT query

Reply
Thread Tools

INSERT Data from SELECT query

 
 
paulmitchell507
Guest
Posts: n/a
 
      08-06-2008
I think I am attempting a simple procedure but I just can't figure out
the correct syntax. My asp (classic) page runs a SELECT query to
obtain dates and ID's from 2 tables

uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
(((calendar.cal_Date) Between [holiday_tbl].[startdate] And
[holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

This works fine.

What I would like to do next is insert the returned values (cal_date)
and (holiday_ID) into a seperate table called holiday_dates. I am
happy to insert the results one recordset at a time, but I don't know
how to do it. I know that uSQL is returning results

'Loop until we've hit the EOF
Do Until objRS.EOF = True
response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
objRS.movenext
Loop

I would appreciate any help
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-06-2008
paulmitchell507 wrote:
> I think I am attempting a simple procedure but I just can't figure out
> the correct syntax. My asp (classic) page runs a SELECT query to
> obtain dates and ID's from 2 tables
>
> uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
> (((calendar.cal_Date) Between [holiday_tbl].[startdate] And
> [holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID
>
> This works fine.


But it's non-standard syntax. Better would be (and not the use of table
aliases:
uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=" & Staff_ID

Better yet would be the use of a parameter token to pass the staff_id value.
I would do it like this:

uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
Set objRS = cmd.Execute(,array(Staff_ID))
etc.

>
> What I would like to do next is insert the returned values (cal_date)
> and (holiday_ID) into a seperate table called holiday_dates. I am
> happy to insert the results one recordset at a time, but I don't know
> how to do it. I know that uSQL is returning results
>
> 'Loop until we've hit the EOF
> Do Until objRS.EOF = True
> response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
> objRS.movenext
> Loop
>
> I would appreciate any help


You left out some information that would have been helpful:
-the datatypes of the cal_date and holiday_ID fields
-the names and datatypes of the fields you want to insert these values into

You also left out some information that should be provided with every
database-related question you ask (think about creating a template with this
info):
database type and version
The parentheses in your WHERE clause lead me to believe you used the Access
Query Builder to construct this query, but this is just a guess - please
don't make us guess.

Is this holiday_dates table in the same database? if so, there is absolutely
no need to open and loop through a recordset here. a simple INSERT...SELECT
statement will do this job nicely. Here is the sql statement:

iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
"SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
cmd.Execute ,array(Staff_ID)

As to why parameter tokens are better than dynamic sql, here is my canned
reply:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

SQL Server:

http://groups.google.com/group/micro...9dc1701?hl=en&




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
paulmitchell507
Guest
Posts: n/a
 
      08-07-2008
On Aug 6, 12:14*pm, "Bob Barrows [MVP]" <(E-Mail Removed)>
wrote:
> paulmitchell507 wrote:
> > I think I am attempting a simple procedure but I just can't figure out
> > the correct syntax. *My asp (classic) page runs a SELECT query to
> > obtain dates and ID's from 2 tables

>
> > uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
> > (((calendar.cal_Date) Between [holiday_tbl].[startdate] And
> > [holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

>
> > This works fine.

>
> But it's non-standard syntax. Better would be (and not the use of table
> aliases:
> uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
> "INNER JOIN *holiday_tbl As h ON " & _
> "c.cal_Date Between *h.startdate AND h.enddate " & _
> "WHERE Email_sent=0 AND Staff_ID=" & Staff_ID
>
> Better yet would be the use of a parameter token to pass the staff_id value.
> I would do it like this:
>
> uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
> "INNER JOIN *holiday_tbl As h ON " & _
> "c.cal_Date Between *h.startdate AND h.enddate " & _
> "WHERE Email_sent=0 AND Staff_ID=?"
>
> dim cmd:set cmd=createobject("adodb.command")
> cmd.CommandText = uSQL
> set cmd.ActiveConnection = YourOpenConnectionObject
> cmd.CommandType = 1 'adCmdText
> Set objRS = cmd.Execute(,array(Staff_ID))
> etc.
>
>
>
> > What I would like to do next is insert the returned values (cal_date)
> > and (holiday_ID) into a seperate table called holiday_dates. *I am
> > happy to insert the results one recordset at a time, but I don't know
> > how to do it. *I know that uSQL is returning results

>
> > 'Loop until we've hit the EOF
> > Do Until objRS.EOF = True
> > response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
> > objRS.movenext
> > Loop

>
> > I would appreciate any help

>
> You left out some information that would have been helpful:
> -the datatypes of the cal_date and holiday_ID *fields
> -the names and datatypes of the fields you want to insert these values into
>
> You also left out some information that should be provided with every
> database-related question you ask (think about creating a template with this
> info):
> database type and version
> The parentheses in your WHERE clause lead me to believe you used the Access
> Query Builder to construct this query, but this is just a guess - please
> don't make us guess.
>
> Is this holiday_dates table in the same database? if so, there is absolutely
> no need to open and loop through a recordset here. a simple INSERT...SELECT
> statement will do this job nicely. Here is the sql statement:
>
> iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
> "SELECT cal_date, holiday_ID from Calendar As c" & _
> "INNER JOIN *holiday_tbl As h ON " & _
> "c.cal_Date Between *h.startdate AND h.enddate " & _
> "WHERE Email_sent=0 AND Staff_ID=?"
>
> dim cmd:set cmd=createobject("adodb.command")
> cmd.CommandText = uSQL
> set cmd.ActiveConnection = YourOpenConnectionObject
> cmd.CommandType = 1 'adCmdText
> cmd.Execute ,array(Staff_ID)
>
> As to why parameter tokens are better than dynamic sql, here is my canned
> reply:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:http://mvp.unixwiz.net/techtips/sql-....aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:http://groups-beta.google.com/group/...etserver.asp.d...
>
> Personally, I prefer using stored procedures, or saved parameter queries
> as
> they are known in Access:
>
> Access:http://www.google.com/groups?hl=en&l...8&selm=e6lLVvO...
>
> http://groups.google.com/groups?hl=e...ff=1&selm=eHYx...
>
> SQL Server:
>
> http://groups.google.com/group/micro...ver.asp.genera...
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"


Wow! what a fantastic reply.
I will follow your template for future posts..of which there will be
many!
I have taken your advice and re-coded all me asp pages to use saved
parameter queries.
You guessed correctly, I have an access 2k database.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select query data type mismatch eyoung1@uiuc.edu ASP General 3 07-02-2008 03:44 PM
How to trace the Insert Query of Sql Data Source ? Luqman ASP .Net 1 07-10-2007 07:17 AM
IO::Select::select() says no readable data even if there are jari.eskelinen@iki.fi Perl Misc 13 08-28-2006 11:16 AM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM
DataAdapter Insert data into a query Paul ASP .Net 0 06-30-2003 06:47 PM



Advertisments