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?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Jul 15, 2013 at 22:04
you can try with square brackets columns that have spaces like ([Order Number], [Item Number],....) — Gökhan Girgin
– Gökhan Girgin, Commented Jul 15, 2013 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
– Evan L, Commented Jul 15, 2013 at 22:45

Community · Accepted Answer · 2017-05-23 12:21:04Z

4

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

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jul 15, 2013 at 22:05

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert SQL Values Into a Database Table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related