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;
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;
And all this time I've been sooo careful about my parameter names, sigh.
No comments:
Post a Comment