By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

INSERT Data from SELECT query

P: n/a
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
Aug 6 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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"
Aug 6 '08 #2

P: n/a
On Aug 6, 12:14*pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
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.
Aug 7 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.