MS Access 2007 data type mismatch in Criteria Expression for string

Question

I have three fields called fileName,rowNo and rowText in Access 07 table named shortNotes. When I run this insert query it works perfect:

thisCommand.CommandText = "insert into shortNotes values('" + fName + "'," + rNo + ",'" + richTextBox2.Text +" |"+rNo+ "')";

But when I added parameters, It started throwing this error: "data type mismatch in Criteria Expression"

Here is the code:

        dbCon = new OleDbConnection(MyconnectionString);
        dbCon.Open();
        thisCommand = new OleDbCommand();
        thisCommand.Connection = dbCon;

        thisCommand.Parameters.Add("@rowtext", OleDbType.BSTR);
        thisCommand.Parameters.Add("@file", OleDbType.BSTR);
        thisCommand.Parameters.Add("@rno", OleDbType.Integer);

        thisCommand.Parameters["@rowtext"].Value = richTextBox2.Text + " |" + rNo;

        thisCommand.Parameters["@file"].Value = fName;

        thisCommand.Parameters["@rno"].Value = rNo;

        thisCommand.CommandText = "insert into shortNotes values(@file,@rno,@rowtext)";
        thisCommand.ExecuteNonQuery();//Error

Here file is memo, rno is number and rowtext is memo datatype in access 07. What is the problem?

Note that OleDb can use named parameters: see http://stackoverflow.com/questions/4857940/ms-access-named-parameters-and-column-names/20811933#20811933 — Ben McIntyre, Dec 28 '13 at 07:07

score 2 · Accepted Answer · answered Aug 20 '12 at 16:45

2

The problem is OleDb does NOT use named parameters.

"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters."

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

Check the example there..

answered Aug 20 '12 at 16:45

banging

2,540
1
22
26

Note that OleDb can use named parameters: see http://stackoverflow.com/questions/4857940/ms-access-named-parameters-and-column-names/20811933#20811933 – Ben McIntyre Dec 28 '13 at 07:09

score 0 · Answer 2 · answered Aug 19 '12 at 08:50

At a guess, could it be something to do with your column lengths? You've specified the type as strings (well memo) but not the length - try using an overload (MSDN docs) that takes the optional parameter for length. For example, if your Access notes fields are 255 chars long:

thisCommand.Parameters.Add("@rowtext", OleDbType.BSTR, 255);
thisCommand.Parameters.Add("@file", OleDbType.BSTR, 255);

My worry is without this, it might assume that the length of your parameters is only 1 character long and so fall over when you try and set the value to something longer than this.

score 0 · Answer 3 · answered Aug 20 '12 at 16:23

0

What data types are these columns in your Access database? Have you tried using VarChar instead of BSTR, or even just using Variant for the data types across the board?

answered Aug 20 '12 at 16:23

Jim

6,753
12
44
72

MS Access 2007 data type mismatch in Criteria Expression for string

3 Answers3