Cannot insert/query date/time value to date/time field in ACCDB file RRS feed

  • Question

  • Hi All,

    I have a stupid problem with ACCDB. I have create connection using oledb.
    I tried to insert data to table using the following code:

                    int result;
                    int userID = 0;
                    int clientID = 0;
                    DateTime startTime = DateTime.Now;
                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=my.accdb;Jet OLEDB:Database Password=Password;";

                    string sql = " INSERT INTO oscTimesheet "
                               + " ( userID, clientID, startTime ) "
                               + " VALUES (@userID, @clientID, @startTime) ";
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;

                    OleDbParameter pUserID = new OleDbParameter("@userID", userID);
                    OleDbParameter pClientID = new OleDbParameter("@clientID", clientID);
                    OleDbParameter pStartTime = new OleDbParameter("@startTime", DbType.Date);
                    pStartTime.Value = startTime;
                    cmd.Connection = con;
                    result = cmd.ExecuteNonQuery();

    NOTE: userID and clientID is Numeric field, startTime is Date/Time in ACCDB file.

    It always shows result as "Data type mismatch in criteria expression.". After I trace, I found that "startTime" is the column which cause me the problem.

    Can you, please suggest me solution for this matter?


    Thursday, January 8, 2009 7:01 AM

All replies

  • I do not think  ACE provider supports named parameters, so your INSERT SQL statement should look like

    string sql = " INSERT INTO oscTimesheet "
                               + " ( userID, clientID, startTime ) "
                               + " VALUES (?, ?, ?) ";

    I would also recommend to provide datatype for the @userID and @clientID parameters explicitly. Rest of the code should stay the same as long as you keep same order of the parameters

    Val Mazur (MVP) http://www.xporttools.net
    • Proposed as answer by VMazur Thursday, January 8, 2009 10:51 AM
    Thursday, January 8, 2009 10:51 AM
  • I have made change according to your comment but it does not work as expected.
    Any other way to working on this, please help me on it.
    Monday, January 12, 2009 6:08 AM
  • What kind of error do you get this time?
    Val Mazur (MVP) http://www.xporttools.net
    Monday, January 12, 2009 11:08 AM
  • I got the same error as previous.
    Monday, January 12, 2009 2:41 PM
  • How did you declare your numeric types (userID and clientID)? Can you post your latest code?
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, January 13, 2009 10:52 AM