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

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:[color=blue]
> 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?? :([/color]

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" <reb_01501@yahoo.com> wrote in message
news:ekDFSoQXDHA.3444@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
statement[color=blue]
> dynamically. It's easy to pass parameters to a procedure. In this case,[/color]
you[color=blue]
> 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:[color=green]
> > 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?? :([/color]
>
>[/color]

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:OvA0V4QXDHA.2516@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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" <reb_01501@yahoo.com> wrote in message
> news:ekDFSoQXDHA.3444@tk2msftngp13.phx.gbl...[color=green]
> > 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[/color][/color]
statement.[color=blue][color=green]
> >
> > Show us the result of the response.write if you still can't figure it[/color][/color]
out.[color=blue][color=green]
> >
> > You may want to use a stored procedure instead of building the sql[/color]
> statement[color=green]
> > dynamically. It's easy to pass parameters to a procedure. In this case,[/color]
> you[color=green]
> > 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:[color=darkred]
> > > 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?? :([/color]
> >
> >[/color]
>
>[/color]

In article <eFasogQXDHA.416@tk2msftngp13.phx.gbl>, ed@askme.com says...
x> I have a form that sends an ASP page the data to use for this
string...[color=blue]
> using values for "startdate", "enddate" and "lookfor" varibables...[/color]
[color=blue]
> 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?? :([/color]

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

-- Rick