none
Access does not support OleDbParameter DateTime? RRS feed

  • Question

  • I got "Unhandled Exception: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression." when I test the code below.

    Code Snippet

    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Nwind.mdb");
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "UPDATE Orders SET OrderDate = ? WHERE OrderID = 10248";
    command.Parameters.Add(new OleDbParameter("@OrderDate", DateTime.Now));
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();

     

    Is that a bug?
    Friday, August 10, 2007 2:55 AM

Answers

  • The problem is that the query doesn't know what you are passing it, and needs to be set up in the sql, if you want to pass only a date time value instead of passing a string like I showed in my first post on this thread, then you need to change your sql and code to the following:

    Code Snippet

    command.CommandText = "PARAMETERS DTTest DateTime;UPDATE Orders SET Orders.OrderDate = [DTTest] WHERE (((Orders.OrderID)=10248));";

    command.Parameters.Add(new OleDbParameter("DTTest", DateTime.Now));

     

     

    This isn't speculation, I created an access database and created a table with the two fields indicated above, and tested this, and my previous code.

     

    Friday, August 10, 2007 12:23 PM
  •  

    Cool! It really works! Now job! Thanks Mike~~

     

    I got my final version now.

     

    Code Snippet

    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Databases\Nwind.mdb");
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "PARAMETERS [@OrderDate] datetime, [@OrderID] int;UPDATE Orders SET OrderDate = [@OrderDate] WHERE OrderID = [@OrderID]";
    command.Parameters.Add(new OleDbParameter("@OrderDate", DateTime.Now));
    command.Parameters.Add(new OleDbParameter("@OrderID", 10248));
    connection.Open();
    WL(command.ExecuteNonQuery());
    connection.Close();

     

    Friday, August 10, 2007 12:39 PM

All replies

  • The parameter needs to be passed as a string:

    Code Snippet
    command.Parameters.Add(new OleDbParameter("@OrderDate", DateTime.Now.ToString()));

     

     

    The query doesn't know that the value is a date time, so it assumes that it is a string that is being passed not a serial value. 

    Friday, August 10, 2007 3:40 AM
  • If you explicitly specify type of your parameter as does it help? Like

     

    command.Parameters.Add(new OleDbParameter("@OrderDate",OleDbType.Date);

    scommand.Parameters["@OrderDate"].Value=DateTime.Now;

    Friday, August 10, 2007 10:58 AM
    Moderator
  • I tried Parameter.ResetDbType() in debug mode, and it's DbType had set to DbType.DateTime. No effect, same exception was thrown.

     

     

    Friday, August 10, 2007 11:46 AM
  • Did you try the code that I gave you?  It works, I tried it 5 minutes ago.

    Friday, August 10, 2007 12:11 PM
  • The problem is that the query doesn't know what you are passing it, and needs to be set up in the sql, if you want to pass only a date time value instead of passing a string like I showed in my first post on this thread, then you need to change your sql and code to the following:

    Code Snippet

    command.CommandText = "PARAMETERS DTTest DateTime;UPDATE Orders SET Orders.OrderDate = [DTTest] WHERE (((Orders.OrderID)=10248));";

    command.Parameters.Add(new OleDbParameter("DTTest", DateTime.Now));

     

     

    This isn't speculation, I created an access database and created a table with the two fields indicated above, and tested this, and my previous code.

     

    Friday, August 10, 2007 12:23 PM
  •  

    Cool! It really works! Now job! Thanks Mike~~

     

    I got my final version now.

     

    Code Snippet

    OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Databases\Nwind.mdb");
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "PARAMETERS [@OrderDate] datetime, [@OrderID] int;UPDATE Orders SET OrderDate = [@OrderDate] WHERE OrderID = [@OrderID]";
    command.Parameters.Add(new OleDbParameter("@OrderDate", DateTime.Now));
    command.Parameters.Add(new OleDbParameter("@OrderID", 10248));
    connection.Open();
    WL(command.ExecuteNonQuery());
    connection.Close();

     

    Friday, August 10, 2007 12:39 PM
  • No problem!

     

     

    Friday, August 10, 2007 2:30 PM