Parameter Placeholders are causing an error in Webmatrix

Question

Every time I try using parameter placeholders either in a SELECT, INSERT, or UPDATE statement in WebMatrix I get the same error. It works fine in WHERE clause. What am I doing wrong I need some help.

Here is my code:

  if (IsPost && Validation.IsValid())
        {  
           Admission1 = Request["AdmDate1"];
           Discharge1 = Request["DisDate1"];
           LOC1 = Request["LOC1"];
           Program1 = Request["ProgramName1"];
           Notes = Request["Notes"];           
           var SQLUPDATE = "UPDATE SSI_Screening_New SET AdmissionDate1=@49, DischargeDate1=@50,  LOC1=@51, ProgramName1=@52, Notes1=@53 WHERE (ID) = (@0)";  
           db.Execute(SQLUPDATE, Admission1, Discharge1, LOC1, Program1, Notes, SSId);

           if (ModelState.IsValid) 
           {     
              Response.Redirect("~/ThankYou");
           }             
        }

Is there an alternative for using parameter placeholders in Webmatrix?

Here is the error I get every time:

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@49".

Thanks in advance for your help!

Knox · Accepted Answer · 2016-02-09T14:43:13.037

1

You are on the right track, but you must start your parameter placeholders at @0, then @1, @2, @3... and so forth. You can't jump to @49, which is why it's saying you must declare that variable. Try using @0 thru @5 and it will work.

           var SQLUPDATE = "UPDATE SSI_Screening_New SET AdmissionDate1=@0, DischargeDate1=@1,  LOC1=@2, ProgramName1=@3, Notes1=@4 WHERE (ID) = (@5)";

edited Feb 09 '16 at 14:43

answered Feb 09 '16 at 00:18

Knox

2,909
11
37
65

Is there another way because I don't want to other fields? – tnlewis Feb 09 '16 at 12:48
No, there is no other way. When doing a parameterized query of any sort, you must start at @0, @1, and so on. There is no advantage to doing anything else. Each database query or execute gets its own list of parameters, so it's not like you can or should reuse them in any kind of way. I edited my answer to show exactly the right way. – Knox Feb 09 '16 at 14:46
Does that mean that I have to create parameter for every field in the record and I don't want to update every field? I only have a couple of fields I want to update. – tnlewis Feb 09 '16 at 16:02
The @ convention is just numbering all the parameters you are passing into the db.query or db.execute. So if you are only updating say 3 fields, you will only have @0, @1, @2 and maybe a @3 to indicate the SSid. The @ number is completely unrelated to the number of the fields in the record. – Knox Feb 10 '16 at 00:25
Thanks. I'm going to try it again. – tnlewis Feb 10 '16 at 11:48

Parameter Placeholders are causing an error in Webmatrix

1 Answers1