Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unanswered: Insert to date/time field

    I've written an asp.net app, and one of the features I'd like to use is timestamping. I'd like to timestamp each record when it is created. Not just a date, but date/time

    My thought was to use an insert statement such as:

    strSQL = "Insert into TABLE (TableKey, Timestamp) values (" & SeqNum.text & "," & now() & ")"

    which returns the statement:
    "Insert into TABLE (TableKey, Timestamp) values (7853,10/31/2008 11:48:23 AM)"

    It syntaxes out.

    I tried surrounding the time/date with # as you would do if it was just the date portion. Again, it doesn't work.

    How do you get a date/time to insert?

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Where is the table being stored? I mean, is it an Access database or SQL Server, etc.

    You might try delimiting the date with a single quote - I believe that is what SQL server likes (probably wrong...it's been a while).

    C

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    or use #

    strSQL = "Insert into TABLE (TableKey, Timestamp) values (" & SeqNum.text & ",#" & now() & "#)"
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect the original problem was not quoting the date/time
    eg
    "Insert into TABLE (TableKey, Timestamp) values (7853," & chr(34) & "10/31/2008 11:48:23 AM" & chr(34)"

    AS Pkstomry says Access/JET usuall requries the hash symbol # either side of the value to expressly tell the SQL engine its a date
    "Insert into TABLE (TableKey, Timestamp) values (7853," & chr(34) & "#10/31/2008 11:48:23 AM#" & chr(34)"

    using the now() value will insert the systems current time.. but bear in mind if you are on a multi user system this means the current time from the PC thats inserting the record. which unless you are running some sort of date/time synchronisation software may not be the 'true' current time, and it may not be the same as other computers. so there is a risk that one computer may claim its 10:23:15, other PC's on the same network may thing it 10:23:45 or ANY other valid datetime as its picking up the time from that PC's OS. as a user can change the time at will (unless the network trolls have tied that down) you cannot rely on the time being precise. if all you want is an indication then thats fine, but don't expect to rely on it if you need the time for legal purposes.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Posts
    9
    Thanks for all the answers.

    It is an msaccess database being store on a webserver.

    Being that it is ASP.NET code, I believe the date/time will be based on the Webserver's time since ASP.NET code is run at the server and then rendered to the client. (No need to worry about different clock settings on each user's machine.)

    I have tried wrapping the date/time with #'s, and while I have excellent results with this method when just inserting a date, I have syntaxed out when trying to use a full date/time.

    So either I've messed up somewhere else in my insert statement or wrapping a fulle date/time with #'s is not the answer.

    I'm still looking for a solution.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Salthead
    I'm still looking for a solution.
    please echo out the exact query statement being sent to Access

    not the asp version with variables, but the pure SQL after the asp variables have been substituted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2008
    Posts
    8
    Perhaps you should use a parameter query instead of building the full SQL statement?

    You're SQL would look something like this:

    Code:
    PARAMETERS TableKey Long, [TimeStamp] DateTime;
    INSERT INTO Table1 ( TableKey, [TimeStamp] )
    SELECT [TableKey] AS Expr1, [TimeStamp] AS Expr2;
    And then you would pass along the tablekey and timestamp values as parameters (however you do that in ASP.net). This would aviod the whole formatting issue and you gain the benefit of avoiding SQL injection security risks if you ues this method througout.

    In VBA (Access 2003), it would look something like:

    Code:
    Dim qdInsert as QueryDef
    Dim pTableKey as Parameter
    Dim pTimestamp as Parameter
    
    set qdInsert = CurrentDB.CreateQueryDef( .. SQL from above ..)  ' Or use a prebuild QueryDef object
    set pTableKey = qdInsert.Parameters!tableKey
    set pTimestamp = qdInsert.Parameters!timeStamp
    
    pTablekey = 12334
    pTimestamp = Now
    
    qdInsert.Execute
    Not sure how the syntax would change for ASP, but I suspect it won't be too different.

    Hope this helps!

  8. #8
    Join Date
    Apr 2004
    Posts
    9
    Here's an exact copy of the SQL statement when I use #'s as delimitters:

    Insert into tblReq (ReqNum, TimeStamp) values (7858,#11/7/2008 8:37:08 AM#)

    ReqNum is a number, and Timestamp is a Date/Time.

    An error returns "Syntax error in INSERT INTO statement."

    The following statement works fine:
    Insert into tblReq (ReqNum) values (7858). So it definitely is a problem with the Date/Time part of the statement

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    weird, that should work

    is there perhaps a space after AM? could you do it without the AM?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    should the date not be terminated by a double quote/speechmark?


    Insert into tblReq (ReqNum, TimeStamp) values (7858,"#11/7/2008 8:37:08 AM#")
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, doublequotes around octothorpes?

    the octothorpes are the access date string delimiters, i don't see why additional delimiters are required
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    They aren't. And I think TimeStamp is a reserved word; you might like to try enclosing that in square brackets... whatever they're called... [TimeStamp]
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •