I've recently changed an Update SQL string from dynamic SQL string to parametric SQL string. Here's what I had before:
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.DBstring);
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "UPDATE myTable SET MY_FIELD='" + myString + "' WHERE F_SERIAL = '"+mySerial+"'";
comm.CommandType = CommandType.Text;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
and here is what I have now:
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.DBstring);
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "UPDATE myTable SET MY_FIELD = @myString WHERE F_SERIAL = @mySerial";
comm.Parameters.Add("@mySerial",OleDbType.VarWChar).Value = mySerial;
comm.Parameters.Add("@myString",OleDbType.VarWChar).Value = myString;
comm.CommandType = CommandType.Text;
comm.Connection = conn;
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
The parameters are correctly set, as from the immediate window (I only show the first one, but the second has the same structure):
>>> comm.Parameters[0];
{@myString}
base {System.Data.Common.DbParameter}: {@myString}
DbType: String
Direction: Input
IsNullable: false
OleDbType: VarWChar
ParameterName: "@myString"
Precision: 0
Scale: 0
Size: 15
SourceColumn: ""
SourceColumnNullMapping: false
SourceVersion: Current
Value: "test ++ ìì''' "
However, while the first code snippet used to work before, the new one doesn't. No error is raised, the execution goes fine, but the value in the Database is not updated. Does anyone have an idea on what could be going wrong? Am I maybe forgetting to do something? Sorry for the dumb question, but I really have nothing more than this and cannot figure out what's wrong.
I finally solved my problem, something very weird. I will add an answer here in case someone could find it helpful in the future.
Basically, the problem was the order in which the parameters were added to the Parameters
collection of the OleDbCommand
object.
In my code, I was doing this:
comm.CommandText = "UPDATE myTable SET MY_FIELD = @myString WHERE F_SERIAL = @mySerial"; //<-- defining the command text
comm.Parameters.Add("@mySerial",OleDbType.VarWChar).Value = mySerial; //<-- parameter "mySerial" before "myString"
comm.Parameters.Add("@myString",OleDbType.VarWChar).Value = myString; //<-- parameter "myString" after "mySerial"
However, I'm using myString
before mySerial
, so basically the resulting query was:
UPDATE myTable SET MY_FIELD = mySerial WHERE F_SERIAL = myString
This, I'd say, is a bit strange because what's the reason of using parameter's names such as @myString
or @mySerial
if then the code only depends on their positioning inside the Parameters
collection?
But well, the problem is now solved, and apparently someone else had already faced the same issue here. As the question's asker correctly says, OleDb
can recognize that you are using a parameter but not what parameter you're using (???), making so everything dependent only on the order on which they are stored in the list.
I apologise for the "useless" question, but I hope at least the answer might turn to be useful to someone that, like me, in the future might struggle debugging for hours an apparently correct code not executing properly just because of the parameters' order within its list.