Syntax Error in Insert Into Statement C# OleDb

Question

I am creating an order form using C# OleDb and am having an issue with attempting to put the size value from a combo box in the order form into the database. The code that causes the issue is below.

 private void btn_Save_Click(object sender, EventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "insert into OrderForm(Size) values ('" + sizeBox.Text + "')";
            command.ExecuteNonQuery();
            MessageBox.Show("Order Inserted into Database");
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error " + ex);
        }
    }

This is the only piece of code that is causing me an issue and i've compared it to my other commandText lines of code that i've commented out to ensure that the syntax is correct and it looks okay to me. I have also checked to ensure that the field name listed into the commandText code and the field in the actual database are correct and it is. Any help on this issue will be greatly appreciated. Cheers

Edit: Syntax Error that the code is giving me

Could it be that Size is of numeric type (e.g. integer)? If so - you don't need single quote in your query — dotnetom, Jul 19 '15 at 13:47
What is the data type of `Size` and what is the value of `sizeBox.Text`? — Patrick Hofman, Jul 19 '15 at 13:47
Also NEVER EVER concatenate query like you just did, because you are vulnerable to [SQL injection attack](https://en.wikipedia.org/wiki/SQL_injection) — dotnetom, Jul 19 '15 at 13:47
What is the exception message? What is `command.CommandText`? — CodeCaster, Jul 19 '15 at 13:50
I read it 3 times but I am unable to understand what issue does it cause? You just say "causes me an issue". Anyway, may be duplicate of http://stackoverflow.com/questions/10941284/how-to-insert-a-record-into-a-access-table-using-oledb ? — Erki M., Jul 19 '15 at 13:50
the data type of the size field in the form is a combobox whereas the datatype of size in the database is Text. — user3158314, Jul 19 '15 at 13:51
Also, understand that if your `ExecuteNonQuery` throws an exception your connection will remain open. Consider moving it to `finally` block or use the [`using`](https://msdn.microsoft.com/en-us/library/yh598w02.aspx) statement — Erki M., Jul 19 '15 at 13:55
@Erki M. I just edited the code to close the connection if an exception is caught. Cheers :) — user3158314, Jul 19 '15 at 14:02

Steve · Accepted Answer · 2015-07-19T14:14:14.153

SIZE is a reserved word. You cannot use it as a field name unless you enclose it in square brackets, so your code should be written as

private void btn_Save_Click(object sender, EventArgs e)
{
    try
    {
        string sqlText = "insert into OrderForm([Size]) values (?)";
        using(OleDbConnection connection = new OleDbConnection(.....))
        using(OleDbCommand command = new OleDbCommand(sqlText, connection))
        {
            connection.Open();
            command.Parameters.Add("@p1", OleDbType.VarWChar).Value = sizeBox.Text;
            int rowsAdded = command.ExecuteNonQuery();
            if(rowsAdded > 0) 
                MessageBox.Show("Order Inserted into Database");
            else
                MessageBox.Show("No Order added to the Database");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error " + ex.Message);
    }
}

A part from enclosing the SIZE in square brackets I have changed a couple of things.

First everytime you reach a database you use the using statement around the connection and other disposable object. This avoid memory leaks and expensive resouce utilization when they are not needed.

Second the query is parameterized, in this way it is a lot more clear what you are doing, avoid sql injections and problems with the correct parsing of the values.

Syntax Error in Insert Into Statement C# OleDb

1 Answers1