C＃+ MySQL的预处理语句瓦特/重复键更新问题 [英] C# + MySQL prepared statement w/ON DUPLICATE KEY UPDATE problems

问题描述

我有是有使用带有对重复密钥更新到MySQL准备好的语句问题的C＃应用​​程序。

I have a C# application that is having issues using a prepared statement with an ON DUPLICATE KEY UPDATE into MySQL.

当我检查连接变量在断点处只是前 mypcmd.ExecuteNonQuery（）; ， mypcmd.IsPrepared 是假。
中的查询不会做任何事情（它应该更新作为主键已经存在），并没有抛出的异常。 mypcmd.Parameters.count = 25 ，因为它应该是，如果我深入到数据视图中的值是正确的。

When I check the connection variables at a breakpoint just before mypcmd.ExecuteNonQuery();, mypcmd.IsPrepared is false. The query does not do anything (it should update as the primary key is already there) and there are no exceptions thrown. mypcmd.Parameters.count = 25 as it should be and the values are correct if I drill down into the data view.

在查询获得通过，0键更新为所有参数名（iefname：？FNAME，中间名：中间名等，而不是精确值）

When the query goes through, key 0 is updated to all parameter names (i.e.fname: ?fname, middlename: ?middlename, etc. instead of exact values)

• MySQL服务器版本5.0.51a-24 + lenny5


• MySQL的C＃程序集版本（mysql.data.dll）6.3.6.0


• 应用WPF是.NET 4.0

代码如下：

const string sqlIpatients = @"
INSERT INTO `tblpatients` (  `number`,`fname`,`middlename`,`surname`,
                            `title`,`contactno`,`mobileno`,`sex`,`dob`,
                            `housename`,`houseno`,`address1`,`address2`,
                            `address3`,`address4`,`postcode`,`notes`,`home`,
                            `sp1`,`sp2`,`sp3`,`sp4`,`sp5`,`sp6`,`email`)
                    VALUES( '?number','?fname','?middlename','?surname',
                            '?title','?contactno','?mobileno','?sex','?dob',
                            '?housename','?houseno','?address1','?address2',
                            '?address3','?address4','?postcode','?notes','?home',
                            '?sp1','?sp2','?sp3','?sp4','?sp5','?sp6','?email')
    ON DUPLICATE KEY UPDATE `number`=VALUES(`number`), `fname`=VALUES(`fname`), `middlename`=VALUES(`middlename`), `surname`=VALUES(`surname`),
                            `title`=VALUES(`title`), `contactno`=VALUES(`contactno`), `mobileno`=VALUES(`mobileno`), `sex`=VALUES(`sex`), `dob`=VALUES(`dob`),
                            `housename`=VALUES(`housename`), `houseno`=VALUES(`houseno`), `address1`=VALUES(`address1`), `address2`=VALUES(`address2`),
                            `address3`=VALUES(`address3`), `address4`=VALUES(`address4`), `postcode`=VALUES(`postcode`), `notes`=VALUES(`notes`), `home`=VALUES(`home`),
                            `sp1`=VALUES(`sp1`), `sp2`=VALUES(`sp2`), `sp3`=VALUES(`sp3`), `sp4`=VALUES(`sp4`), `sp5`=VALUES(`sp5`), `sp6`=VALUES(`sp6`), `email`=VALUES(`email`)";

...

...

MySqlCommand mypcmd = new MySqlCommand(sqlIpatients, myConn2);
    mypcmd.Prepare();
    mypcmd.Parameters.Add("?number", MySqlDbType.UInt32);
    mypcmd.Parameters.Add("?fname", MySqlDbType.VarChar, 20);
    mypcmd.Parameters.Add("?middlename", MySqlDbType.VarChar, 20);
    mypcmd.Parameters.Add("?surname", MySqlDbType.VarChar, 40);
    mypcmd.Parameters.Add("?title", MySqlDbType.VarChar, 6);
    mypcmd.Parameters.Add("?contactno", MySqlDbType.VarChar, 40);
    mypcmd.Parameters.Add("?mobileno", MySqlDbType.VarChar, 40);
    mypcmd.Parameters.Add("?sex", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?dob", MySqlDbType.Date);
    mypcmd.Parameters.Add("?housename", MySqlDbType.VarChar, 20);
    mypcmd.Parameters.Add("?houseno", MySqlDbType.VarChar, 20);
    mypcmd.Parameters.Add("?address1", MySqlDbType.TinyText);
    mypcmd.Parameters.Add("?address2", MySqlDbType.TinyText);
    mypcmd.Parameters.Add("?address3", MySqlDbType.TinyText);
    mypcmd.Parameters.Add("?address4", MySqlDbType.TinyText);
    mypcmd.Parameters.Add("?postcode", MySqlDbType.TinyText);
    mypcmd.Parameters.Add("?notes", MySqlDbType.Text);
    mypcmd.Parameters.Add("?home", MySqlDbType.UInt16);
    mypcmd.Parameters.Add("?sp1", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?sp2", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?sp3", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?sp4", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?sp5", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?sp6", MySqlDbType.VarChar, 1);
    mypcmd.Parameters.Add("?email", MySqlDbType.Text);

    var prodr = procmd.ExecuteReader();
    while (prodr.Read())
    {
        foreach (MySqlParameter p in mypcmd.Parameters)
        {
            p.Value = prodr[p.ParameterName.Replace("?", "")].ToString().Trim();
        }
    }
    mypcmd.ExecuteNonQuery();

我在想什么？请帮助！

What am I missing? Please help!

推荐答案

刚刚发现出了什么问题了。必须从SQL查询中删除单引号。工程就像现在的魅力。

Just found out what the issue was. Have to remove single quotes from the SQL query. Works like a charm now.

在修复多么简单了，希望这将不遗余力别人我的头痛恼火。

Irritated at how simple the fix was, hopefully this will spare someone my headache.

这篇关于C＃+ MySQL的预处理语句瓦特/重复键更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！