oledb exception syntax error in insert into statem

Question

I'm having and exception in my insert into statement.But the data is inserting to the table correctly. can someone please show me the error in this code.

private void btnAddNewSale_Click(object sender, EventArgs e)
{     
            string StrQuery;
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            for (int i = 0; i < DataGridViewAddSale.Rows.Count; i++)
            {
                StrQuery = "insert into BillItem (billNumber,storeItemNumber,numberOfItems,priceForEach,totalValue) values (" + txtBillNo.Text + ", "+ DataGridViewAddSale.Rows[i].Cells["ColCordNo"].Value + ", "+ DataGridViewAddSale.Rows[i].Cells["ColQty"].Value + ", " + DataGridViewAddSale.Rows[i].Cells["ColUnitPrice"].Value + " ," + DataGridViewAddSale.Rows[i].Cells["ColTotalValue"].Value + ");";
                command.CommandText = StrQuery;
                command.ExecuteNonQuery();
            }
        }

Below isthe exception

System.Data.OleDb.OleDbException (Ox8004OE14): Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OIeDbHResult br)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Oata.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OIeDb.OleDbCommand.ExecuteReaderlnternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQueryo
at Bsystem_1 ._1 .Form2.btnAddNewSale_Click(Object sender, EventArgs e) in c:\Users\jagath\Documents\Visual Studio 2013\Projects\Bsystem 1.1\Bsystem
1.1\Form2.cs:line 166
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, 1nt32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.BuftonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, 1nt32 msg, IntPtr wparam, IntPtr Ipa ram)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
dwComponentlD, 1nt32 reason, 1nt32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLooplnner(1nt32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(1nt32 reason, ApplicationContext context)
at System.Windows.Forms.Application.RunDialog(Form form)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at System.Windows.Forms.Form.ShowDialogO
at Bsystem_1 ._1 .Forml .btnjogin_Click(Object sender, EventArgs e) in c:\Users\jagath\Documents\Visual Studio 2013\Projects\Bsystem 1.1\Bsystem
1.1\Forml.cs:line 49

Answer 1

Check whether you have assigned any foreign key (relationships) in that table ,that is not valid.

Answer 2

Check column's names and data type. Then use CommandParameter to avoid sql injection. I also recommend you to encapsulate the table name with square brackets if you use a reserved word as table name.

using (var command = new OleDbCommand
{
    Connection = connection,
    CommandText =
        "insert into [BillItem] (billNumber, storeItemNumber, numberOfItems, priceForEach, totalValue) values (?, ?, ?, ?, ?)"
})
{
    for (int i = 0; i < DataGridViewAddSale.Rows.Count; i++)
    {
        command.Parameters.Clear();
        var rowCells = DataGridViewAddSale.Rows[i].Cells;
        command.Parameters.AddWithValue("@p1", txtBillNo.Text);
        command.Parameters.AddWithValue("@p2", rowCells["ColCordNo"].Value);
        command.Parameters.AddWithValue("@p3", rowCells["ColQty"].Value);
        command.Parameters.AddWithValue("@p4", rowCells["ColUnitPrice"].Value);
        command.Parameters.AddWithValue("@p5", rowCells["ColTotalValue"].Value);

        command.ExecuteNonQuery();
    }
}

Answer 3

As above suggested, you must use parameterized query to protect sql-injection..

insert into [BillItem] (billNumber, storeItemNumber, numberOfItems, priceForEach, totalValue) 
values (@para1,@para2,@para3,...)

//and add into command
command.Parameters.AddWithValue("@para1", txtBillNo.Text);

The above error comes, when your value have Sqlserver reserved word, most likely single quote like charlie's angels or < or > or & in xml field