MMAVb.net [2008] - ADO.Net SQLCommand can you use subquery in INSERT?

[rw409168]

Greetings,

Hmm I wasn't sure if to post this here or the SQL server programming board, here goes:-

Using a sqlcommand is it possible to use subqueries in an INSERT statement?

I attempted the following (and numerous variations playing around with syntax):-

Using insertuser As New SqlCommand("INSERT INTO Users (UserID, Name, Password, Phone) " _
+ "VALUES((Select MAX(UserId)+1 as UserId FROM Users), @name, @password, @phone)", conn)

Errors with 1046: Subqueries are not allowed in this contect. Only scalar expressions are allowed.

The scenario is the userID is hidden from the user and does not appear on the form, I want to find the last value (MAX) userId (integer) and increment by 1.

Errorcode 1046 suggests It can't be done with a subquery but was wondering if it can be done but was going the wrong way about it.

Any advice or tips would be appreciated.

Thanks
Rob

 

[gmmastros]

try this:

Using insertuser As New SqlCommand("INSERT INTO Users (UserID, Name, Password, Phone) " _
+ "Select MAX(UserId)+1 as UserId, @name, @password, @phone FROM Users", conn)

I may have messed things up just a little with the quotes and parenthesis, ultimately you want something like...

[tt]
Insert Into Users(UserId, Name, Password, Phone)
Select Max(UserId) + 1 As UserId,
@Name,
@Password,
@Phone
From Users
[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Your query should look like this. Notice the elimination of the VALUES keyword.

INSERT INTO Users (UserID, Name, Password, Phone) Select MAX(UserId)+1 as UserId, @name, @password, @phone FROM Users

However, you should really consider making the UserID column an IDENTITY column. It's a nightmare trying to accurately utilize a Max ID + 1 setup when an IDENTITY column will serve the same purpose.

 

[rw409168]

Thanks for the tips.

I'm going to research now to double check what an IDENTITY column is.

Thanks again, I would have never thought of dropping the VALUES keyword.