locked
c# with sql: Delete Access Date/Time row Data Type Mismatch RRS feed

  • Question

  • I am trying to delete the row with the minimum date.  First I read in the minimum date using:

    OleDbCommand myCommand = new OleDbCommand("SELECT MIN(Date) FROM 30_Day_Data", myConnection);

    myCommand.ExecuteNonQuery();
    OleDbDataReader myReader = myCommand.ExecuteReader();

    This then gives me a date in the format "1/2/2008 12:09:33 AM".  Since that is the minimum date, I need that row deleted, so I use:

    OleDbCommand myDeleteCommand = new OleDbCommand("DELETE FROM 30_Day_Data WHERE Date='" + "myReader().GetValue(0).ToString()" + "'", myConnection);
    myDeleteCommand.ExecuteNonQuery();

    This gives me an error saying "Data type mismatch in criteria expression". I have no idea how to format the date so that it can get deleted.  I have tried nearly everything with no success.  Any help is appriciated.

    Thanks!
    Ross
    Wednesday, May 7, 2008 8:35 PM

Answers

  • You need to use parameter to pass datetime values to your SQL statement. Concatenated literal dates could be treated incorrectly. In addition to it Date is a reserved word and you need to put Date column name into square brackets. Your code should look something like

     

    OleDbCommand myDeleteCommand = new OleDbCommand("DELETE FROM 30_Day_Data WHERE [Date]=?"", myConnection)

    OleDbParameter MyDate = myDeleteCommand.Parameters.Add ("MyDate", OleDbType.DateTime);
    MyDate .Value = (DateTime)myReader().GetValue(0)

    myDeleteCommand.ExecuteNonQuery();

    Thursday, May 8, 2008 9:56 AM

All replies

  • Right off the bat it looks like you're using a literal string in the WHERE clause by surrounding the GetValue() expression with double quotes:

     

    WHERE Date='" + "myReader().GetValue(0).ToString()" + "'",

     

    One general technique you can use with an Access/Jet database is to write the command text into a variable, copy it to the clipboard, and then paste the query string into the Access query designer SQL pane to see if it is syntactically correct. This will save you a lot of time looking for typos and punctuation glitches.

     

    --Mary

    Wednesday, May 7, 2008 11:17 PM
  •  

    just change it to be like that:

    OleDbCommand myDeleteCommand = new OleDbCommand("DELETE FROM 30_Day_Data WHERE Date='" + myReader().GetValue(0).ToString() + "'", myConnection);

    problem is that with your previous string your sql query became:

    "DELETE FROM 30_Day_Data WHERE Date='myReader().GetValue(0).ToString()'",

    Thus,

    sql is stating that myReader().GetValue(0).ToString() is not a valid date format.

     

    With the new string provided you query should look like this:

    "DELETE FROM 30_Day_Data WHERE Date='1/2/2008 12:09:33 AM'",

     

    Get it?

    Thursday, May 8, 2008 12:36 AM
  • You need to use parameter to pass datetime values to your SQL statement. Concatenated literal dates could be treated incorrectly. In addition to it Date is a reserved word and you need to put Date column name into square brackets. Your code should look something like

     

    OleDbCommand myDeleteCommand = new OleDbCommand("DELETE FROM 30_Day_Data WHERE [Date]=?"", myConnection)

    OleDbParameter MyDate = myDeleteCommand.Parameters.Add ("MyDate", OleDbType.DateTime);
    MyDate .Value = (DateTime)myReader().GetValue(0)

    myDeleteCommand.ExecuteNonQuery();

    Thursday, May 8, 2008 9:56 AM