c# - 错误: Column count doesn't match value count at row 1 - But the query works in Navicat

Question

我试图从表中获取参数并将它们插入到另一个表中，但总是收到此错误:错误:列计数与第 1 行的值计数不匹配

有趣的是，它确实插入了几行，而对于某些行，我收到了错误。我已经检查了列数与值的数量相匹配，一切似乎都正确。每当我调试代码并选择“插入”命令的内容并将其执行到 Navicat 中时，它都会起作用。

有人知道我为什么会收到此错误吗？

这是我的代码:

private void CopylmtValues()
{
        if (ObjSqlAccess.Connected)
        {

            DtSqlSettings.Clear();

            string InsertLmtPara = "";
            int rows2 = 0;

            progressBar1.Visible = true;
            progressBar1.Value = 0;
            progressBar1.Minimum = rows2;
            progressBar1.Maximum = DtSqlSettings.Rows.Count;


            string GetlimitsVls = "Select * from `" + comboBox1.Text + "`.`limitvalues` where LimitID = (Select LimitID from `"+ comboBox1.Text +"`.`limits` where LimitChecksum = '" + comboBox8.Text + "')";                                    
            DtSqlSettings = ObjSqlAccess.GetDataTableFromTable(GetlimitsVls);

            label8.ResetText();
            label8.Text = "" + DtSqlSettings.Rows.Count + " rows has been found!";

            string lmtval = "";
            string lmt1 = "";
            string lmt2 = "";
            string lmt3 = "";
            string lmt4 = "";
            string lmt5 = "";
            string lmt6 = "";
            string lmt7 = "";
            string lmt8 = "";
            string lmt9 = "";
            string lmt10 = "";
            string lmt11 = "";
            string lmt12 = "";

            bool Getrows2 = false;
            //bool CheckDuplication = false;

            if (DtSqlSettings != null)
            {
                 while(rows2 != DtSqlSettings.Rows.Count)
                {
                    string getlmtID = "Select distinct LimitID from `" + comboBox1.Text + "`.limits where LimitChecksum = '" + comboBox8.Text + "'";
                    limitID = ObjSqlAccess.GetDataTableFromTable(getlmtID);


                        lmtval = limitID.Rows[0][0].ToString();
                        lmt1 = DtSqlSettings.Rows[rows2][2].ToString();
                        lmt2 = DtSqlSettings.Rows[rows2][3].ToString();
                        lmt3 = DtSqlSettings.Rows[rows2][4].ToString();
                        lmt4 = DtSqlSettings.Rows[rows2][5].ToString();
                        lmt5 = DtSqlSettings.Rows[rows2][6].ToString();
                        lmt6 = DtSqlSettings.Rows[rows2][7].ToString();
                        lmt7 = DtSqlSettings.Rows[rows2][8].ToString();
                        lmt8 = DtSqlSettings.Rows[rows2][9].ToString();
                        lmt9 = DtSqlSettings.Rows[rows2][10].ToString();
                        lmt10 = DtSqlSettings.Rows[rows2][11].ToString();
                        lmt11 = DtSqlSettings.Rows[rows2][12].ToString();
                        lmt12 = DtSqlSettings.Rows[rows2][13].ToString();

                        if(!Getrows2)
                        {

                             InsertLmtPara = "INSERT INTO `" + comboBox2.Text + "`.limitvalues " +
                                            "(`LimitID`,`Location`,`TestID`,`Description`,`LimitType`,`UnitType`,`LowLimit`,`HighLimit`,`NominalValue`,`StringLimit`,`TestFunction`,`ID`,`SubID`)" +
                                            " VALUES ('"+ lmtval +"','" + lmt1 + "','" + lmt2 + "','" + lmt3 + "','" + lmt4 + "','" + lmt5 + "'," + lmt6 + "," + lmt7 + ",'" + lmt8 + "','" + lmt9 + "','" + lmt10 + "','" + lmt11 + "','" + lmt12 + "')";
                            int InsertPARA = ObjSqlAccess.ExecuteNoneQuery(InsertLmtPara);

                            progressBar1.PerformStep();

                            if(rows2 == DtSqlSettings.Rows.Count)
                            {
                                Getrows2 = true;

                            }

                            rows2++;
                    }
                }
            }
        }
    }

Answer 1

这可能是由于某种注入(inject)而发生的。添加参数是更好的方法。我将向您展示原因的示例

我有一个带有列的表格:姓名、年龄、地址

如果我尝试插入一些值，例如

“Salim”，28 岁，“钦奈第 27 AVN 圣安东尼殖民地”

.

此处提供的值将是:

“萨利姆”，

28，

“圣安东尼殖民地”，

钦奈第 27 届 AVN”

.

sql 引擎首先查找提供的值 count 并返回此类错误。

.

您可以像这样使用参数

    string name = "Salim";
    string addr = "St.Anthony's Colony, Chennai's 27th AVN";
    int age = 28;

    string query = "Insert Into StaffDetails ([Name], [Age], [Address]) Values (@name, @age, @address)";

    IDbCommand comm = new IDbCommand (query, connection);

    comm.Parameters.AddWithValue("@name", name);
    comm.Parameters.AddWithValue("@age", age);
    comm.Parameters.AddWithValue("@address", addr);

    comm.ExecuteNonQuery();