参数'UPDATE'字符串运行无错误，但不执行任何操作 [英] Parametric 'UPDATE' string running with no errors but not doing anything

问题描述

我最近将Update SQL字符串从动态SQL字符串更改为参数SQL字符串.这是我以前的经历:

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();

这就是我现在拥有的:

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.

基本上，问题在于参数添加到OleDbCommand对象的Parameters集合的顺序.

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"

但是，我使用的是myString 之前的 mySerial，所以基本上结果查询是:

However, I'm using myString before mySerial, so basically the resulting query was:

UPDATE myTable SET MY_FIELD = mySerial WHERE F_SERIAL = myString

我想说这有点奇怪，因为使用参数名称(例如@myString或@mySerial)的原因是什么，如果代码仅取决于它们在Parameters集合中的位置呢?

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?

但是，问题已经解决，显然其他人已经在此处遇到了相同的问题.正如问题的提问者正确地说的那样，OleDb可以识别您正在使用参数，但不能识别您正在使用什么参数(???)，因此一切都取决于仅按照它们在列表中存储的顺序.

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.

这篇关于参数'UPDATE'字符串运行无错误，但不执行任何操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！