Problem with a sql statement

Question

string insertCommand = 
    "INSERT INTO Users (UserID) VALUES" + "" + "(CONVERT(uniqueidentifier, '" + 
    UsersIdentityToinsert + "'),'"+ userName+",0,null')";

it tells me:

There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

But i have four columns?

Beno · Accepted Answer · 2011-06-06 06:11:18Z

2

you only have UserID in the insert list but in the value list, you have two values - a uniqueidentifier and a username

this would work better: (assuming the name of your username column)

string insertCommand = 
    "INSERT INTO Users (UserID, username) VALUES" + "" + 
    "(CONVERT(uniqueidentifier, '" + UsersIdentityToinsert + "'),'"+ userName+"')";

EDIT:

I feel i need to address this part a bit more clearly:

But i have four columns?

The error you received refers to the number of columns specified in the INSERT statement - not the actual table so if you start an INSERT with:

INSERT INTO AnyTable (ColumnA, ColumnB)

you need to match the number of columns here in your VALUES clause

VALUES ("Value1", "Value2")

edited Jun 6, 2011 at 6:11

answered Jun 6, 2011 at 6:02

Beno

4,7631 gold badge29 silver badges26 bronze badges

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

5 Comments

nathan gonzalez Over a year ago

it would work even better if we were using parameters so we could guard against sql injection and ugly code. :)

WithFlyingColors Over a year ago

hmmm. are u saying that i need to Declare @Username , UniqueIdentifier..and then SET them tothe values....would that prevent the sql attack?

Beno Over a year ago

the sql injection is a valid point but more than just declare them - you should validate any values that you get as well

WithFlyingColors Over a year ago

WOW, it seems to require more than one sql statement , huh

Beno Over a year ago

potentially, yes. but imagine if someone were to sign up with the username: usr');drop database aaa;. you need to make sure that no one can do that. (please note, i don't really know if that would work but it serves as an extreme example)

Akram Shahda · Accepted Answer · 2011-06-06 06:04:52Z

0

You are missing "UserName" in column Names, that is, Column Name and values mismathch.

string insertCommand = 
    "INSERT INTO Users (UserID,UserName) VALUES" + "" + 
    "(CONVERT(uniqueidentifier, '" + UsersIdentityToinsert + "'),'"+ userName+"')";

edited Jun 6, 2011 at 6:04

Akram Shahda

14.7k5 gold badges46 silver badges67 bronze badges

answered Jun 6, 2011 at 6:03

Navaneethan

2,2656 gold badges24 silver badges32 bronze badges

Collectives™ on Stack Overflow

Problem with a sql statement

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related