"INSERT 语句中的列数多于 VALUES 子句中指定的值" C# 到 SQL

Question

我正在尝试将数据插入基于Excel文件的SQL Server。一切都很好，直到客户要求我在单击单选按钮时插入列。因此，我设置了一个条件，其中单选按钮单击然后插入值。问题是我得到一个异常"INSERT 语句中的列比 VALUES 子句中指定的值多。VALUES 子句中的值数必须与 INSERT 语句中指定的列数匹配。

如何解决这个问题，请给我为什么会发生这种情况？ 谢谢。

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");
}

Answer 1

不是直接的答案，但这是您应该如何处理参数 - 在这种情况下，通过"dapper"(在 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
});

看看它有多容易理解？另外：它现在 100% 不受 SQL 注入和区域设置问题(如日期和数字格式(的影响。使用正确的参数化可以解决几乎所有与理解级联 SQL 问题相关的问题。