Insert SQL Values Into a Database Table

Question

I am getting the following error

syntax not correct near item number

but I don't see anything wrong, the values being inserted are from a dataset containing field names in variables from another sql query that is being looped through and then inserted into another table like so....

string strOrderDetails = 
    "INSERT INTO Orders (Order Number, Item Number, Description, Price) " +
    "VALUES ('" + strOrderNo.Replace("'", "''").ToString() + "', '"
             + intItemNo + "', '"
             + strDesc.Replace("'", "''").ToString() + "', '"
             + decPrice + "')";

On execution of the above is where the code falls over and states there's an error near the word item number?

Do I need to do something to the intItemNo as it's an integer?

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Jul 15 '13 at 22:04
you can try with square brackets columns that have spaces like ([Order Number], [Item Number],....) — Gökhan Girgin, Jul 15 '13 at 22:05
Please please please do what Steve's answer denotes below. Parameterized queries are always a better idea. He also ensures proper disposal of your `SqlCommand` and `SqlConnection` objects with the `using` keyword. — Evan L, Jul 15 '13 at 22:45

score 4 · Answer 1 · edited May 23 '17 at 12:21

When a column contains spaces you need to enclose it in square brackets or other delimiter for the choosen database

But said that, please do not use string concatenation to build sql commands, but always a parameterized query.

string strOrderDetails = "INSERT INTO Orders ([Order Number], [Item Number]," + 
                         "Description, Price) VALUES (@ordNum, @temNo, @desc, @price";
using(SqlConnection cn = new SqlConnection(conString))
using(SqlCommand cmd = new SqlCommand(strOrderDetails, cn))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@ordNum",strOrderNo);
    cmd.Parameters.AddWithValue("@itemNo",intItemNo);
    cmd.Parameters.AddWithValue("@desc",strDesc);
    cmd.Parameters.AddWithValue("@price", decPrice);
    cmd.ExecuteNonQuery();
}

As you could notice, using parameters remove the need to write code to handle quotes in the input values, but also remove the possibility of Sql Injection attacks

Insert SQL Values Into a Database Table

1 Answers1