Invalid column name SQL

Question

I have wasted hours trying to find what's incorrect in my code. Before adding a new column in the table, it was ok but after that, whenever I submit my form, it gives me an error

The column name is not valid. [ Node name (if any) = ,Column name = userID ]

Here is my code:

con.Open();  
string query = "INSERT INTO PlayerTable 
(username ,password, picture, scoreL1, scoreL2, scoreL3, userID) 
VALUES
('" + @userName + "','" + @password + "',@picture," + @score1 
+ "," + @score2 + "," + @score3 + "," + @num + ")";

                cmd.Parameters.AddWithValue("@picture", a);
                cmd.Parameters.AddWithValue("@username", userName);
                cmd.Parameters.AddWithValue("@password", password);
                cmd.Parameters.AddWithValue("@scoreL1", score1);
                cmd.Parameters.AddWithValue("@scoreL2", score2);
                cmd.Parameters.AddWithValue("@scoreL3", score3);
                cmd.Parameters.AddWithValue("@userID", num);
                cmd = new SqlCeCommand(query, con);
                cmd.ExecuteNonQuery();

Sani Huttunen · Accepted Answer · 2014-05-12 11:49:54Z

3

Shouldn't it be:

string query =
  "INSERT INTO PlayerTable
   (username, password, picture, scoreL1, scoreL2, scoreL3, userID)
   VALUES(@userName, @password, @picture, @scoreL1, @scoreL2, @scoreL3, @userID)";

That is not @score1 but @scoreL1 and same for the others.

Edit

When you instantiate a new SqlCeCommand:

cmd = new SqlCeCommand(query, con);

you essentially erase the paramaters you set earlier.
Move the instantiation above the parameter assignments:

cmd = new SqlCeCommand(query, con);
cmd.Parameters.AddWithValue("@picture", a);
cmd.Parameters.AddWithValue("@username", userName);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@scoreL1", score1);
cmd.Parameters.AddWithValue("@scoreL2", score2);
cmd.Parameters.AddWithValue("@scoreL3", score3);
cmd.Parameters.AddWithValue("@userID", num);
cmd.ExecuteNonQuery();

edited May 12, 2014 at 11:49

answered May 11, 2014 at 3:22

Sani Huttunen

24.4k7 gold badges78 silver badges82 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

+1 . . . The other important thing is not to enclose the variable names in single quotes.

Sani Huttunen Over a year ago

@user3624655: Do you have a column userID in the database table?

Sani Huttunen Over a year ago

Can you show the layout/design/script of the table?

Erik Philips · Accepted Answer · 2014-05-11 03:25:26Z

0

You should not be surrounding your query parameters with quotes:

string query = "INSERT INTO PlayerTable 
 (username ,password, picture, scoreL1, scoreL2, scoreL3, userID) 
 VALUES(@userName, @password, @picture, @scoreL1, @scoreL2, @scoreL3, @userID )";

Additionally, you need to make sure your Parameter Names match the @ values.

answered May 11, 2014 at 3:25

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

Collectives™ on Stack Overflow

Invalid column name SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related