INSERT statement using a subquery

I am trying to write an INSERT statement that uses a subquery as one of the values in the VALUES list, but am recieving the following error:
---------------------------------------------------------------------------------------------------
Msg 1046, Level 15, State 1, Procedure undersound_Membership_CreateBand, Line 30
Subqueries are not allowed in this context. Only scalar expressions are allowed.
---------------------------------------------------------------------------------------------------
I believe I am recieving the error because the query could possibly return multiple values even though I know it will only return one.

Does anyone have an idea of how to do this a different way?

(let me know if I haven't clearified this issue enough)

Thanks,
Jon


CODE------------------------------------------------------------------------------------------------------




INSERT INTO undersound_Bands
(
UserId,
BandName,
BandCity,
BandState,
BandDescription
)



VALUES
(
(SELECT M.UserId FROM aspnet_Membership AS M inner join aspnet_Users AS U on M.UserId = U.UserId WHERE U.UserName = @UserName),
@BandName,
@BandCity,
@BandState,
@BandDescription
)</BLOCKQUOTE>

 

If your subquery is returning multiple values you could do this...

SELECT TOP 1M.UserId FROM aspnet_Membership AS M inner join aspnet_Users AS U on M.UserId = U.UserId WHERE U.UserName = @UserName


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

It's ok to return multiple values (You'll just insert multiple rows) I always do inserts like this:

INSERT INTO undersound_Bands
(
UserId,
BandName,
BandCity,
BandState,
BandDescription
)
SELECT M.UserId
@BandName,
@BandCity,
@BandState,
@BandDescription
FROM aspnet_Membership AS M
inner join aspnet_Users AS U on M.UserId = U.UserId
WHERE U.UserName = @UserName