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
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();
}
}
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
Check whether you have assigned any foreign key (relationships) in that table ,that is not valid.
User contributions licensed under CC BY-SA 3.0