Syntax Error INSERT INTO Statement – ADO.Net Solved


This is a common error occurred during the database operation in ADO.Net. The cause for this error is the column name matches some reserved words. To solve this issue suffix and prefix with “] and [” in column names.

Command object

If you are using command object use the following method in the insert statement.

OleDbCommand cmd = new OleDbCommand(“insert into acgroup(gname,parent,[note]) values(‘” + gname + “‘,'” + gparent + “‘,'” + gdescription + “‘” + “)”, Globals.con);
cmd.ExecuteScalar();

here ‘note’ cause the error.

Dataset and Command builder

If you are using dataset, adapter and command builder to insert records, use command builder Quickfix method to solve the issue as follows

dr = Globals.GroupDataSet.Tables[0].NewRow();
dr[“gname”] = gname;
dr[“parent”] = gparent;
dr[“note”] = gdescription;
Globals.cmdbuilder = new OleDbCommandBuilder(Globals.GroupAdapter);
Globals.cmdbuilder.QuotePrefix = “[“;
Globals.cmdbuilder.QuoteSuffix = “]”;
Globals.GroupDataSet.Tables[0].Rows.Add(dr);
Globals.GroupAdapter.InsertCommand = Globals.cmdbuilder.GetInsertCommand();
int stat = Globals.GroupAdapter.Update(Globals.GroupDataSet.Tables[0]);
if (stat > 0)
{
MessageBox.Show(“Group Information saved”);
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.