INSERT INTO syntax error in c#

Question

please figure out the error in my code.it show syntax error INSERT INTO statement.

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

show us your cmd for more information and mistakes and your table structure — Evolutio, Oct 08 '15 at 08:46
Please use ***PARAMETRIZED QUERIES!*** Don't just concatenate together your SQL statements - that's a horrible practice leaving you open to **SQL injection** attacks - the #1 vulnerability out there on the web ... — marc_s, Oct 08 '15 at 08:50
sno Autoincrement invoice NUmber datetime text custm text total number tax number grand number — Priyanka, Oct 08 '15 at 09:03

Dmitry Bychenko · Answer 1 · 2015-10-08T10:01:08.813

It seems that you've commited all the sins possible in this short fragment. Something like that is expected:

// 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)

score 2 · Answer 2 · answered Oct 08 '15 at 09:24

Apart from your weird INSERT statement, your column name datetime is a reserve word in Access. You should escape it suing [] like below.

INSERT INTO tbbill(invoice,[datetime],custm,total,tax,grand)

Your current query is open to SQL Injection and so as suggested in comment consider using parameterized query instead.

Chawin · Accepted Answer · 2015-10-08T09:40:16.997

This should work:

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

EDIT:

As stated by others, your query is still open to SQL injection. Dmitry's answer will be the safest and efficient option.

INSERT INTO syntax error in c#

3 Answers3