Database Date Problem

gmoney060 · August 5th, 2004, 10:20 PM

I am trying to select only recordsets from a database were the date field is the same as todays date

database:
| EventID | Date |

Code that i have problems with:

Code:

<%
    'Declares Variables
    Dim dbName2
    Dim ObjConn2
    Dim events

    'Sets Up Database Connection
    dbName2 = "/fpdb/events.mdb"
    ObjConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & Server.MapPath(dbName2)

    'Connects to Database
    Set events = Server.CreateObject("ADODB.RecordSet")
    events.Open "SELECT * FROM Events Where date="&date(),ObjConn2,1,2
    Do While Not events.EOF
    Response.Write("<div align=center><Strong>"&events("Time")&": </Strong>"&events("GameDesc")&"")
    events.MoveNext
    Loop
    events.close
    Set events = Nothing    
                              %>

gmoney060 · August 5th, 2004, 10:22 PM

I keep going to the EOF..... I should have a entry to display....

rajanikrishna · August 6th, 2004, 02:44 AM

Hello

events.Open "SELECT * FROM Events Where date=#" & date() & "#",ObjConn2,1,2

in Access you query a date by "#" before and after date

happygv · August 7th, 2004, 02:22 AM

Yes gmoney060, you should have an entry to display, only when you embed the date() with # on both side as suggested by RajaniKrishna.

It is always better to contruct your query on Access db first, and if that returns the expected result, then copy/paste that query in to your asp code (and make changes if required). That should always keep you out of trouble, than contructing sleect statements on the fly on asp page and searching for result set.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection