SqlCommand error

Question

At the moment I'm trying to populate my SQL Server database with 3 values I'm hard coding in through a C# program.

My database has 4 columns which are as follows:

RowID (this should get updated automatically by my database)
Name (stored as a string)
Score (int)
Accuracy (float)

In my code I'm trying to fill these out with the following lines of code:

 using (SqlConnection connection = new SqlConnection(DBConnection))
 {
            string name = "John";
            int score = 123;
            float Accuracy = 20.0f;

            SqlCommand command = new SqlCommand("INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES("  + name + " , " + score + " , " + Accuracy + ")", connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

But when I run my program, Visual Studio highlights

command.ExecuteNonQuery()

stating ' John' is not a valid column.

Is there something I'm doing wrong with this?

One thing you should have read already had you taken 10 seconds to search StackOverflow for any related questions is: **USE PARAMETERIZED QUERIES, FOR SANITY'S SAKE!!!** — Thorsten Dittmar, Apr 10 '13 at 09:22

Thorsten Dittmar · Answer 1 · 2013-04-10T09:30:10.493

The (in my opinion only) correct way to do this is:

using (SqlConnection connection = new SqlConnection(DBConnection))
{
    string name = "John";
    int score = 123;
    float Accuracy = 20.0f;

    using (SqlCommand command = new SqlCommand("INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES(@name, @score, @accuracy)", connection);
    {
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@score", score);
        command.Parameters.AddWithValue("@accuracy", accuracy);

        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

Please note that you do not need to mind the single quotes when using parameterized queries!! The string parameters are not enclosed in single quotes - it's all done for you by the framework.

score 1 · Answer 2 · edited May 23 '17 at 11:43

1

Plenty wrong here.

If you wanted to insert like this you need to add " ' " around your values.
You need to have commas between values.
This is ripe for SQL injection
Bad for performance as each query is different.

You actually want to be adding parameters (AddParameterWithValue)

See here:

SQL injection on INSERT

edited May 23 '17 at 11:43

Community

1
1

answered Apr 10 '13 at 09:23

LoztInSpace

5,584
1
15
27

1) is not quite correct. You need to include the values in single quotes if the value is a string. Numbers can be used without single quotes. – Thorsten Dittmar Apr 10 '13 at 09:25
Yes agreed. The main issue here is not using parameters though, so a moot point. – LoztInSpace Apr 10 '13 at 09:28

score 1 · Answer 3 · answered Apr 10 '13 at 09:24

Use parametrized queries for this kind of issues:

using (SqlConnection connection = new SqlConnection(DBConnection))
        {
            string name = "John";
            int score = 123;
            float Accuracy = 20.0f;

            SqlCommand command = new SqlCommand("INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES(@name,@score,@accuracy)", connection);

               SqlParameter name= new SqlParameter("@name", name);
                name.Value = name;
                command.Parameters.Add(name);
               .
               . 
               .
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

In this way you can add parameters so that confusion of columns can be avoided.

The way you write it it's pretty redundant. You can use `AddWithValue`. Also, the `SqlParameter` constructor you're using already sets the parameter's value, so the line `name.Value = ...` is not needed. — Thorsten Dittmar, Apr 10 '13 at 09:28
@ThorstenDittmar yeah, recently i started writing parametrized queries, before that i use to write queries same as poster has written. I saw your answer and it was quit educational for me. Thanx. — Freelancer, Apr 10 '13 at 09:31

score 0 · Answer 4 · answered Apr 10 '13 at 09:20

0

Since John is a string, you have to let your database know that by added ' on either side of John.

So change ...VALUES(" + name + " , "... to ...VALUES('" + name + "' , "...

If you just write John without ' on either side, then the database will think you are referrign to a column, and as your error message shows: the database does not know anything about a column called John.

answered Apr 10 '13 at 09:20

Øyvind Bråthen

59,338
27
124
151

@ThorstenDittmar - I just pointed out what was wrong with his code (from the SQL servers point of view). I never encouraged sending concatenated strings to the database, but I guess I could have pointed it out in my answer as well ;) – Øyvind Bråthen Apr 10 '13 at 09:28

Pranay Rana · Answer 5 · 2013-04-10T09:26:11.717

just do thisto work it properly

SqlCommand command = new SqlCommand("INSERT INTO 
      HighScoreTable(Name, Score, Accuracy) 
               VALUES('"  + name + "' , " + score + " , " + Accuracy + ")", connection);

as name is string value just decorate it with ' like this '" + name + "' because in sql server if the string is not decorates with ' it will give you an error

right now string value get convert to following sql query shich is not valid

      INSERT INTO 
      HighScoreTable(Name, Score, Accuracy) 
               VALUES(John , 123 , 20.0f)

To avoid this mistake make use of SQLParameter i.e. prameterize query

score 0 · Answer 6 · edited May 23 '17 at 12:21

0

I suspect you haven't escaped the text.

SqlCommand command = new SqlCommand("INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES("'" + name + "' , " + score + " , " + Accuracy + ")"

A better way would be to use proper command parameters:

When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?

edited May 23 '17 at 12:21

Community

1
1

answered Apr 10 '13 at 09:22

RidingTheRails

1,105
1
12
22

walther · Answer 7 · 2013-04-10T09:34:40.970

Firstly, I'd strongly suggest you a different approach - parametrized queries, asap. It will prevent SQL injections and make your life easier, because you won't have to think about formatting your SQL query everytime.. It can get really tedious after a while to wonder where all the quotes should go and it's a pain in the ass during a maintenance to modify such code...

Secondly, what your SQL translates to looks like this:

SqlCommand command = new SqlCommand("INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES(John , 10 , ..)", connection);

SQL thinks your values are actually column names! You need to wrap your values in single quotes

INSERT INTO HighScoreTable(Name, Score, Accuracy) VALUES('John' , 10 , '..')

But `Score` is an `INT` - so the value you provide for score really shouldn't be in quotes...... — marc_s, Apr 10 '13 at 09:31

SqlCommand error

7 Answers7