none
INSERT statement error please help RRS feed

  • Question

  • hey guys im trying to insert multiple items into the one table and it returns the error 'Data type mismatch in criteria expression.

    OleDbCommand cmd = new OleDbCommand("INSERT INTO EnteredData (DataEnteredBy,DataMg,DateOfData,DataProvidedBy) VALUES (@DataEnteredBy, @DataMg, @DateofData, @DataProvidedBy)", con);
         
    cmd.Parameters.AddWithValue("@DataEnteredBy", Response2);
    cmd.Parameters.AddWithValue("@DataMg",txtMicrograms.Text);
    cmd.Parameters.AddWithValue("@DateOfData",calDateCollected);
    cmd.Parameters.AddWithValue("@DataProvidedBy", Login.Pusername);
    
    con.Open();
    cmd.ExecuteReader();

    Any replies thanks in advance 

    Monday, May 21, 2012 4:44 PM

Answers

  • OLE DB expects '?' parameter markers in the insert statement string.  Try:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO EnteredData (DataEnteredBy,DataMg,DateOfData,DataProvidedBy) VALUES (?,?, ?, ?)", con);

    Also, what are the .NET data types of the provided parameter values?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    • Proposed as answer by SKracker Friday, May 25, 2012 5:59 AM
    • Edited by Dan GuzmanMVP Saturday, May 26, 2012 2:14 AM removed extraneous VALUES clause
    • Marked as answer by Tony xiaoModerator Tuesday, June 12, 2012 6:06 AM
    Tuesday, May 22, 2012 11:55 AM

All replies

  • Hi,

    1)Check the column dataTypes in a table. Exact data types you have to use.

    2)Use ExecuteNonQuery() for SQL statements like 'INSERT','UPDATE','DELETE'.

    eg: cmd.ExecuteNonQuery()


    PS.Shakeer Hussain

    Monday, May 21, 2012 5:16 PM
  • I get the same error when i change it to cmd.ExecuteNonQuery();
    Monday, May 21, 2012 11:26 PM
    1. set the parameters for the oledb command and add prams as shown below
    OleDbCommand cmd = new OleDbCommand();
            OleDbParameter param = new OleDbParameter("@DataEnterdBy", OleDbType.VarChar);
    param.Value=Response2;
                cmd.Parameters.Add(param);
    • Do above for all of the parameters depending on the oledbType



    $Kracker


    • Edited by SKracker Tuesday, May 22, 2012 4:45 AM correction
    Tuesday, May 22, 2012 4:44 AM
  • How does this work ? will i have to create a new parameter for each one ? 

    • Edited by Oavatog Tuesday, May 22, 2012 11:32 AM
    Tuesday, May 22, 2012 11:29 AM
  • OLE DB expects '?' parameter markers in the insert statement string.  Try:

    OleDbCommand cmd = new OleDbCommand("INSERT INTO EnteredData (DataEnteredBy,DataMg,DateOfData,DataProvidedBy) VALUES (?,?, ?, ?)", con);

    Also, what are the .NET data types of the provided parameter values?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    • Proposed as answer by SKracker Friday, May 25, 2012 5:59 AM
    • Edited by Dan GuzmanMVP Saturday, May 26, 2012 2:14 AM removed extraneous VALUES clause
    • Marked as answer by Tony xiaoModerator Tuesday, June 12, 2012 6:06 AM
    Tuesday, May 22, 2012 11:55 AM
  • I would guess that the problem is with your date parameter. Either the calDateCollected variable is not a Date data type, or DateOfData is not a Date data type in the database table. Either of those cases would explain the data type mismatch.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 22, 2012 4:35 PM
  • How does this work ? will i have to create a new parameter for each one ? 

    1. yeah set attributes and try, datatype is curcial dont miss it and
    2. your's is a simple command try

    $Kracker

    Friday, May 25, 2012 6:00 AM