使用MySQL c#插入查询中的神秘错误

Question

我一直试图在MySQL查询中找到一个错误，时间太长了。我所要做的就是将两个值插入到我的数据库中。只要我不包括"longText"的值，这个查询就能正常工作，而且我现在完全知道这个简单的查询是什么引起了问题。

下面是MySQL异常消息：

MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'longText) VALUES ('191', '529', '', '1', 'index', CURRENT_TIMESTAMP, ' at line 1

Zeile 548:        cmd.Connection = con;
Zeile 549:        cmd.CommandText = "INSERT INTO confirmationfreetext (confirmationId, customer, shortText, sorting, addedBy, entryDate, longText) VALUES (@confirmationId, @customer, @shortText, @sorting, @addedBy, CURRENT_TIMESTAMP, @longText)";
Zeile 550:        cmd.ExecuteNonQuery();
Zeile 551:        con.Close();

[MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'longText) VALUES ('191', '529', '', '1', 'index', CURRENT_TIMESTAMP, ' at line 1]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +275
   MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) +116
   MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId) +54
   MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId) +132
   MySql.Data.MySqlClient.MySqlDataReader.NextResult() +707
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1620
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +37
   MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() +58
   editConfirmation.saveFreeTextButton_Click(Object sender, EventArgs e) in e:\aspnet_projects\ammon\editConfirmation.aspx.cs:550
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9614758
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

这是相关的asp.net c#代码：

        //connect to database
        MySqlConnection con = new MySqlConnection();
        con.ConnectionString = Helper.CONNECTION_STRING;
        MySqlCommand cmd = null;

        //add freetext to db
        con.Open();
        cmd = new MySqlCommand();
        cmd.Parameters.AddWithValue("@confirmationId", Session["currentConfirmationId"].ToString());
        cmd.Parameters.AddWithValue("@customer", Session["currentCustomerId"].ToString());
        cmd.Parameters.AddWithValue("@shortText", shortText.Text);
        cmd.Parameters.AddWithValue("@longText", longText.Text);
        cmd.Parameters.AddWithValue("@sorting", "1");
        cmd.Parameters.AddWithValue("@addedBy", Session["authenticatedUser"].ToString());
        cmd.Parameters.AddWithValue("@entryDate", "CURRENT_TIMESTAMP");
        cmd.Connection = con;
        cmd.CommandText = "INSERT INTO confirmationfreetext (confirmationId, customer, shortText, sorting, addedBy, entryDate, longText) VALUES (@confirmationId, @customer, @shortText, @sorting, @addedBy, CURRENT_TIMESTAMP, @longText)";
        cmd.ExecuteNonQuery();
        con.Close();

我可以将值更改为

cmd.Parameters.AddWithValue("@longText", 'blablabla');

或者任何我想要的，我总是会犯同样的错误。如果手动将值写入MySQL查询中，也会得到相同的错误。

下面是相关的MySQL表：

CREATE  TABLE `ammon`.`confirmationfreetext` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `confirmationId` INT NULL ,
  `customer` INT(11) NULL ,
  `shortText` VARCHAR(255) NULL ,
  `longText` VARCHAR(255) NULL ,
  `sorting` INT NULL ,
  `entryDate` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ,
  `addedBy` VARCHAR(128) NULL ,
  PRIMARY KEY (`id`) );

我希望任何人都能对这个错误说点什么。提前结束。

Answer 1

请尝试使用以下代码：

//connect to database
    MySqlConnection con = new MySqlConnection();
    con.ConnectionString = Helper.CONNECTION_STRING;
    MySqlCommand cmd = null;

    //add freetext to db
    con.Open();
    cmd = new MySqlCommand();
    cmd.Parameters.AddWithValue("?confirmationId", Session["currentConfirmationId"].ToString());
    cmd.Parameters.AddWithValue("?customer", Session["currentCustomerId"].ToString());
    cmd.Parameters.AddWithValue("?shortText", shortText.Text);
    cmd.Parameters.AddWithValue("?longText", longText.Text);
    cmd.Parameters.AddWithValue("?sorting", "1");
    cmd.Parameters.AddWithValue("?addedBy", Session["authenticatedUser"].ToString());
    cmd.Parameters.AddWithValue("?entryDate", "CURRENT_TIMESTAMP");
    cmd.Connection = con;
    cmd.CommandText = "INSERT INTO confirmationfreetext (confirmationId, customer, shortText, sorting, addedBy, entryDate, longText) VALUES (?confirmationId, ?customer, ?shortText, ?sorting, ?addedBy, CURRENT_TIMESTAMP, ?longText)";
    cmd.ExecuteNonQuery();
    con.Close();