Error: Column count doesn't match value count at row 1 - But the query works in Navicat

Question

I am trying to get the parameters from a table and insert them into a different table but I always get this error: Error: Column count doesn't match value count at row 1

The funny part is that it does insert a few rows and for some I get the error. I have checked the number of columns to match with the number of values and everything seems right. And whenever I debug the code and select the content of my Insert command and execute it into Navicat it works.

Does anyone has any ideas why I'm getting this error?

This is my code:

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

This may be happening cauz of a kind of injection. Adding parameters is a better way. I'll show you an example of the cause

I have a table with cols : Name, Age, Address

If i try to insert some value like

'Salim', 28, 'St.Anthony's Colony, Chennai's 27th AVN'

.

The supplied values here will be :

'Salim',

28,

'St.Anthony's Colony,

Chennai's 27th AVN'

.

The sql engine first looks for the supplied values count and return this kind of error.

.

You can use Parameters like this

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