paulmitchell507 said:
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
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....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/microsoft.public.inetserver.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&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvO...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYx...
SQL Server:
http://groups.google.com/group/microsoft.public.inetserver.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"
