What is wrong with these parameters?

Question

I have an access file with 7 fields:

DocID - text - primary SourceID - text ReceivedDay - Date/Time Summary - text DueDay - Date/Time Person - text Status - Yes/No 

Now I want to update this file with the following code:

 const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\DocMan.mdb;Persist Security Info=True"; const string InsertQuery = "INSERT Into Docs(DocID,ReceivedDay,Summary,Person,DueDay,Status,SourceID) Values(@DocID,@ReceivedDay,@Summary,@Person,@DueDay,@Status,@SourceID)"; string DocID = textBox1.Text; string SourceID = comboBox1.SelectedIndex.ToString(); DateTime ReceivedDay = dateTimePicker1.Value; string Summary = richTextBox1.Text; string Person = textBox2.Text; DateTime DueDay = dateTimePicker2.Value; bool Status = false; OleDbConnection cnn = new OleDbConnection(ConnectionString); cnn.Open(); OleDbCommand cmd = new OleDbCommand(InsertQuery, cnn); cmd.Parameters.AddWithValue("@DocID", DocID); cmd.Parameters.AddWithValue("@SourceID", SourceID); cmd.Parameters.AddWithValue("@ReceivedDay", ReceivedDay); cmd.Parameters.AddWithValue("@Summary", Summary); cmd.Parameters.AddWithValue("@Person", Person); cmd.Parameters.AddWithValue("@DueDay", DueDay); cmd.Parameters.AddWithValue("@Status", Status); cmd.ExecuteNonQuery(); cnn.Close(); 

But I get an exception:

 Data type mismatch in criteria expression. 

How can i fix this?

EDIT: I fixed this using a different approach:

I built a query like this:

 INSERT INTO Docs (DocID, SourceID, ReceivedDay, Summary, Person, DueDay, Status) VALUES (?, ?, ?, ?, ?, ?, ?) 

and then use the TableAdapter to call it:

 string DocID = textBox1.Text; string SourceID = comboBox1.SelectedIndex.ToString(); DateTime ReceivedDay = dateTimePicker1.Value.Date; string Summary = richTextBox1.Text; string Person = textBox2.Text; DateTime DueDay = dateTimePicker2.Value.Date; bool Status = false; DocManDataSetTableAdapters.DocsTableAdapter docsTableAdapter = new DocManDataSetTableAdapters.DocsTableAdapter(); docsTableAdapter.InsertQuery(DocID,SourceID,ReceivedDay,Summary,Person,DueDay,false); 

Much easier and now it works great. Thanks to everyone.

Answer 1

Just ask Google , I think over 10,000 hits are impressive. Your argument, “I don’t think that ...” is not valid until you have proven it.

This is what MSDN says:

The OLE DB.NET provider does not support named parameters for passing parameters to an SQL statement or stored procedure called by OleDbCommand when the CommandType parameter is set to Text. In this case, you must use the question mark ( ? ). For instance:

 SELECT * FROM Customers WHERE CustomerID = ? 

Therefore, the order in which OleDbParameter objects OleDbParameter added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Answer 2

The problem is that the options are not in the same order when you add them.

For example, in your commented line ( //adapter.InsertQuery... ) you have a DocID and then RecievedDay ... but when you add them, first add the DocID and then add the SourceID .

Make sure they are in the same order ... and this applies to both sql statements and stored procedures.

This is because ADO.NET does not support named parameters when using the OLEDB provider, and since you are connecting to the access database, you are using the OLEDB tool ... so the order of the parameters matters.

---

If they are ok and still not working, I think this might be a problem with DateTime s;
Try converting it to a string before adding it as a parameter:

 cmd.Parameters.AddWithValue("@ReceivedDay", ReceivedDay.ToShortDateString()); cmd.Parameters.AddWithValue("@DueDay", DueDay.ToShortDateString()); 

Also make sure the date format is in US format ( m/d/yyyy ) or ISO format ( yyyy-mm-dd )

Answer 3

OleDb does not support named parameters, so Dreas answer is correct.

When you use OleDb, you need to add the parameters in the same order as in the request, since the names you give them are not used.