469,361 Members | 2,429 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,361 developers. It's quick & easy.

ASP SQL - using variables in SQL select screen

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?? :(

--
- Ed
Jul 19 '05 #1
4 15307
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 wrote:
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?? :(

Jul 19 '05 #2
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

"Bob Barrows" <re*******@yahoo.com> wrote in message
news:ek**************@tk2msftngp13.phx.gbl...
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 wrote:
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?? :(


Jul 19 '05 #3
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

--
- Ed
"Ed Garcia" <ed@askme.com> wrote in message
news:Ov**************@TK2MSFTNGP09.phx.gbl...
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

"Bob Barrows" <re*******@yahoo.com> wrote in message
news:ek**************@tk2msftngp13.phx.gbl...
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 wrote:
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?? :(



Jul 19 '05 #4
In article <eF*************@tk2msftngp13.phx.gbl>, ed@askme.com says...
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

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Steven Scaife | last post: by
reply views Thread by Jason Steeves | last post: by
2 posts views Thread by Les Peabody | last post: by
8 posts views Thread by Jeff Higgins | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.