OF & EOF error

[Herriot]

I am learning ASP and am messing about with a database that contains Menu items for a cafe (Main Courses etc).

I have managed to display all of the database contents (currently four lines of data) which is simply a table that has Date (date/Time); MainCourse (Text); Vegetable (Text); Fruit (Text); Dessert (text) as fields. The first record contains todays date (currently 03/11/2002) followed by three other consecutive days.

However when I want to view just todays date (currently 03/11/2002) I get an error.

Here is the code:
<%
Dim Connect, OnMenu, Today, Query,
Set Connect = Server.createObject(&quot;ADODB.Connection&quot
Connect.open &quot;Menu&quot;


Today = CStr(Date)
Query = &quot;SELECT * FROM Menu WHERE Date = #&quot; & Today & &quot;#&quot;
Set OnMenu = Connect.Execute(Query)
%>

<Font Size=5>Today:<%=OnMenu(&quot;Date&quot%></Font><br>
Main Course:<%=OnMenu(&quot;MainCourse&quot%></Font><br>
Vegetables:<%=OnMenu(&quot;Vegetable&quot%></Font><br>
Fruit:<%=OnMenu(&quot;Fruit&quot%></Font><br>
Dessert:<%=OnMenu(&quot;Dessert&quot%></Font><br>



When run I get this

Time:

...as I would expect but then this is followed by the error...

ADODB.Field error '80020009'

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

?


I am guessing that this means that the data with todays date assigned to it in the database cannot be found.

When using <%=Date%> todays date IS displayed in the browser and there IS a date in the database table with the exact same date and in the same format.
Example:
the <%=Date%> displays 03/11/2002 and the data in the Date field in the database is also 03/11/2002

Thanks in advance for any help you can give me to correct this problem.

Peter

 

[Tarwn]

The problem here is that frtonpage is not understanding your date formatting (the default format coming from Today).
Try: &quot;SELECT * FROM Menu WHERE Date = #&quot; & FormatDateTime(Today,2) & &quot;#&quot;

Basically the default format is a long date long time format. So access was most likely trying to match midnight on todays date (midnight because you only asked for date, not time). The format I used abovwe is the short date format. Since it is just being sent a date then access should be able to match it against the value you have in the database.

-Tarwn
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[Herriot]

Thanks for your reply TARWN. However it didn't work :-(

I think that it may have something to do with checking EOF and BOF using an IF...then...(else)...end if but what goes between these I have no idea. I have tried various things to no avail.

Thanks anyway, I appreciate your time and effort.

Peter

 

[snowboardr]

I believe your problem is in your database, you have your column named &quot;date&quot; which is a reserved word by sql I would suggest staying away from anything that is a word, and if you have a string, put strTHE_WORD, so you don't get any reserved words there too. So change your column name and give it a try.

<%
Dim Connect, OnMenu, strToday, sqlQuery
Set Connect = Server.createObject(&quot;ADODB.Connection&quot
Connect.open &quot;Menu&quot;

strToday = Date

sqlQuery= &quot;SELECT * FROM Menu WHERE Date = #&quot; & strToday & &quot;#&quot;
Set OnMenu = Connect.Execute(sqlQuery)

If onMenu.eof then
Response.write &quot;No record found.&quot;
Else
%>
<Font Size=5>Today:<%=OnMenu(&quot;Date&quot%></Font><br>
Main Course:<%=OnMenu(&quot;MainCourse&quot%></Font><br>
Vegetables:<%=OnMenu(&quot;Vegetable&quot%></Font><br>
Fruit:<%=OnMenu(&quot;Fruit&quot%></Font><br>
Dessert:<%=OnMenu(&quot;Dessert&quot%></Font><br>

<% End If %> www.vzio.com
ASP WEB DEVELOPMENT

 

[Herriot]

Sorry snowboardr that didnt work either but I get your point with regard to naming. I don't get the BOF and EOF error but I get &quot;Records not found&quot;.

I can assure you that the data is in the database.

Thanks anyway.

 

[snowboardr]

sqlQuery= &quot;SELECT * FROM Menu WHERE db_Date = '&quot; & strToday & &quot;'&quot;

I would suggest trying different ways other then the hash value, and you did change the column name date correct?

You could also try the like statement. Or you could make your date column a text type instead of a date type, this is what I usually do and have no problems selecting the correct date.

- Jason www.vzio.com
ASP WEB DEVELOPMENT

 

[Herriot]

Snowboardr...
Thanks for your suggestions. It turned out to be the hash values. I changed them to a ' and it worked . I went back to the original asp page and tried that and it worked there too. I take your points about namin and will build that into any future programming.

Thanks for this. I am at your web page too which is good.
Thanks also to TARWN.

Peter