'There are more columns in the INSERT statement than values specified in the VALUES clause' C# to SQL

Question

I'm trying to insert data to SQL Server based on Excel File. Everything is fine until the client ask me to insert column if the radioButton is clicked. So, I make a condition where radioButtonClicked then insert the values. The problem is I get an exception 'There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.'

How to fix this and please give me reason why is this happened? Thanks.

            koneksi.Open();

            string df = "SET DATEFORMAT mdy;";
            SqlCommand cmd_df = new SqlCommand(df, koneksi);
            cmd_df.ExecuteNonQuery();
            string sql = "delete from absenTA DBCC CHECKIDENT('absenTA', RESEED, 0)";
            SqlCommand cmd = new SqlCommand(sql, koneksi);
            cmd.ExecuteNonQuery();
            foreach (DataTable table in result.Tables)
            {
                foreach (DataRow dr in table.Rows)
                {
                    string sql_insert = "";

                    if (metroRBOff.Checked||metroRBON.Checked) //tanggalberubah == true
                    {
                        MessageBox.Show("Tanggal Berubah");
                        //dr[2] = textBoxDateTimeOnOff.Text;
                        //Convert.ToDateTime(dr[2]);
                        if (metroRBON.Checked)
                        {
                            sql_insert = "insert into absenTA(id, nama, tanggal, hari, first_in, last_out,on_off) values (" +
                                    dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                                    dateTimePicker + "'" + "," + "'" + dr[3] + "'" + "," + "'" +
                                    dr[4] + "'" + "," + "'" + dr[5] + "'" + "'" + metroRBON.Text + "'" + "); ";
                        }
                        else
                        {
                            sql_insert = "insert into absenTA(id, nama, tanggal, hari, first_in, last_out,on_off) values (" +
                                    dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                                    dateTimePicker + "'" + "," + "'" + dr[3] + "'" + "," + "'" +
                                    dr[4] + "'" + "," + "'" + dr[5] + "'" + "'" + metroRBOff.Text + "'" + "); ";
                        }
                    }

                    else if (dr[4] == DBNull.Value && dr[5] == DBNull.Value)
                    {
                        sql_insert = "insert into absenTA(id, nama, tanggal, hari) values (" +
                              dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                              dr[2] + "'" + "," + "'" + dr[3] + "'" + ");";
                    }
                    else if (dr[4] == DBNull.Value)
                    {
                        sql_insert = "insert into absenTA(id, nama, tanggal, hari, last_out) values (" +
                              dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                              dr[2] + "'" + "," + "'" + dr[3] + "'"+ "," + "'"+ dr[5] + "'" + ");";
                    }
                    else if (dr[5] == DBNull.Value)
                    {
                        sql_insert = "insert into absenTA(id, nama, tanggal, hari, first_in) values (" +
                              dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                              dr[2] + "'" + "," + "'" + dr[3] + "'" + "," + "'" + dr[4]+"'" +");";
                    }
                    else
                    {
                        sql_insert = "insert into absenTA(id, nama, tanggal, hari, first_in, last_out) values (" +
                        dr[0] + "," + "'" + dr[1] + "'" + "," + "'" +
                        dr[2] + "'" + "," + "'" + dr[3] + "'" + "," + "'" +
                        dr[4] + "'" + "," + "'" + dr[5] + "'" + ");";
                    }

                    SqlCommand cmd_insert = new SqlCommand(sql_insert, koneksi);
                    //cmd_insert.Parameters.Add("@tanggal", SqlDbType.Date).Value = dateTimePickerOnOff.Value.Date;
                    cmd_insert.ExecuteNonQuery();
                    /*absenTA addtable = new absenTA()
                        {

                            id = Convert.ToInt32(dr[0]),
                            nama = Convert.ToString(dr[1]),
                            tanggal = Convert.ToDateTime(dr[2]),
                            hari = Convert.ToString(dr[3]),
                            first_in = Convert.ToDateTime(dr[4]),
                            last_out = Convert.ToDateTime(dr[5])
                        };
                        conn.absenTAs.InsertOnSubmit(addtable);
                    }*/


                }
                koneksi.Close();
                //conn.SubmitChanges();
                MessageBox.Show("Konversi Data Sukses");
            }

What is the *exact* value of `sql_insert` when it fails? Also, you should be using parameters to avoid SQL Injections (as well as these typo problems) — Camilo Terevinto, Aug 11 '18 at 22:48
As already noted, you should **never, ever** build SQL like this - it is incredibly dangerous. It is also not necessary to concat the values character by character (`+","+"'"` etc) - it would be *much* more readable as a paramerized verbatim string literal. But; find the *actual* value of `sql_insert` *after* this, and either tell us *that*, or look at it carefully yourself. — Marc Gravell, Aug 11 '18 at 22:57
Hi, thanks for the answer. I found out that my sql syntax were typo and to secure. However I'm new in sql and this program is just for dekstop use. — Venta11, Aug 11 '18 at 23:02
@Venta11 desktop users can be evil, too :) often accidentally... — Marc Gravell, Aug 11 '18 at 23:03

score 1 · Answer 1 · answered Aug 11 '18 at 23:02

Not a direct answer, but here's how you should be dealing with parameters - in this case taken a step further via "dapper" (free on nuget):

connection.Execute(@"
insert into absenTA(id, nama, tanggal, hari, first_in, last_out, on_off)
values (@id, @nama, @tanggal, @hari, @first_in, @last_out, @on_off)",
   new {
       id = (int)dr[0],
       nama = (string)dr[1],
       tanggal = (DateTime)dateTimePicker,
       //... for the rest
  });

See how easier it is to understand? Also: it is now 100% safe from SQL injection and from locale issues such as date and number formats. Using proper parameterization is the fix to almost all issues that relate to understanding concatenated SQL problems.

By the way, one polite way to say _Thank You_ for an answer on Stack Overflow is an up-vote. And, if it fixes your issue, mark the answer as "accepted". — Flydog57, Aug 11 '18 at 23:21

'There are more columns in the INSERT statement than values specified in the VALUES clause' C# to SQL

1 Answers1