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
– Ňɏssa Pøngjǣrdenlarp, Commented Mar 15, 2015 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.
– Ann L., Commented Mar 15, 2015 at 15:53
Sorry, I'm updating the code, I made a typo while copying the code. — Matteo NNZ
– Matteo NNZ, Commented Mar 15, 2015 at 15:53
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
– Steve, Commented Mar 15, 2015 at 19:04

Community · Accepted Answer · 2017-05-23 12:12:54Z

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, 2017 at 12:12

CommunityBot

11 silver badge

answered Mar 15, 2015 at 22:26

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steve Over a year ago

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.

Matteo NNZ Over a year ago

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!

Steve Over a year ago

And do not forget the using statement. See you again on SO. Good night

Gord Thompson Over a year ago

"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).

Matteo NNZ Over a year ago

@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 :)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related