Using a SubQuery in an Insert Statement in SQL Server 2005

I have a carsale project. It completely works on localhost. I have a "AddCar.aspx" page that inserts a car record with car's features. Car features are selected with checkboxes. If i don't check any checkbox, there is no problem. But if i check one of feature checkboxes, my page gives an error like this:

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

And my code is like that:

foreach (DataListItem item in Security1.Items) {
        CheckBox CheckBox1 = (CheckBox)item.FindControl("CheckBox1");

        if (CheckBox1.Checked) {
        开发者_StackOverflow    HiddenField h = (HiddenField)item.FindControl("FeaID");
            string add = "Insert into Carfeature (RecID,FeatureID) values ((select Max(RecID) from record),@FeatureID)";
            cmd[k] = new SqlCommand();
            cmd[k].CommandType = CommandType.Text;
            cmd[k].Parameters.Add("@FeatureID", SqlDbType.Int).Value = h.Value;
            cmd[k].CommandText = add;
            k++;
        }
}

Is there any solution?

Two things, first of all, try this SQL:

Insert into Carfeature (RecID,FeatureID) 
select Max(RecID), @FeatureID from record;

Secondly, the Max(RecId) is problematic if you have multiple threads doing this. Are you aware that you can get the last inserted identity? Isn't that what you want to do here? If you've just inserted a record into the record table in the previous step

select SCOPE_IDENTITY() as RecID;

will give you the correct RecID in a thread safe manner.

Change your SQL to this:

Insert into Carfeature (RecID,FeatureID)
select Max(RecID), @FeatureID from record

I think you could just re-format your sql and do it this way:

Insert into Carfeature (RecID,FeatureID) select Max(RecID), @FeatureId from record

This is most likely due to concurrency control. A way I'd recommend doing this on SQL Server 2005 is to change your sql statement to the following using CTE's(http://msdn.microsoft.com/en-us/library/ms190766.aspx):

with MaxId as
(
  select Max(RecID)
  from record
)
insert into Carfeature (RecID,FeatureID)
select @MaxID, @FeatureID
from MaxId

Friends, you all answered true, thanks a lot. I changed my code like that and it worked:

Insert into Carfeature (RecID,FeatureID) select Max(RecID), @FeatureID from record

But I don't know how to set accepted answer, because all of answers are true :)