c#中INSERT INTO语法错误
本文关键字:语法 错误 INTO INSERT | 更新日期: 2023-09-27 18:16:31
请找出我代码中的错误。INSERT INTO语句显示语法错误
OleDbCommand cmd = new OleDbCommand("INSERT INTO tbbill(invoice,datetime,custm,total,tax,grand)VALUES(" + Convert.ToInt32(txtinvoice.Text) + ",'" + dateTimePicker1.Value.ToString("yyyy/MMM/dd") + "','" + Convert.ToString(txtcn.Text) + "','" + txtttl.Text + "','" + Convert.ToInt32(cmtax.Text) + "','" + txtgrdttl.Text + "')", con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();
似乎你在这段简短的片段中犯了所有可能的错误。
// Make SQL readable
String sql =
@"INSERT INTO tbbill(
invoice,
[datetime], /* reserved word */
custm,
total,
tax,
grand)
VALUES(
?, ?, ?, ?, ?, ?)"; // Make SQL parametrized
// Put IDisposable into "using"
using (OleDbCommand cmd = new OleDbCommand(sql, con)) {
// Parameterized
cmd.Parameters.Add(txtinvoice.Text);
cmd.Parameters.Add(dateTimePicker1.Value);
cmd.Parameters.Add(txtcn.Text);
cmd.Parameters.Add(txtttl.Text);
cmd.Parameters.Add(cmtax.Text);
cmd.Parameters.Add(txtgrdttl.Text);
cmd.ExecuteNonQuery();
}
// Do not close that's not opened by you (i.e. con)
除了您奇怪的INSERT
语句,您的列名datetime
是Access中的保留字。你应该像下面这样用[]
来转义。
INSERT INTO tbbill(invoice,[datetime],custm,total,tax,grand)
您当前的查询是对SQL注入开放的,因此建议在评论中考虑使用参数化查询。
应该可以:
OleDbCommand cmd = new OleDbCommand(@"INSERT INTO tbbill(invoice,[datetime],custm,total,tax,grand)
VALUES(" + Convert.ToInt32(txtinvoice.Text) + ",'"" +
dateTimePicker1.Value.ToString("yyyy/MMM/dd") + "'",'"" +
Convert.ToString(txtcn.Text) + "'",'"" + txtttl.Text + "'",'"" +
Convert.ToInt32(cmtax.Text) + "'",'"" + txtgrdttl.Text + "'")", con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();
编辑:正如其他人所说,您的查询仍然对SQL注入开放。德米特里的答案将是最安全、最有效的选择。