How does JET OLEDB Parameters compare strings with text field in and Access DB

Question

I have the following update query in C# using a JET OLEDB connection, connecting to a ms access DB file. The query fails to change the fields, it runs correctly but just 0 rows changed.

I think the problem is how parameters are processed and compared against the DB but have no idea how to fix it.

The "User" column is set as text. I have an insert statement that works perfectly set up in the same fashion with the parameters.

com.CommandText = "UPDATE [ExamMaster] SET [User] =  (DLookup('LName', 'Users', 'ID' = '@correctUser') WHERE [User] = '@user'";

com.Parameters.AddWithValue("@correctUser", correctUser);
com.Parameters.AddWithValue("@user", userName);

If I do not use a parameter for the where clause and just insert it into the command string like so:

WHERE [User] = '"+userName+"'";</code>

it will update the DB just fine. What am I missing here? UPDATE: With or with single quotes makes no difference and rearranging the order of the parameters does not work either.

Answer 1

The order matters. I "think" in your query user is being called first before the correctUser due to the DLOOKUP function.

com.Parameters.AddWithValue("@user", userName);
com.Parameters.AddWithValue("@correctUser", correctUser);

You don't need to single quote parameters:

WHERE [User] = @user";

and I'll guess that the DLOOKUP doesn't need the single quotes either, just [brackets] if the field name has a space or is a reserved word (which [User] might be).

Answer 2

You will need to change that a bit, try:

OleDbConnection cn = new OleDbConnection(aconnectionstring);
cn.Open();

//testing
int correctUser = 1;
string userName = "1";

OleDbCommand com = new OleDbCommand();
com.Connection = cn;

//You cannot have a parameter in DLookUp
com.CommandText = "UPDATE [ExamMaster] SET [User] = " +
   "DLookup('LName', 'Users', 'ID = " + correctUser + "') WHERE [User] = @user";

com.Parameters.AddWithValue("@user", userName);

//You must execute the query
com.ExecuteNonQuery();