SQL, Dates, Results Wiz? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

akreci -> SQL, Dates, Results Wiz? (2/28/2001 20:19:00)

This is the issue I am using frontpage results wiz to get data from my access database I use this custom SQL to access the data as you can see I have set 2 criteria:

SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = ::Aval_Date:: ORDER BY product_var ASC

MFG which is the manufactur set in the data base as text and Aval_Date which is a date and is in date/time format. The problem is when a user fills in the criteria feilds the results page makes the users put # sings around the date there for I guess qualifing it as a date or number field,if they are not there then no results are found. is there a way to eliminate the # signs or at lease hide them from the users aside from setting the format in the data base as text.

Thanks for your time

[This message has been edited by akreci (edited 02-28-2001).]




Spooky -> RE: SQL, Dates, Results Wiz? (2/28/2001 14:53:00)

Sure, change the SQL to :

SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = #::Aval_Date::# ORDER BY product_var ASC

------------------
Spooky
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!




akreci -> RE: SQL, Dates, Results Wiz? (2/28/2001 15:48:00)

I tried this and many other ways of concatenating the # signs into the query and this is what I get when I try to verify the query.
The server encountered an error while verifying the query:
Server error: Unable to retrieve schema information from the query:

SELECT * FROM mfgqty3 WHERE (MFG = '1' AND Aval_Date = #2#) ORDER BY product_var ASC

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '(MFG = '1' AND Aval_Date = #2#)'.


quote:
Originally posted by Spooky:
Sure, change the SQL to :

SELECT * FROM mfgqty3 WHERE (MFG = '::MFG::' AND Aval_Date = #::Aval_Date::# ORDER BY product_var ASC




Spooky -> RE: SQL, Dates, Results Wiz? (2/28/2001 18:52:00)

Hmm, that SQL check is a PITA

Change the # back to ' and you should have more success (otherwise modify the gray code in html view)




Guest -> RE: SQL, Dates, Results Wiz? (3/1/2001 23:28:00)

Why don't you add the #'s the value you grab from the text box before you pass it to your SQL string using some similar to the script below. That way the users never have to enter them and the SQL string will be happy too.

<%txtDate.value = CDate("#"&textDate.value&"#")%>




akreci -> RE: SQL, Dates, Results Wiz? (3/2/2001 17:08:00)

I want to thank both of you for all the help with this problem. With a little more fooling around I got it to work using a little bit of both ideas. I all I had to do was put the #'s in the sql like we first figured but I was using the GUI to much and not looking at the actual code. When I put my SQL in the GUI it could not update the bots so I needed to make some changes in the webbot code. I want to give this forum props the replies were quick and very helpful I will use it again!!!!! Good luck to all...

quote:
Originally posted by EleciaE:
Why don't you add the #'s the value you grab from the text box before you pass it to your SQL string using some similar to the script below. That way the users never have to enter them and the SQL string will be happy too.

<%txtDate.value = CDate("#"&textDate.value&"#")%>



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125