473,594 Members | 2,713 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 15461
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*******@yaho o.com> wrote in message
news:ek******** ******@tk2msftn gp13.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******** ******@TK2MSFTN GP09.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*******@yaho o.com> wrote in message
news:ek******** ******@tk2msftn gp13.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
2087
by: Steven Scaife | last post by:
I have decided to re-write the intranet site i created over a year ago. The coding is pretty awful and hard to read cos I made the mistake of not putting comments in or putting crappy comments in with the code. So I have decided to try and make things neater by grouping it into subs. So i have a sub to create the variables, a sub for the connection etc. Only problem is when i call my subs, it says that the variable is undefined. If i...
4
1964
by: RelaxoRy | last post by:
I have 2 screen sizes I want to accomodate for. screen.width <= 800 and > 800. This is because If it's one or the other, I want a different set of menu graphics, and to set variables for my javascript menus to popup in the right position. So do I write a function ... ....get the screen.width fine, and break off into a loop, setup some variables. But then I want to use these in the HTML page similar to as if I would ASP <%=tWidth%>. ...
12
1454
by: Mal Ice | last post by:
I am creating an initial index.htm page on which I show some disclaimers and introduction information. In the head section I have Javascript which determines the screen resolution of the client. I assign values to two variables to assign the width and height. (my_width and my_height). I have a Form button which is clicked on agreeing to the disclaimers. On clicking the form button I want to direct the user (open a new window) to a...
0
1362
by: Jason Steeves | last post by:
I have one .aspx form that my users fill out and this then takes that information and populates a second .aspx form via session variables. I need to screen scrape the second .aspx form and e-mail that off. I have figured out how to do the screen scrape but it e-mails a blank form with none of the session variables set. Is there a way to screen scrapte the second .aspx form with the variables set from the first .aspx form? Thanks in...
3
61040
by: Sean | last post by:
Have you ever wanted to add the great features inherent in Google Maps? Here is how you do it. ============== == STEP ONE == ============== Create a new MS Access form called frmGoogleMap. Size the form to your liking...
2
1801
by: Les Peabody | last post by:
Hello. I'm a rookie ASP VBScripter and am having a difficult time scripting the following scenario: I have an index.asp file that has a multi-line text box and a button of type button. When the button is clicked a window is spawned with change.asp as its source. There is a form with a few text boxes and with a button of type submit and a cancel button of type button that just closes the window. What I want to happen is I want the...
8
1658
by: Jeff Higgins | last post by:
Hi, In the variable "anchor" I would like to, (I think), use the value of the variable "text" in the predicate of the select expression. Is this possible? Is there a better way of doing what I think I want to do?
0
2077
by: | last post by:
I'm being driven nuts by what I'm sure is a simple solution, I have to pass some user and company id variables between 2 pages and on the second page use the params in a SQL query to list all items for that company. I set up the pages simply at first and could see the variables were being passed. When I added my gridview and sqldatasource nothing happened and it looks like the datasource is not picking up the session variable and I'm...
6
2358
by: trbjr | last post by:
Hello experts, I want to describe a project design and then ask some questions at the end. There is no code in this discussion, just symbols to illustrate an idea. Let S stand for a screen or form. I want to capture data as answers to questions presented to a user thru a series of screens. Since there are many user questions, I will ask them thru a series of screens. Thus, let S1, S2, S3, ... Sn
0
7946
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
7876
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8372
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8003
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5408
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3897
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2385
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1478
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1210
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.