Daft Developer

Wednesday, May 10, 2006

OleDbCommand Parameters - Order Matters

Having trouble with your MS Access Update statement? Check your parameter order.


So I've written a small parameterized Update statement to create an OleDbCommand.
I was surprised to find that my Update command was returning 0 rows updated. Everything looks fine on inspection, so I pull the command into MS Access and replace the parameters with the specified values and it works fine. My code looks like this,


string sql = "UPDATE tblMemberVehicle SET MemberId=@MemberId, VehicleTypeId=@VehicleTypeId, Identifier=@Identifier WHERE Id=@Id";

OleDbCommand dbCommand = new OleDbCommand(sql, conn );

dbCommand.Parameters.Add("@Id", OleDbType.Integer).Value = vehicle.Id;

dbCommand.Parameters.Add("@MemberId", OleDbType.Integer).Value = vehicle.User.Id;

dbCommand.Parameters.Add("@VehicleTypeId", OleDbType.Integer).Value = vehicle.VehicleType.Id;

dbCommand.Parameters.Add("@Identifier", OleDbType.VarChar).Value = vehicle.Identifier;

Parameter names all match nicely, no exceptions from the database. I check the rows updated after running my ExecuteNonQuery statement, and always - 0 rows updated.


I have another update statement which is working, so I take a quick look at it. Lo and behold, the @Id was the last parameter added, and it corresponds with the parameter order as it appears in the SQL statement. Could it be that OleDbCommand works just like OdbcCommand and requires parameters specified in the SQL specified order? I begin to suspect that the parameter names are actually meaningless and conduct a small experiment. I change the parameter names to nonsensical names and leave the sql parameter names alone.


string sql = "UPDATE tblMemberVehicle SET MemberId=@MemberId, VehicleTypeId=@VehicleTypeId, Identifier=@Identifier WHERE Id=@Id";

OleDbCommand dbCommand = new OleDbCommand(sql, conn );

dbCommand.Parameters.Add("@Foo", OleDbType.Integer).Value = vehicle.User.Id;

dbCommand.Parameters.Add("@Bar", OleDbType.Integer).Value = vehicle.VehicleType.Id;

dbCommand.Parameters.Add("@Try", OleDbType.VarChar).Value = vehicle.Identifier;

dbCommand.Parameters.Add("@This", OleDbType.Integer).Value = vehicle.Id;

Any guesses as to what happens? Surprise, surprise, this code works. The parameter names are truly meaningless, well against MS Access anyway. I believe SQL Server respects these parameter names and actually uses them.


And all this time I've been sooo careful about my parameter names, sigh.

0 Comments:

Post a Comment



<< Home