CODEWITHSUNDEEP
c#ms-accessoledb
Gigi
Gigi

Reputation: 340

Update command with parameters gives "Data type mismatch in criteria expression"

I have the following code:

/*
// it works
cmd_oper = "UPDATE [Model Elements] SET [Record Status] = \"Disabled\" WHERE [Index] = @db_idx";
/*/
// it doesn't work
cmd_oper = "UPDATE [Model Elements] SET [Record Status] = @stat WHERE [Index] = @db_idx";
//*/
using( OleDbCommand cmd = new OleDbCommand( cmd_oper, svr_conn ) )
{
    cmd.Parameters.Add( "@db_idx", OleDbType.Integer ).Value = 2;
    //cmd.Parameters.Add( "@stat", OleDbType.VarChar ).Value = "Disabled";
    cmd.Parameters.AddWithValue( "@stat", "Disabled" );
    cmd.ExecuteNonQuery();
}

With the second variant of cmd_oper (the one not commented in the beginning of the code) I get "Data type mismatch in criteria expression". The other one works. The type of Record Status column is set in database as Short Text. I know there are many posts related to this error on StackOverflow but I couldn't find an exact fit. Thanks.

Upvotes: 0

Views: 871

Answers (1)

Caius Jard
Caius Jard

Reputation: 74615

The fine manual, https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx has the following to say about parameters

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.

Ergo, I think you need your code to be like:

cmd_oper = "UPDATE [Model Elements] SET [Record Status] = ? WHERE [Index] = ?";
using( OleDbCommand cmd = new OleDbCommand( cmd_oper, svr_conn ) )
{
    cmd.Parameters.AddWithValue( "anything-name-doesnt-matter", "Disabled" );
    cmd.Parameters.Add( "its-position-that-matters", OleDbType.Integer ).Value = 2;
    cmd.ExecuteNonQuery();
}

For what it's worth, you should probably name your parameters sensibly (I named them silly above to demonstrate that the name is irrelevant) because the idea is that once prepared, you can execute a statement many times just changing the parameters:

    cmd.Parameters["its-position-that-matters"].Value = 3;
    cmd.ExecuteNonQuery();
    cmd.Parameters["its-position-that-matters"].Value = 4;
    cmd.ExecuteNonQuery();
    cmd.Parameters["its-position-that-matters"].Value = 5;
    cmd.ExecuteNonQuery();
    cmd.Parameters["its-position-that-matters"].Value = 6;
    cmd.ExecuteNonQuery();

Here i've run the update for index values 3, 4, 5 and 6 too, just by changing the parameter value and re-running. It would hence have been better of me to choose a sensible name for the "index" parameter, to make the code more readable

Steve, in his comment, has noted he believes that you can put named parameters in the query, but the names are ignored (they're essentially treated as ? marks anyway) so you'll still need to add the parameter values in the same order as the placeholders appear. If you have repeated a placeholder in a query, you'll have to repeat-add it to the parameters collection. I've no comment on the accuracy of steve's assertion; I've always used ?

Ultimately, this is all good evidence that really you should get into learning to use a data access library like Entity Framework, and stop writing SQL strings in your button click event handlers - it's not a good way to code. If you'd used EF from the outset, you'd never even have hit this problem. Good on you for using parameterised queries though. Now go check out EF and leave this '90s donkey-way of doing data access behind :)

Upvotes: 1

Related Questions