locked
How to query a datetime field in access database using ASP.NET RRS feed

  • Question

  • User-1652394277 posted

    Hello

    I am using an Access database with one of the Table columns defined as Date/Time field.

     

    I am trying to use ASP.NET 4.0 Web forms application to query and retrieve the row based on the DateTime field.

    Here is my code snippet:

          string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

          OleDbConnection oleDBConn = new OleDbConnection(connectionString);

          DateTime dt = new DateTime(dtBatchCreation.Year, dtBatchCreation.Month, dtBatchCreation.Day, dtBatchCreation.Hour, dtBatchCreation.Minute, dtBatchCreation.Second);

          //string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = " + String.Format("{0:#yyyy-MM-dd hh:mm:ss tt#}", dt);
         
          string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = @BatchDate";

          string s = @String.Format("{0:yyyy-MM-dd HH:mm:ss}", dt);

          OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

          //cmd.Parameters.AddWithValue("@BatchDate", s);

          oleDBConn.Open();

          OleDbDataReader reader = cmd.ExecuteReader();

          int batchID = (int)reader[0];

          reader.Close();

          oleDBConn.Close();

          return batchID;

    The above methods always return an error.

     

    Tuesday, October 19, 2010 4:55 PM

Answers

  • User-1199946673 posted

     Are you sure the record is added? And if so, what datetime is inserted? You really should start using parameterized queries:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO Batch (ID, CreationDate, UserName, BatchTotal) VALUES ( ?, ?, ?, ?) ", oleDBConn);
    cmd.Parameters.AddWithValue("userID", userID);
    cmd.Parameters.AddWithValue("CreationDate", CreationDate.ToOADate());
    cmd.Parameters.AddWithValue("UserName", User.Identity.Name);
    cmd.Parameters.AddWithValue("BatchTotal", dBatchTotal);
    
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 20, 2010 1:58 PM

All replies

  • User-1199946673 posted

     

    cmd.Parameters.AddWithValue("@BatchDate", dt.ToOADate());

    More info regarding Access and Date(time)s:

    http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET 

    Tuesday, October 19, 2010 6:09 PM
  • User-1652394277 posted

    If I have inserted a row into the table of an access database as follows:

          string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

          OleDbConnection oleDBConn = new OleDbConnection(connectionString);

          DateTime dtBatchCreation = DateTime.Now;

          StringBuilder sql = new StringBuilder();
          sql.Append("INSERT INTO Batch (ID, CreationDate, UserName, BatchTotal) VALUES ( ");
          sql.Append("'" + userID + "', ");
          sql.Append("'" + dtBatchCreation + "', ");
          sql.Append("'" + User.Identity.Name + "', ");
          sql.Append("'" + dBatchTotal + "' ");
          sql.Append(" )");
         
          OleDbCommand cmd = new OleDbCommand(sql.ToString(), oleDBConn);

          oleDBConn.Open();

          cmd.ExecuteNonQuery();

          oleDBConn.Close();

     

    I am trying to retrieve the record inserted by querying with the CreationDate.

    I tried using the ToOADate() method but that does NOT seem to retrieve the record created above.

    Here is the snippet for retrieving:

           string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

           OleDbConnection oleDBConn = new OleDbConnection(connectionString);

           DateTime dt = new DateTime(2010, 10, 20, 7, 10, 19);

           string sql = "SELECT ID FROM Batch WHERE CreationDate = @BatchDate";

           OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

           cmd.Parameters.AddWithValue("@BatchDate", dtBatchCreation.ToOADate());

           oleDBConn.Open();

           OleDbDataReader reader = cmd.ExecuteReader();

           int batchID = (int)reader["BatchID"];

           reader.Close();

           oleDBConn.Close();

           return batchID; 

     

    It still throws an exception.

    Wednesday, October 20, 2010 10:39 AM
  • User-1199946673 posted

     Are you sure the record is added? And if so, what datetime is inserted? You really should start using parameterized queries:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO Batch (ID, CreationDate, UserName, BatchTotal) VALUES ( ?, ?, ?, ?) ", oleDBConn);
    cmd.Parameters.AddWithValue("userID", userID);
    cmd.Parameters.AddWithValue("CreationDate", CreationDate.ToOADate());
    cmd.Parameters.AddWithValue("UserName", User.Identity.Name);
    cmd.Parameters.AddWithValue("BatchTotal", dBatchTotal);
    
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 20, 2010 1:58 PM