Search code examples
c#sqloledb

SQL UPDATE command won't work with Command.Parameters.Add/AddWithValue

just discovered something strange, If I write an SQL statement for a command like so:

command.CommandText = "UPDATE LoginTbl SET PW = @pw WHERE TwyID = @twyID";
command.Parameters.AddWithValue("@pw", pw_txt.Text);
command.Parameters.AddWithValue("@twyID", TwyID.ToString());
command.ExecuteNonQuery();

The SQL command will run with no exception being thrown, however it does not update the database at all. Strangely, if I write the command without parameters:

command.CommandText = "UPDATE LoginTbl SET PW = '"+pw_txt.Text+ "' WHERE TwyID = '"+TwyID.ToString()+"'";
command.ExecuteNonQuery();

The SQL command runs perfectly but this time it actually updates the database!

This puzzled me some what as I am very used to using parameters in most SQL commands.

Wondering if anyone knows why the UPDATE statement particularly doesn't accept the OleDbParameters enumerator.

Thanks, Liam

Solution

  • Firstly, you really don't want to use your second approach (string concatenation) as it can lead to all sorts of SQL Injection nastiness as well as issues involving parameters being passed in incorrectly.

    If you are using an OleDbConnection or OleDbCommand as opposed to a SqlConnection and SqlCommand, you may want to try not using named parameters as they are not supported :

    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

    and instead just use ? instead. Just ensure that you add your parameters in the proper order :

    command.CommandText = "UPDATE LoginTbl SET PW = ? WHERE TwyID = ?";
command.Parameters.AddWithValue("@pw", pw_txt.Text);
command.Parameters.AddWithValue("@twyID", TwyID.ToString());
command.ExecuteNonQuery();