CODEWITHSUNDEEP
c#winformsoledb
Aditya Bokade
Aditya Bokade

Reputation: 1786

MS Access 2007 data type mismatch in Criteria Expression for string

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?

Upvotes: 1

Views: 2178

Answers (3)

banging
banging

Reputation: 2550

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..

Upvotes: 2

Jim
Jim

Reputation: 6881

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?

Upvotes: 0

Bridge
Bridge

Reputation: 30651

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.

Upvotes: 0

Related Questions