OleDbCommand Parameter Ordering

1 minute read

I’ve been working on an ASP.Net application with an Access backend for the past few days and I came across an interesting feature of the OleDbCommand object in the .NET framework.  I was writing some code to update the users table on the database, but for some reason or other the code just would not update the row. I checked and verified that all of the data had been initialized to the appropriate values in the object being used to update the database but nothing! I was not getting any error message and I was truly stumped!

After a bit I thought about this for a second.  I had used an update statement on another part of the object but the only difference was that now I had to update multiple fields on the record.  Apparently, when adding parameters to a OleDbCommand object you have to add parameters as they appear in the update sql string.  If they don’t match, you won’t be able to update the table.  Below is the code that finally worked:

public void UpdateTeamMemberInfo()
	command = new OleDbCommand("UPDATE Users SET FirstName = ?, " +
							   "LastName = ?, " +
							   "Email = ?, " +
							   "Phone = ? " +
							   "WHERE User_Id = ? " +
							   "AND TeamID = ?", con);

	// Note: the position of the parameters must match what you see above in
	// the query string above or else this query will not work in updating
	// the user in question.
	command.Parameters.AddWithValue("FirstName", FirstName);
	command.Parameters.AddWithValue("LastName", LastName);
	command.Parameters.AddWithValue("Email", Email);
	command.Parameters.AddWithValue("Phone", Phone);
	command.Parameters.AddWithValue("User_Id", UserID);
	command.Parameters.AddWithValue("TeamID", TeamID);

After I had found the solution, I did want to see if this was the default ways things worked.  It turns out it is, check out this link for more info on this!

Leave a comment