|
 |
asp_databases thread: Using Form output to query a DB
Message #1 by alex@i... on Sat, 27 Jan 2001 23:49:41 -0000
|
|
Help me please
I have made a simple database with Two tables - a list of Drugs and the
date with which they were released.
I wish to submit via a form (post method) a date with which I want to
display a list of drugs released on that date.
I have converted the text output from the form into a date format (CDate)
and queried this against the database using an INNER JOIN for the two
tables and the WHERE command.
But when I run the script I get no data.?????
What really puzzles me is that if I input the same date directly into the
WHERE statement, the required data is displayed.
Further if I response.write the output from the form, it displays the
exact date as expected.
Further still if I change the output from the form to the word Date i.e.
via TypeName(), all data is displayed.
Therefore I have checked all my individual programming steps, but the end
result is still no data.......
Please please help, as you have guessed i am a novice
best wishes
alex jennings
actual code I am using
Datprint = CDate(Request.form("datefrom")) 'to convert output form
to a date format
Response.write Datprint 'just to prove I am getting the right date
Set rsprintlist = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT Drughistory INNER JOIN datesupply ON
Drughistory.Historyid = datesupply.Historyid " & _
"WHERE (((datesupply.date) = " & Datprint & "));" 'What the hell is wrong
with this line
Message #2 by Imar Spaanjaars <Imar@S...> on Sun, 28 Jan 2001 11:38:17 +0100
|
|
When you execute an SQL statement like this, you need to enclose your date
with either a # sign or a tick (').
Access databases like the # sign and SQL server likes the '. So try
something like this instead:
strSQL = "SELECT Drughistory INNER JOIN datesupply ON " & _
"Drughistory.Historyid = datesupply.Historyid " & _
"WHERE (datesupply.date = #" & Datprint & "#);"
HtH
Imar
At 11:49 PM 1/27/2001 +0000, you wrote:
>Help me please
>
>I have made a simple database with Two tables - a list of Drugs and the
>date with which they were released.
>
>I wish to submit via a form (post method) a date with which I want to
>display a list of drugs released on that date.
>
>I have converted the text output from the form into a date format (CDate)
>and queried this against the database using an INNER JOIN for the two
>tables and the WHERE command.
>
>But when I run the script I get no data.?????
>What really puzzles me is that if I input the same date directly into the
>WHERE statement, the required data is displayed.
>Further if I response.write the output from the form, it displays the
>exact date as expected.
>Further still if I change the output from the form to the word Date i.e.
>via TypeName(), all data is displayed.
>
>Therefore I have checked all my individual programming steps, but the end
>result is still no data.......
>
>Please please help, as you have guessed i am a novice
>
>best wishes
>alex jennings
>
>actual code I am using
>
>Datprint = CDate(Request.form("datefrom")) 'to convert output form
>to a date format
>
>Response.write Datprint 'just to prove I am getting the right date
>
>Set rsprintlist = Server.CreateObject("ADODB.Recordset")
>
>strSQL = "SELECT Drughistory INNER JOIN datesupply ON
>Drughistory.Historyid = datesupply.Historyid " & _
>
>"WHERE (((datesupply.date) = " & Datprint & "));" 'What the hell is wrong
>with this line
>
>
>
>
>---
>NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
>Visit EarthWeb for the latest in IT Management, Software Development,
>Web Development, Networking & Communications, and Hardware & Systems.
>Click on http://www.earthweb.com for FREE articles, tutorials,
>and discussions from the experts.
Message #3 by "Wally Burfine" <oopconsultant@h...> on Sun, 28 Jan 2001 04:32:33 -0000
|
|
You are converting the string date to a numeric representation of the date.
You really want to send the date in the query as a string date with the
appropiate delimiters. If it is Access delimiter is #, SQL Server will
automatically cast the string to a date.
"WHERE datesupply.date=#12/23/1998#" -- for Access
"WHERE datesupply.date=12/23/1998 -- for SQL Server
>From: alex@i...
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Using Form output to query a DB
>Date: Sat, 27 Jan 2001 23:49:41 -0000
>
>Help me please
>
>I have made a simple database with Two tables - a list of Drugs and the
>date with which they were released.
>
>I wish to submit via a form (post method) a date with which I want to
>display a list of drugs released on that date.
>
>I have converted the text output from the form into a date format (CDate)
>and queried this against the database using an INNER JOIN for the two
>tables and the WHERE command.
>
>But when I run the script I get no data.?????
>What really puzzles me is that if I input the same date directly into the
>WHERE statement, the required data is displayed.
>Further if I response.write the output from the form, it displays the
>exact date as expected.
>Further still if I change the output from the form to the word Date i.e.
>via TypeName(), all data is displayed.
>
>Therefore I have checked all my individual programming steps, but the end
>result is still no data.......
>
>Please please help, as you have guessed i am a novice
>
>best wishes
>alex jennings
>
>actual code I am using
>
>Datprint = CDate(Request.form("datefrom")) 'to convert output form
>to a date format
>
>Response.write Datprint 'just to prove I am getting the right date
>
>Set rsprintlist = Server.CreateObject("ADODB.Recordset")
>
>strSQL = "SELECT Drughistory INNER JOIN datesupply ON
>Drughistory.Historyid = datesupply.Historyid " & _
>
>"WHERE (((datesupply.date) = " & Datprint & "));" 'What the hell is
>wrong
>with this line
>
>
Message #4 by "Dallas Martin" <dmartin@z...> on Sat, 27 Jan 2001 22:17:27 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0010_01C088AE.EEBCEDE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
First, I think you need single quotes surrounding DatPrint.
Next, if you are using SQL Server, then open the query analyzer and
do a select on the date field from the table. You will notice that
the datetime field is expressed as the date plus the time,
i.e. 2000-12-16 11:34:56.400
This select statement will return at least the above row:
SELECT lastmodified from clients WHERE lastmodified >=3D '12/16/2000'
This select statement will NOT return a single row:
SELECT lastmodified from clients WHERE lastmodified =3D "12/16/2000"
This select statement will NOT return at a single row:
SELECT lastmodified from clients WHERE lastmodified LIKE "12/16/2000%"
This select statement will return at least the above row
SELECT lastmodified from clients WHERE lastmodified BETWEEN "12/16/2000"
AND "12/17/2000"
I hope I have pointed you in the right direction. As you can see date
handling in SQL can be tricky.
Cheers,
Dallas Martin
----- Original Message -----
From: <alex@i...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, January 27, 2001 6:49 PM
Subject: [asp_databases] Using Form output to query a DB
> Help me please
>
> I have made a simple database with Two tables - a list of Drugs and
the
> date with which they were released.
>
> I wish to submit via a form (post method) a date with which I want to
> display a list of drugs released on that date.
>
> I have converted the text output from the form into a date format
(CDate)
> and queried this against the database using an INNER JOIN for the two
> tables and the WHERE command.
>
> But when I run the script I get no data.?????
> What really puzzles me is that if I input the same date directly into
the
> WHERE statement, the required data is displayed.
> Further if I response.write the output from the form, it displays the
> exact date as expected.
> Further still if I change the output from the form to the word Date
i.e.
> via TypeName(), all data is displayed.
>
> Therefore I have checked all my individual programming steps, but the
end
> result is still no data.......
>
> Please please help, as you have guessed i am a novice
>
> best wishes
> alex jennings
>
> actual code I am using
>
> Datprint =3D CDate(Request.form("datefrom")) 'to convert
output form
> to a date format
>
> Response.write Datprint 'just to prove I am getting the right date
>
> Set rsprintlist =3D Server.CreateObject("ADODB.Recordset")
>
> strSQL =3D "SELECT Drughistory INNER JOIN datesupply ON
> Drughistory.Historyid =3D datesupply.Historyid " & _
>
> "WHERE (((datesupply.date) =3D " & Datprint & "));" 'What the
hell is wrong
> with this line
>
>
>
>
> ---
> NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
> Visit EarthWeb for the latest in IT Management, Software Development,
> Web Development, Networking & Communications, and Hardware & Systems.
> Click on http://www.earthweb.com for FREE articles, tutorials,
> and discussions from the experts.
$subst('Email.Unsub')
>
|
|
|
View Cart
