C# MySql 插入或更新具有 78 个单选按钮的调查表单的命令语法

Question

我有一个 C# Win 表单，其中包含(除其他外)一个弹出面板，其中包含一个包含 78 个单选按钮的调查。该调查有一个提交按钮，可将数据插入 MySQL 表中。点击事件背后的代码应该允许更新和插入(因为人们可能会分几个阶段填写调查)。我遇到的问题是 MySQL INSERT 命令似乎存在语法问题，因为单击“提交”时会引发以下异常...

Error in SQL syntax near " at line 1

我的 INSERT 语句当前是

MySqlCommand cmd = new MySqlCommand("INSERT INTO central_db.azt_image VALUES ('" + SerialCombo.Text + "', '" + Global.cust_dfe + "', '" + EngImgcomboBx.Text + "', '" + SerialCombo.Text + "', '" + AssetLocTxtBx.Text + "', '" + AssetNameTxtBx.Text + "', '" + (ImgL1Pend.Checked ? "1" : "0") + "', '" + (ImgL2Pend.Checked ? "1" : "0") + "', '" + (ImgL3Pend.Checked ? "1" : "0") + "', '" + (ImgL4Pend.Checked ? "1" : "0") + "', '" + (ImgL5Pend.Checked ? "1" : "0") + "', '" + (ImgL6Pend.Checked ? "1" : "0") + "', '" + (ImgL7Pend.Checked ? "1" : "0") + "', '" + (ImgL8Pend.Checked ? "1" : "0") + "', '" + (ImgL9Pend.Checked ? "1" : "0") + "', '" + (ImgL10Pend.Checked ? "1" : "0") + "', '" + (ImgL11Pend.Checked ? "1" : "0") + "', '" + (ImgL12Pend.Checked ? "1" : "0") + "', '" + (ImgL13Pend.Checked ? "1" : "0") + "', '" + (ImgL14Pend.Checked ? "1" : "0") + "', '" + (ImgL15Pend.Checked ? "1" : "0") + "', '" + (ImgL16Pend.Checked ? "1" : "0") + "', '" + (ImgL17Pend.Checked ? "1" : "0") + "', '" + (ImgL18Pend.Checked ? "1" : "0") + "', '" + (ImgL19Pend.Checked ? "1" : "0") + "', '" + (ImgL20Pend.Checked ? "1" : "0") + "', '" + (ImgL21Pend.Checked ? "1" : "0") + "', '" + (ImgL22Pend.Checked ? "1" : "0") + "', '" + (ImgL23Pend.Checked ? "1" : "0") + "', '" + (ImgL24Pend.Checked ? "1" : "0") + "', '" + (ImgL25Pend.Checked ? "1" : "0") + "', '" + (ImgL26Pend.Checked ? "1" : "0") + "', '" + (ImgL1NA.Checked ? "1" : "0") + "', '" + (ImgL2NA.Checked ? "1" : "0") + "', '" + (ImgL3NA.Checked ? "1" : "0") + "', '" + (ImgL4NA.Checked ? "1" : "0") + "', '" + (ImgL5NA.Checked ? "1" : "0") + "', '" + (ImgL6NA.Checked ? "1" : "0") + "', '" + (ImgL7NA.Checked ? "1" : "0") + "', '" + (ImgL8NA.Checked ? "1" : "0") + "', '" + (ImgL9NA.Checked ? "1" : "0") + "', '" + (ImgL10NA.Checked ? "1" : "0") + "', '" + (ImgL11NA.Checked ? "1" : "0") + "', '" + (ImgL12NA.Checked ? "1" : "0") + "', '" + (ImgL13NA.Checked ? "1" : "0") + "', '" + (ImgL14NA.Checked ? "1" : "0") + "', '" + (ImgL15NA.Checked ? "1" : "0") + "', '" + (ImgL16NA.Checked ? "1" : "0") + "', '" + (ImgL17NA.Checked ? "1" : "0") + "', '" + (ImgL18NA.Checked ? "1" : "0") + "', '" + (ImgL19NA.Checked ? "1" : "0") + "', '" + (ImgL20NA.Checked ? "1" : "0") + "', '" + (ImgL21NA.Checked ? "1" : "0") + "', '" + (ImgL22NA.Checked ? "1" : "0") + "', '" + (ImgL23NA.Checked ? "1" : "0") + "', '" + (ImgL24NA.Checked ? "1" : "0") + "', '" + (ImgL25NA.Checked ? "1" : "0") + "', '" + (ImgL26NA.Checked ? "1" : "0") + "', '" + (ImgL1Comp.Checked ? "1" : "0") + "', '" + (ImgL2Comp.Checked ? "1" : "0") + "', '" + (ImgL3Comp.Checked ? "1" : "0") + "', '" + (ImgL4Comp.Checked ? "1" : "0") + "', '" + (ImgL5Comp.Checked ? "1" : "0") + "', '" + (ImgL6Comp.Checked ? "1" : "0") + "', '" + (ImgL7Comp.Checked ? "1" : "0") + "', '" + (ImgL8Comp.Checked ? "1" : "0") + "', '" + (ImgL9Comp.Checked ? "1" : "0") + "', '" + (ImgL10Comp.Checked ? "1" : "0") + "', '" + (ImgL11Comp.Checked ? "1" : "0") + "', '" + (ImgL12Comp.Checked ? "1" : "0") + "', '" + (ImgL13Comp.Checked ? "1" : "0") + "', '" + (ImgL14Comp.Checked ? "1" : "0") + "', '" + (ImgL15Comp.Checked ? "1" : "0") + "', '" + (ImgL16Comp.Checked ? "1" : "0") + "', '" + (ImgL17Comp.Checked ? "1" : "0") + "', '" + (ImgL18Comp.Checked ? "1" : "0") + "', '" + (ImgL19Comp.Checked ? "1" : "0") + "', '" + (ImgL20Comp.Checked ? "1" : "0") + "', '" + (ImgL21Comp.Checked ? "1" : "0") + "', '" + (ImgL22Comp.Checked ? "1" : "0") + "', '" + (ImgL23Comp.Checked ? "1" : "0") + "', '" + (ImgL24Comp.Checked ? "1" : "0") + "', '" + (ImgL25Comp.Checked ? "1" : "0") + "', '" + (ImgL26Comp.Checked ? "1" : "0") + "', '" + ImgNoteTxt.Text + "') ON DUPLICATE KEY UPDATE;", conn);

由于每个 INSERT 或 UPDATE 的每一列总是有一个值，所以我没有声明列标题。我不是 MySQL 专家(远非如此)，我不确定问题是否在于命令的结构或语法......任何帮助都会得到极大的帮助，因为我已经为此奋斗了几天。

Answer 1

好的，所以我发现了我的 INSERT 语句的问题...在 INSERT 语句后使用 ON DUPLICATE KEY UPDATE 选项时，您必须显式设置要更新的值，即

INSERT INTO some_database.some_table ( key, first_name, last_name, current_score) VALUES ("123", "John", "Doe", 69) ON DUPLICATE KEY UPDATE current_score=69;

一旦我明确列出了我想要更新的列，当发现重复键时，INSERT 语句就会正常工作...

感谢您的评论，StackOverflow 社区非常棒...