ASP SQL - using variables in SQL select screen

[Ed Garcia]

I have a form that sends an ASP page the data to use for this string...
using values for "startdate", "enddate" and "lookfor" varibables...

rs.Open "SELECT * FROM TABLE WHERE dateadded >= ' & startdate & ' AND
dateadded < ' & enddate & ' AND sendemail = ' & lookfor & '"

well it works perfectly when I have the actual dates or the actual value of
"lookfor" like this example

rs.Open "SELECT * FROM TABLE WHERE dateadded >= ('6/15/2003 2:12:03 PM')
AND dateadded < ('8/15/2003 2:12:03 PM') AND sendemail = 'Y'"


but when I use the top string with variables instead of values i get error:
Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting datetime from character string.

/newsstats/reports.asp, line 23


If I use response.Writes before the error happens i get what seem to be
correct values for the variables?!?!

('7/29/2003 12:00:00 AM')
('8/4/2003 12:00:00 AM')
'Y'

what am I doing wrong??

 

[Bob Barrows]

You need to response.write the entire sql statement to be able to debug
this. Always assign your SQL statement to a variable so you can
response.write it. Then use the variable in your recordset open statement.

Show us the result of the response.write if you still can't figure it out.

You may want to use a stored procedure instead of building the sql statement
dynamically. It's easy to pass parameters to a procedure. In this case, you
could run a script like this to create the procedure:

CREATE PROCEDURE GetRecords (
@start datetime,
@end datetime,
@lookfor char(1) --just a guess
) As
Select col1, ...,colN FROM Table
WHERE dateadded >= @start AND
dateadded < @end AND
sendemail = @lookfor

Then in ASP, do this:
conn.GetRecords cdate(startdate), cdate(enddate), _
lookfor, rs

Bob Barrows

 

[Ed Garcia]

I swear I just heard an airplane fly above my head... did I mentioned I am
relatively new at this?

Ok let me thinker that you gave me a bit and try it...

 

[Ed Garcia]

thanks for the tip of making this a complete sql statement before tyring to
actually get it... it worked after I did this...

selectstring = "SELECT * FROM TABLE WHERE dateadded >= " + startdate + "
AND lastvisit < " + enddate + " AND sendemail = '" + lookfor + "'"

rs.Open (selectstring), sConnString, 3

 

[Guinness Mann]

x> I have a form that sends an ASP page the data to use for this
string...

using values for "startdate", "enddate" and "lookfor" varibables...

If I use response.Writes before the error happens i get what seem to be
correct values for the variables?!?!

('7/29/2003 12:00:00 AM')
('8/4/2003 12:00:00 AM')
'Y'

what am I doing wrong??

If you're accessing the Jet engine, you need to surround your dates with
#-signs, or so I've heard.

-- Rick