CODEWITHSUNDEEP
c#ms-accessoledb
James Kevin De Jesus
James Kevin De Jesus

Reputation: 73

C# AND ACCESS - Data type mismatch in criteria expression

I've created a code that updates/edits details of a/an computer/electronic product for a C# program connecting to the MS Access. Here are the codes:

OleDbCommand cmd = new OleDbCommand("UPDATE Available SET ProductType = '" + newAvailable.ProductType + "', Brand = '"+ newAvailable.Brand + "', Model = '" + newAvailable.Model + "', SerialNo = '" + newAvailable.SerialNo + "', Remarks = '" + newAvailable.Remarks + "', RAM = '" + newAvailable.RAM + "', HDD = '" + newAvailable.HDD + "', ODD = '" + newAvailable.ODD + "', VideoCard = '" + newAvailable.VideoCard + "', PS = '" + newAvailable.PS + "'  WHERE AvailableID = '"+oldAvailable.AvailableID+"'", cnn);
cmd.CommandType = CommandType.Text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();

AvailableID accepts Int32 values and the rest of the variables are string. The program is executable, yet the C# detected the error.

Data type mismatch in criteria expression.

What should I do?

Upvotes: 2

Views: 24760

Answers (5)

Tony
Tony

Reputation: 41

remove (' ') for those has a integer value

Upvotes: 4

Black Canery
Black Canery

Reputation: 117

I would use something like this to achieve what you are trying to do. This code works fine for me with MS Access 2013

//setting up connection.
OleDbConnection conn = new OledbConnection("connectionstring goes here");
//set the command string query
string cmdStr = "UPDATE Available SET ProductType = ?, Brand = ?, Model = ?, SerialNo = ?, Remarks = ?, RAM = ?, ODD = ?, VideoCard = ?, PS = ?  WHERE AvailableID = ?";
//create the command 
OleDbCommand cmd = new OleDbCommand(cmdStr,conn);
//add parameters
cmd.Parameters.AddWithValue("@p1",newAvailable.Brand);
cmd.Parameters.AddWithValue("@p2",newAvailable.Brand);
cmd.Parameters.AddWithValue("@p3",newAvailable.SerialNo);
// add all your parameters in the correct order here below .

Upvotes: 0

Waqar Majid
Waqar Majid

Reputation: 111

simple is to put a debug point on this and check the query. Copy it and run it in access directly and make changes with data. You will find out what parameter value you are entering is wrong.

Upvotes: 0

Inderpal Singh
Inderpal Singh

Reputation: 270

Try this

OleDbCommand cmd = new OleDbCommand("UPDATE Available SET ProductType = '" + newAvailable.ProductType + "', Brand = '"+ newAvailable.Brand + "', Model = '" + newAvailable.Model + "', SerialNo = '" + newAvailable.SerialNo + "', Remarks = '" + newAvailable.Remarks + "', RAM = '" + newAvailable.RAM + "', HDD = '" + newAvailable.HDD + "', ODD = '" + newAvailable.ODD + "', VideoCard = '" + newAvailable.VideoCard + "', PS = '" + newAvailable.PS + "'  WHERE AvailableID = "+oldAvailable.AvailableID, cnn);
        cmd.CommandType = CommandType.Text;
        cnn.Open();
        cmd.ExecuteNonQuery();
        cnn.Close();

Upvotes: 0

Dimitar Dimitrov
Dimitar Dimitrov

Reputation: 15148

I suspect that you're not passing one of your parameters correct probably the AvailableID, instead try to add the parameters this way:

var cmd = new OleDbCommand
{
    Connection = cnn,
    CommandType = CommandType.Text,
    CommandText = "UPDATE Available SET ProductType = ?, Brand = ?, Model = ?, SerialNo = ?, Remarks = ?, RAM = ?, ODD = ?, VideoCard = ?, PS = ?  WHERE AvailableID = ?"
};

cmd.Parameters.Add(new OleDbParameter {Value = newAvailable.ProductType});
cmd.Parameters.Add(new OleDbParameter {Value = newAvailable.Brand});
// add the other parameters ...

As a side note, it's not a good idea to generate queries by concatenating strings anyway you should always use parameters.

Upvotes: 11

Related Questions