CODEWITHSUNDEEP
databasems-accessasp-classic
Katherine Perotta
Katherine Perotta

Reputation: 125

ASP Classic and MSAccess Dates

I've entered dates into Access from ASP before and im using the same code...but it will not take the query no matter what. I've been working all day, maybe im just tired, but can somebody take a look at this for me?

This is a sample query the code generates:

INSERT INTO COMMENTS (FID,AUTHOR,DATE,COMMENT) VALUES ("6","John Doe",#4/15/2012#,"test comment")

Like I said, the template for the code came directly from somewhere else that works. If I remove the date from the query, it works fine. I've triple checked the DATE field is a DATE/TIME column and i've tried removing and re-creating it with no luck.

Would appreciate some help.

Upvotes: 0

Views: 2244

Answers (3)

grepsedawk
grepsedawk

Reputation: 3411

Here's what I got to work for me:

    strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("data.mdb")
    strSQL_Insert = "INSERT INTO COMMENTS (FID,AUTHOR,CDATE,COMMENT) VALUES ('6','John Doe','4/15/2012','test comment')"

   Set Conn=Server.CreateObject("ADODB.Connection")
        Conn.Open strDSNPath
            Conn.Execute strSQL_Insert
        Conn.Close
    Set Conn = Nothing

And here's the database: Download

Notes:

*I couldn't get it to work with the (") so I used (')

*The date has no hashtag (which may need fixing)

*Sorry it's so late, it took me like an hour and a half to figure it out, and it only ended up being "use single quotes"

Upvotes: 0

Nick
Nick

Reputation: 6025

You could try #MM-DD-YYYY#. It's also suggested to use YYYY-MM-DD, so you can try #YYYY-MM-DD# or even #'YYYY-MM-DD'#.

Upvotes: 0

Control Freak
Control Freak

Reputation: 13233

I haven't touched Access in a while, but a few queries you can try:

Query 1: Change DATE to [DATE] since DATE may be a common keyword used by Access.

    INSERT INTO COMMENTS 
    (FID,AUTHOR,[DATE],COMMENT) 
    VALUES 
    ("6","John Doe",#4/15/2012#,"test comment")

Query 2: Change #4/15/2012# to GetDate() (Date Stamp)

    INSERT INTO COMMENTS 
    (FID,AUTHOR,[DATE],COMMENT) 
    VALUES 
    ("6","John Doe",GETDATE(),"test comment")

Or maybe try a combination of them. Report back if either of these don't work.

Upvotes: 1

Related Questions