Using a SubQuery in an Insert Statement in SQL Server 2005

Question

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) {
            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?

Answer 1

Change your SQL to this:

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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 :)