Ask a Question related to ASP, Design and Development.
-
Ed Garcia #1
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
Ed Garcia Guest
-
video full screen and screen savers
Why does full-screen mode not disable the system screen saver in the flash player but does so for AIR? In the documentation it says "For AIR... -
passing variables from 'select' element in a form
Hi, I have 2 'select' element in a form. One is populated with a list of names and the other is empty Users can select 1 or more names from the... -
passing variables from select elements in form
I have an interesting problem that I'm trying to solve. I have 2 'select' element in a form. One is populated with a list of names and the other... -
Asp variables in sql select
Hello, Is such select sql statment allowed? " SELECT * FROM Photo_SubCategory Where (Status = 1 And Session("id") <> "") or (Status = 0 And... -
SELECT DISTINCT + ORDER BY gives ERROR 145: ORDER BY items mustappear in the select list if SELECT DISTINCT is specified.
Dan, You should be able to do this: SELECT Id, FaxID, ReceivedTime, Pages FROM ( SELECT DISTINCT .Id AS Id, -
Bob Barrows #2 Re: ASP SQL - using variables in SQL select screen
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?? :(
Bob Barrows Guest
-
Ed Garcia #3 Re: ASP SQL - using variables in SQL select screen
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...statement> 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 sqlyou> dynamically. It's easy to pass parameters to a procedure. In this case,> 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?? :(
>
Ed Garcia Guest
-
Ed Garcia #4 Re: ASP SQL - using variables in SQL select screen
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...statement.> 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...> > 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 openout.> >
> > Show us the result of the response.write if you still can't figure it> statement> >
> > You may want to use a stored procedure instead of building the sql> you> > dynamically. It's easy to pass parameters to a procedure. In this case,>> > 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?? :(
> >
>
Ed Garcia Guest
-
Guinness Mann #5 Re: ASP SQL - using variables in SQL select screen
In article <eFasogQXDHA.416@tk2msftngp13.phx.gbl>, [email]ed@askme.com[/email] 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 you're accessing the Jet engine, you need to surround your dates with> 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?? :(
#-signs, or so I've heard.
-- Rick
Guinness Mann Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
