none
How do you search an mdb file in C# using DateTime parameter? RRS feed

  • Question

  • I've created an Access database file via code, with one of the fields being of type Date/Time, and naturally enough, it stores the date and time of the record, and is in the format of 22/09/2007 23:31:33.

    I'm trying to search for all records which have the same datetime value. The SQL string I'm using is:

    Code Snippet

     str_SQL = "SELECT * FROM Exercise WHERE RecordTimeStamp = " + CheckTimeStamp;


    Where RecordTimeStamp  is the column in table Exercise and is of type Date/Time, and CheckTimeStamp (of type DateTime) is the parameter passed into the code. I've inspected the contents of str_SQL via breakpoint and it looks perfect to me:

                        
    Code Snippet

      "SELECT * FROM Exercise WHERE RecordTimeStamp = 23/09/2007 00:51:24"


    but it just barfs and exits the code. I'm using VS2005 Express, and all my other stuff (create mdb file, open, read records, add records, delete records) works just fine.

    I've also tried

    Code Snippet

     str_SQL = "SELECT * FROM Exercise WHERE RecordTimeStamp = '" + CheckTimeStamp + "'";


    but that didn't work either. On the other hand, using

    Code Snippet

     str_SQL = "SELECT * FROM Exercise WHERE RecordTimeStamp = 1";


    though pointless of course as it could never work, at least resulted in the query running and the program continuing normally.


    Can anybody tell me what (I presume basic) mistake I'm making here?
    Saturday, September 22, 2007 3:00 PM

Answers

  •  

    Jet requires '#'s around dates.

     

    Try:

      "SELECT * FROM Exercise WHERE RecordTimeStamp = #23/09/2007 00:51:24#"

     

    or str_SQL = "SELECT * FROM Exercise WHERE RecordTimeStamp = #" + CheckTimeStamp + "#";

     

     

    Saturday, September 22, 2007 6:55 PM
  • You could also use parameters instead of SQL variable insertion so you don't have to worry about syntax issues such as this.

     

    Code Snippet

    Dim AccessCommand As New OleDbCommand("SELECT * FROM Exercise WHERE RecordTimeStamp = ?", AccessConnection)

     

    AccessCommand.Parameters.Add("@DateVal", System.Data.OleDb.OleDbType.DBDate).Value = TheDateValue

     

     

     

    Monday, September 24, 2007 12:40 PM

All replies

  •  

    Jet requires '#'s around dates.

     

    Try:

      "SELECT * FROM Exercise WHERE RecordTimeStamp = #23/09/2007 00:51:24#"

     

    or str_SQL = "SELECT * FROM Exercise WHERE RecordTimeStamp = #" + CheckTimeStamp + "#";

     

     

    Saturday, September 22, 2007 6:55 PM
  • Duh. I think I've been at this for too long. Need coffee.

    Thanks
    Sunday, September 23, 2007 2:32 AM
  • You could also use parameters instead of SQL variable insertion so you don't have to worry about syntax issues such as this.

     

    Code Snippet

    Dim AccessCommand As New OleDbCommand("SELECT * FROM Exercise WHERE RecordTimeStamp = ?", AccessConnection)

     

    AccessCommand.Parameters.Add("@DateVal", System.Data.OleDb.OleDbType.DBDate).Value = TheDateValue

     

     

     

    Monday, September 24, 2007 12:40 PM