Why is my C# loop in ASP.NET MVC 5 ActionResult throwing this error message

Question

My code:

int total = Convert.ToInt32(form["REC-tr-total"]); 
int value = 0;
string insValOutput = ""; 

if (total > 0)
{
  for (int i = 1; i <= total; i++) 
  {
    value = Convert.ToInt32(form["inventoryID[" + i + "]"]); 
    insValOutput = $"(" + vCAS_capture_payments.Id + ", value{i + 1}),"; 
  }

db.Database.ExecuteSqlCommand(String.Format(@"INSERT INTO dbo.VCAS_capture_payments__REF_items (FK_capture_paymentsId, FK_inventoryId)                 VALUES {0};", insValOutput));             
}

The error message:

This seems like a pretty straight-forward loop, but error keeps coming up... Experts please advise.

UPDATE: for those asking, I've updated this line as follows:

insValOutput = String.Format(@"({0},{1}),", vCAS_capture_payments.Id, value);  // (1, value1),(2, value2) ...

But I get the same results.

UPDATE No.2:

Thanks for all the comments, really appreciate it. I know the code is not the best, but I have been fighting with how best to implement multi-insert via LinQ and Entity Framework in my current codebase.

However from the answer provided, I was able to amend the code as follows, which works.

The culprit was the SQL statement and the concatenation.

int total = Convert.ToInt32(form["REC-tr-total"]); // -- Total Receipt item rows
int value = 0;
string insValOutput = ""; // -- Insert VALUES variable

if (total > 0)
{
    for (int i = 1; i <= total; i++)
    {
        value = Convert.ToInt32(form["inventoryID["+i+"]"]); // -- Fetch the inventory ID for index i   
        insValOutput += String.Format(@"({0},{1}),", vCAS_capture_payments.Id, value);  // (1, value1),(2, value2) ...
    }

    db.Database.ExecuteSqlCommand(String.Format(@"
                    INSERT INTO dbo.VCAS_capture_payments__REF_items (FK_capture_paymentsId, FK_inventoryId)
                    VALUES {0};", insValOutput.TrimEnd(',', ' ')));
}

Answer 1

It looks as though you are trying to build the list of values passed to your insert statement. You want the result to be Values (itemA1, itemA2), (itemB2, itemB2), ... If this is incorrect the rest of the answer might not be relevant.

The error is in the line where you are structuring this collection:

insValOutput = $"(" + vCAS_capture_payments.Id + ", value{i + 1}),";

There are a few problems in your conditional where you are building the collection of values...

The first problem is that you will only ever have 1 (itema, itemb) collection because you do not append to your insValOutput variable. You're actually overwriting it with each execution of your loop.

The second is once you clear the error for '{' you'll get an error for a comma because you add a comma to the end of the text, and your last element needs to omit the comma.

The third, is the specific error you are receiving right now.

insValOutput = $"(" + vCAS_capture_payments.Id + ", value{i + 1}),";

Currently, you're actually putting the string "value{i + 1}" into your insValOutput, and the error is stating that the curly bracket is incorrect syntax.

I think what you were looking for is this:

insValOutput += $"({vCAS_capture_payments.Id}, {value}),"

But even with that corrected, you'll still have the issues stated above. Also, it looks hideous, find a way without mixing the string interpolation and the "+" for concatenation.

You should revisit how you are building this Values collection, then you should capture the entire statement you are passing so you can both proof read it, and then execute it in an SMS session to check for errors.

As others have pointed out, this is also not a great way to build queries in your application. You should definitely look into restructuring your code and how it builds queries.