Retrieve date value issue

Forum|Forum|19 years ago
March 3, 2007
3 replies
236 views

Hi all,

I am trying to retrieve a count of booking entries made 30 days ago, below
is the end of the query I am having problems with.

dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_form.TimeOfBooking")

When I use the = sign the error reads

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

for the entry on the page

(rsCash30("CountOfTimeOfBooking"))

But I know the record exists, also the query works when I use the signs < or
> or <> all works ok but not with the = ??

Full SQL query reads

Set rsCash30 = myConn.Execute("SELECT Count(dbo.booking_form.TimeOfBooking)
AS CountOfTimeOfBooking FROM dbo.booking_form WHERE
dbo.booking_form.allocated = 'Completed' AND dbo.booking_form.CustomerID
='0' AND dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_form.TimeOfBooking")

Regards

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

Server side applications

This topic has been closed for replies.

C

Creativ_Technologies

Inspiring

Since you are using MS SQL you would not use the # signs. Also I notice that the customerID=0 in the WHERE clause. Make sure there is a user with the ID of 0 in the DB, normally when you use the int increment the first value is 1, and you have the customerID = '0' indecating that it is a strring value. Double check your data type. I am guessing your customerID is an int so remove the quotes from around the value.

N

Newsgroup_UserAuthor

Inspiring

Sorry I was abbreviating I am using SQL 2000, cant test as SQL statement is
developed outside dreamweaver in this case, what is the issue with = and
Date/Time? how would it be surrounded by #?

Thanks in advance

Simon

"envision3d" <webforumsuser@macromedia.com> wrote in message
news:escl82$ous$1@forums.macromedia.com...
> Does the recordset return any results when you click the "Test" button in
the
> recordset dialog in DW?
>
> I notice your syntax is wrong for this:
(rsCash30("CountOfTimeOfBooking"))
>
> It should be: rsCash30.Fields.Item("CountOfTimeOfBooking").Value
>
> and if if within html it needs to be surrounded by <%= %>
>
> I also think you may be getting the error using "=" sign because the
Date/Time
> datatype is seen as a string, so if you are using an Access database it
needs
> to be surrounded by # signs.
>
>

C

Creativ_Technologies

Inspiring

Does the recordset return any results when you click the "Test" button in the recordset dialog in DW?

I notice your syntax is wrong for this: (rsCash30("CountOfTimeOfBooking"))

It should be: rsCash30.Fields.Item("CountOfTimeOfBooking").Value

and if if within html it needs to be surrounded by <%= %>

I also think you may be getting the error using "=" sign because the Date/Time datatype is seen as a string, so if you are using an Access database it needs to be surrounded by # signs.

I just noticed you are using MS SQL as your DB. So try placing single quotes around your date.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded