Parametric 'UPDATE' string running with no errors but not doing anything

Question

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.

for starters the SQL is not the same in Before and After. In the second you are passing 2 params for one in the SQL (no WHERE) — Ňɏssa Pøngjǣrdenlarp, Mar 15 '15 at 15:53
This may or may not have anything to do with this, but I notice that your parameterized command, as you show it, has no `where` clause? — Ann L., Mar 15 '15 at 15:53
Sorry, I'm updating the code, I made a typo while copying the code. — Matteo NNZ, Mar 15 '15 at 15:53
@AnnL. absolutely sure, I would get a null cast exception when trying to cast a null variable into a VarWChar one otherwise :) — Matteo NNZ, Mar 15 '15 at 15:55
Try to catch the return value of ExecuteNonQuery. It is an integer that counts how many rows have been changed by the query. If it is zero then no record matches the @mySerial parameter, if it is 1 or more then you are not looking at the correct database. (Do you use |DataDirectory| in the connection string?) — Steve, Mar 15 '15 at 19:04
@Steve thanks for the tip, very useful. The return is actually 0, but the mySerial parameter is correct (in the immediate window I've typed mySerial and it returns me the value I want). Any idea on what can be going wrong? — Matteo NNZ, Mar 15 '15 at 20:48
Zero mean that there is no record updated and this could only caused by a failed match by the WHERE condition. But if you open Access and try the same identical query with the actual values instead of parameters, do you get any record? Again what is the value of `this.DBstring`? — Steve, Mar 15 '15 at 20:52
@Steve, problem found :) I'm adding an answer to my question, that I figured out it was actually a bit misleading. — Matteo NNZ, Mar 15 '15 at 22:19

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

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.

edited May 23 '17 at 12:12

Community

1
1

answered Mar 15 '15 at 22:26

Matteo NNZ

11,930
12
52
89

2

Well I should have been spotted that before. Probably I am a bit tired now. The officially sanctioned way to name the placeholders in OleDB is the `?` symbol. It just happens that OleDb when working with an Access database (and perhaps some other db) allows the parameter placeholders to have a name in the form @xxxx (probably for better portability toward its big cousin Sql Server) but this doesn't mean that you could put them in any order in the parameter collection. – Steve Mar 15 '15 at 22:33
1

Right @Steve, it just sounds a bit strange to me. I would have said that calling a parameter meant returning that parameter, don't you think so? Thanks anyway a lot for your help, apart for this problem caused only by myself and my low attention, you've given a big + to my overall code with the parametrisation! – Matteo NNZ Mar 15 '15 at 22:35
And do not forget the using statement. See you again on SO. Good night – Steve Mar 15 '15 at 22:51
1

*"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?"* - That's just the way `System.Data.OleDb` works. It doesn't *prevent* you from using @named parameters, but it also *doesn't pay any attention* to the names; it only cares about the order in which the parameter placeholders appear in the CommandText (which must be the same order in which the parameters are created). – Gord Thompson Mar 15 '15 at 23:47
@GordThompson yes you're right, I just find this behavior misleading (in my opinion we should just not be able to use the names). Basically the only use we should do is "?", because using the parameter names might even make the code more readable but lead to big confusion because (as in my case) myString becomes mySerial and viceversa just because of the order I have defined them. Lesson learned :) – Matteo NNZ Mar 16 '15 at 12:20

Parametric 'UPDATE' string running with no errors but not doing anything

1 Answers1

Linked