What's 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 that:

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

and then used a 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 more simple, and It works fine now. Thank you all

Solution 1

Simply ask google, I guess more than 10000 hits is quite impressive. Your argument "I don't think that..." is not valid until you proved it.

This is what MSDN says:

The OLE DB.NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE
CustomerID = ?

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

Solution 2

The problem is because the parameters are not in the same order when you are adding them.

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

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

This is because ADO.NET does not support named parameters when using an OLEDB provider, and since you are connecting to an Access DB, you are infact using an OLEDB provider...so the order of the parameters does matter.

---

If they are in order, and it's still not working, then I think that it might be an issue with the DateTimes;
Try converting it to string before adding it as a parameter :

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

And also make sure that the format of the date is in U.S. format (m/d/yyyy) or ISO Format (yyyy-mm-dd)

Solution 3

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

When you use OleDb, then you have to add the parameters in the same order as they appear in the query, since the names that you give them, are not used.

Comments

[Quan Mai]

@Dreas: DocID and SourceID are string, I don't think parsing them to integer is necessary!

[Mad Myche]

@Coops I know this is ancient, but that limitation is if the CommandType is Text. It is supported for other CommandType enumerations