Error when trying to insert into MS Access Database

-1

I am developing a program that uses a relational database. In one particular form I am trying to insert new products information into the database.

using System.Data.OleDb;

When I try to save a new product this code runs...

connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "insert into Products (ProductName,ProductSKU,RP,WP,Stock,FPS,Ammo,MagazineCap,HopUp,Range,Brand,Colour,Action,Features) values('" + txt_ProductName.Text + "','" + txt_SKU.Text + "'," + txt_RP.Text + "," + txt_WP.Text + "," + numericUpDown_Inventory.Value + "," + cobo_FPS.Text + ",'" + cobo_Ammo.Text + "'," + cobo_MagazineCap.Text + ",'" + cobo_HopUp.Text + "'," + cobo_Range.Text + ",'" + cobo_Brand.Text + "','" + cobo_Colour.Text + "','" + cobo_Action.Text + "','" + txt_Features.Text + "')";
//Action field currently causes an error
MessageBox.Show(query);
command.CommandText = query;
command.ExecuteNonQuery();
connection.Close();

...and an error is thrown

"Error System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement."

(and then a bunch of stuff which I don't think is important)

Apologies for the HUGE SQL query. I am using the exact same method of using the insert SQL query in several other places in my program and they all work completely fine. This example however is causing this error. Through the tedious process of "commenting out" individual parts of my SQL query I found that the error lies with the "Action" field. I have checked that the data type in my database is correct and that I am using the '' punctuation to surround the text string that is being inserted into the database.

I think I've checked everything, so why am I still getting this error?

Many thanks in advance and if more information is required just let me know ;)

c#
sql
database
ms-access
oledb
asked on Stack Overflow Apr 2, 2015 by Oli • edited Apr 2, 2015 by Soner Gönül

1 Answer

4

Action is a reserved keyword in OLE DB Provider. You need to use it with square brackets like [Action]. As a best practice, change it to non-reserved word.

But more impontant

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your OleDbConnection and OleDbCommand automatically instead of calling .Close() method manually.

answered on Stack Overflow Apr 2, 2015 by Soner Gönül • edited May 23, 2017 by Community

User contributions licensed under CC BY-SA 3.0