|
 |
asp_databases thread: select record set using date?
Message #1 by "peter" <ph@t...> on Tue, 24 Oct 2000 14:56:53 +0100
|
|
The following code is trying to select a rs using a date selected by the
user
strDate = Request.Form("selectdate")
Response.Write strDate 'prints out date ok
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
gives the error :
Microsoft JET Database Engine error '80040e14'
Division by zero
have tried various syntax forms to no avail as I know it must be something
with the date format, but ??, thanks in advance,
peter
Message #2 by "Ray Murphy" <raymondmurphy@c...> on Tue, 24 Oct 2000 16:19:25 +0100
|
|
Peter,
I'm presuming that you're using Access as your database - I don't use
Access at all, but I'm sure I recall reading somewhere that comparing
dates in Access requires you to put hashes around the date, as in
SELECT fieldvalue, somedate from sometable where
somedate between Between #1/1/99# And #12/12/99#
If this is the case, then you''ll need to amend your code accordingly ....
HTH.
Ray Murphy
www.goldsealdata.com
> The following code is trying to select a rs using a date selected by the
> user
>
> strDate = Request.Form("selectdate")
> Response.Write strDate 'prints out date ok
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open strConnect
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
>
> gives the error :
>
> Microsoft JET Database Engine error '80040e14'
> Division by zero
>
> have tried various syntax forms to no avail as I know it must be something
> with the date format, but ??, thanks in advance,
>
> peter
Message #3 by "peter" <ph@t...> on Tue, 24 Oct 2000 19:53:40 +0100
|
|
Thanks Ray,
It is an Access DB Im using at the moment although it will get upsized to
sql...I've tried
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= #strDate# " )
with various delimiters etc and generally get the following error.
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
cheers
peter
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 25 Oct 2000 13:16:58 +1000
|
|
http://www.adopenstatic.com/faq/80040e14.asp
or more specifically
http://www.adopenstatic.com/faq/80040e14.asp#scenario4
Cheers
Ken
----- Original Message -----
From: "peter" <ph@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, October 24, 2000 11:56 PM
Subject: [asp_databases] select record set using date?
> The following code is trying to select a rs using a date selected by the
> user
>
> strDate = Request.Form("selectdate")
> Response.Write strDate 'prints out date ok
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open strConnect
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
>
> gives the error :
>
> Microsoft JET Database Engine error '80040e14'
> Division by zero
>
> have tried various syntax forms to no avail as I know it must be something
> with the date format, but ??, thanks in advance,
>
> peter
Message #5 by "Dallas Martin" <dmartin@z...> on Tue, 24 Oct 2000 20:57:06 -0400
|
|
I think you have to enclose your date within quotes or pound signs
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=#" & strDate &
"#" )
OR
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate='" & strDate &
"'")
I believe that the divsion error is because Access is trying to calculate
the
requested date. This calculation uses a math function. Since your date
string
resolved to 0 or nothing, Access returns the error message.
----- Original Message -----
From: "peter" <ph@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, October 24, 2000 9:56 AM
Subject: [asp_databases] select record set using date?
> The following code is trying to select a rs using a date selected by the
> user
>
> strDate = Request.Form("selectdate")
> Response.Write strDate 'prints out date ok
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open strConnect
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
>
> gives the error :
>
> Microsoft JET Database Engine error '80040e14'
> Division by zero
>
> have tried various syntax forms to no avail as I know it must be something
> with the date format, but ??, thanks in advance,
>
> peter
Message #6 by "John Powell" <qu4gmire@h...> on Tue, 24 Oct 2000 22:20:10 -0500
|
|
Peter,
Instead of this:
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= #strDate# " )
try this:
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= #" & strDate &
"#" )
That should work for you. It is pretty much the same as the following bit of
VBA code from an Access DB I write/maintain (and I know this code works...):
WHERE ((([ALQ-184].DATE)>=#" & DateStart & "# AND"
Hope it works for you...
JP
----- Original Message -----
From: "peter" <ph@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, October 24, 2000 1:53 PM
Subject: [asp_databases] Re: select record set using date?
> Thanks Ray,
>
> It is an Access DB Im using at the moment although it will get upsized to
> sql...I've tried
>
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= #strDate# " )
>
> with various delimiters etc and generally get the following error.
>
> Microsoft JET Database Engine error '80040e07'
> Data type mismatch in criteria expression.
>
> cheers
>
> peter
>
Message #7 by "Chris Duke" <chrisduke@s...> on Tue, 24 Oct 2000 23:04:13 -0500
|
|
Peter,
Try using # # to delimit the date variable/value..
try this:
[instead of]
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
[do this]
Set oRS = oConn.Execute("SELECT * FROM News WHERE NewsDate=#" & strDate &
"#)"
[reason]
you have to delimit date values with a #, e.g: #dtmDate#
similar to having to delimit text values with ', e.g: 'txtSomeTextField'
Hope that helps!
Ch33rs,
Chris Duke
Dj/Producer/Developer/Designer/Human
<!--------// .EoF = True //--------->
-----Original Message-----
From: peter [mailto:ph@t...]
Sent: Tuesday, October 24, 2000 8:57 AM
To: ASP Databases
Subject: [asp_databases] select record set using date?
The following code is trying to select a rs using a date selected by the
user
strDate = Request.Form("selectdate")
Response.Write strDate 'prints out date ok
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
gives the error :
Microsoft JET Database Engine error '80040e14'
Division by zero
have tried various syntax forms to no avail as I know it must be something
with the date format, but ??, thanks in advance,
peter
Message #8 by "Holloway, Mike" <HollowayM@E...> on Wed, 25 Oct 2000 07:35:00 -0500
|
|
Try strDate = CStr(Request.Form("selectdate"))
> -----Original Message-----
> From: peter [SMTP:ph@t...]
> Sent: Tuesday, October 24, 2000 8:57 AM
> To: ASP Databases
> Subject: [asp_databases] select record set using date?
>
> The following code is trying to select a rs using a date selected by the
> user
>
> strDate = Request.Form("selectdate")
> Response.Write strDate 'prints out date ok
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open strConnect
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
>
> gives the error :
>
> Microsoft JET Database Engine error '80040e14'
> Division by zero
>
> have tried various syntax forms to no avail as I know it must be something
> with the date format, but ??, thanks in advance,
>
> peter
>
Message #9 by "Jason A. Greenfeld" <jgreenfeld@r...> on Wed, 25 Oct 2000 09:47:29 -0400
|
|
The reason why you are getting the error is because strDate is a string and
not an actual date.
Here are a few suggestions:
1.
If the field NewsDate is not a date field in the DB then change your select
statement to this.
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= '" & strDate &
"'")
2.
if the field NewsDate is a date field in the db, then you have to do this:
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate='" &
CDate(strDate) & "'" )
the CDate() function makes the string an actual date.
The reason why your statement wasn't working is because you were using
strDate as a number. When using string variables, they follow this format:
'" & string & "'
Numbers don't have to follow that method.
That should solve your problem!
Jason Greenfeld
Rubicon Technologies, Inc.
http://www.rubicontechnologies.com
-----Original Message-----
From: peter [mailto:ph@t...]
Sent: Tuesday, October 24, 2000 9:57 AM
To: ASP Databases
Subject: [asp_databases] select record set using date?
The following code is trying to select a rs using a date selected by the
user
strDate = Request.Form("selectdate")
Response.Write strDate 'prints out date ok
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
gives the error :
Microsoft JET Database Engine error '80040e14'
Division by zero
have tried various syntax forms to no avail as I know it must be something
with the date format, but ??, thanks in advance,
peter
Message #10 by "Nemesys70 Nemesys70" <nemesys70@h...> on Wed, 25 Oct 2000 20:35:08 GMT
|
|
I concurr with Ray.
>From: "Ray Murphy" <raymondmurphy@c...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: select record set using date?
>Date: Tue, 24 Oct 2000 16:19:25 +0100
>
>Peter,
>
>I'm presuming that you're using Access as your database - I don't use
>Access at all, but I'm sure I recall reading somewhere that comparing
>dates in Access requires you to put hashes around the date, as in
>
> SELECT fieldvalue, somedate from sometable where
> somedate between Between #1/1/99# And #12/12/99#
>
>If this is the case, then you''ll need to amend your code accordingly ....
>
>HTH.
>
>Ray Murphy
>www.goldsealdata.com
>
>
> > The following code is trying to select a rs using a date selected by the
> > user
> >
> > strDate = Request.Form("selectdate")
> > Response.Write strDate 'prints out date ok
> > Set oConn = Server.CreateObject("ADODB.Connection")
> > oConn.Open strConnect
> > Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate
>)
> >
> > gives the error :
> >
> > Microsoft JET Database Engine error '80040e14'
> > Division by zero
> >
> > have tried various syntax forms to no avail as I know it must be
>something
> > with the date format, but ??, thanks in advance,
> >
> > peter
>
Message #11 by "John Powell" <qu4gmire@h...> on Wed, 25 Oct 2000 17:18:32 -0500
|
|
Yeah, I forgot he was using a string & not a date var. Why not just dim the
var as a Date type?
If you do that, I know the code snippet I sent him will work...
----- Original Message -----
From: "Jason A. Greenfeld" <jgreenfeld@r...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, October 25, 2000 8:47 AM
Subject: [asp_databases] RE: select record set using date?
> The reason why you are getting the error is because strDate is a string
and
> not an actual date.
>
> Here are a few suggestions:
>
> 1.
> If the field NewsDate is not a date field in the DB then change your
select
> statement to this.
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate= '" & strDate &
> "'")
>
> 2.
> if the field NewsDate is a date field in the db, then you have to do this:
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate='" &
> CDate(strDate) & "'" )
> the CDate() function makes the string an actual date.
>
> The reason why your statement wasn't working is because you were using
> strDate as a number. When using string variables, they follow this format:
> '" & string & "'
>
> Numbers don't have to follow that method.
>
> That should solve your problem!
>
>
> Jason Greenfeld
> Rubicon Technologies, Inc.
> http://www.rubicontechnologies.com
>
>
>
> -----Original Message-----
> From: peter [mailto:ph@t...]
> Sent: Tuesday, October 24, 2000 9:57 AM
> To: ASP Databases
> Subject: [asp_databases] select record set using date?
>
>
> The following code is trying to select a rs using a date selected by the
> user
>
> strDate = Request.Form("selectdate")
> Response.Write strDate 'prints out date ok
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open strConnect
> Set rs = oConn.Execute("SELECT * FROM News WHERE NewsDate=" & strDate )
>
> gives the error :
>
> Microsoft JET Database Engine error '80040e14'
> Division by zero
>
> have tried various syntax forms to no avail as I know it must be something
> with the date format, but ??, thanks in advance,
>
> peter
>
Message #12 by "peter" <ph@t...> on Thu, 26 Oct 2000 00:57:18 +0100
|
|
Just to clear up this point
# "& strDate &" # is the code that solved it, (Note for Access)
thanks for the help,
cheers
peter
|
|
|
View Cart
