Error : Must declare the scalar variable

Question

Right im a junior programmer and im trying to build a system which shows data from a database. I have about 18 check boxes will all different types of data. Depending on what is ticked will depend on what is displayed in the data grid. Im having to use sql parameters in my SQL statement. Below is the code im using in my function which is called in each of the checkboxes checked changed methods aswell. The current error is Must Declare the scalar variable Filter0 Below is the function -
strQualified is an array of strings and paramArray is an array of sqlParameter
much help is needed :-)
[code]

private void chkBoxFilter()         {                         if (chkBlood.Checked)                 strQualified[0] = "BloodTransfusion1";             else                 strQualified[0] = "";             if (chBlood2.Checked)                 strQualified[1] = "BloodTransfusion2";             else                 strQualified[1] = "";             if (chkDementia.Checked)                 strQualified[2] = "Dementia";             else                 strQualified[2] = "";             if (chkEpidural.Checked)                 strQualified[3] = "Epidural";             else                 strQualified[3] = "";             if (chkEqualityDiversity.Checked)                 strQualified[4] = "EqualityDiversity";             else                 strQualified[4] = "";             if (chkInfectionControl.Checked)                 strQualified[5] = "InfectionControl";             else                 strQualified[5] = "";             if (chkInformationGovernance.Checked)                 strQualified[6] = "InformationGovern";             else                 strQualified[6] = "";             if (chkInsulin.Checked)                 strQualified[7] = "Insulin";             else                 strQualified[7] = "";             if (chkIV.Checked)                 strQualified[8] = "IV";             else                 strQualified[8] = "";             if (chkMandatoryA.Checked)                 strQualified[9] = "MandatoryA";             else                 strQualified[9] = "";             if (chkMandatoryB.Checked)                 strQualified[10] = "MandatoryB";             else                 strQualified[10] = "";             if (chkMandatoryC.Checked)                 strQualified[11] = "MandatoryC";             else                 strQualified[11] = "";             if (chkManualHandling.Checked)                 strQualified[12] = "ManualHandling";             else                 strQualified[12] = "";             if (chkMedicine.Checked)                 strQualified[13] = "MedicineManagement";             else                 strQualified[13] = "";             if (chkMentorsUpdate.Checked)                 strQualified[14] = "MentorsUpdate";             else                 strQualified[14] = "";             if (chkPDR.Checked)                 strQualified[15] = "PDR";             else                 strQualified[15] = "";             if (chkPIN.Checked)                 strQualified[16] = "PIN";             else                 strQualified[16] = "";             if (chkSafeGuarding.Checked)                 strQualified[17] = "SafeGuardingAdults";             else                 strQualified[17] = "";             for(int i = 0; i < strQualified.Length;i++)     {         paramArray[i] = new SqlParameter();         paramArray[i].ParameterName = "@Filter" + i.ToString();         paramArray[i].Value = strQualified[i];              }             command = @"Select * from view_TrainingHist where training_Type = @Filter0 OR  training_Type = @Filter1 OR training_Type = @Filter2 OR training_Type = @Filter3 OR  training_Type = @Filter4 OR training_Type = @Filter5 OR training_Type = @Filter6 OR  training_Type = @Filter7 OR training_Type = @Filter8 OR training_Type = @Filter9 OR  training_Type = @Filter10 OR training_Type = @Filter11 OR training_Type = @Filter12 OR  training_Type = @Filter13 OR training_Type = @Filter14 OR training_Type = @Filter15 OR  training_Type = @Filter16 OR training_Type = @Filter17";             dc.GainConnection(command, dgv1, "view_TrainingHist");                 }

[/code]

theLizard · Answer

I can't test this because I do not have your database but I would not do what you are doing. If check boxes are unchecked you set the corresponding array value to nothing and parsing these empty values in your sql which can be totally ignored. Build your sql based on the check boxes that are checked and ignore those that are unchecked. First thing you may want to consider here is where you set the array value for each checkbox. If it were me I would assign or clear array values in the check box Click event like this strQualified[0] = (checkBox1->Checked == true? BloodTransfusion1 : ; Doing this you do not need to check if the check box is checked or not it is not relevant. In your for loop you can do a test that will include or exclude parameters. eg int paramCount = -1; for (int i = 0; i { //add to string only if array value is not null or empty. if(!string.IsNullOrEmpty(strQualified[i]) { paramCount ++; paramArray[ paramCount ] = new SqlParameter(); paramArray[ paramCount ].ParameterName = @Filter + i.ToString(); paramArray[ paramCount ].Value = strQualified[i]; } } This way you will end up with an sql statement that reflects just those value you are looking for the way that it currently is you are saying if this is thatand this is return everything in either case your filter would not be working the way you want it to. so instead of what you have (which may contain null or empty values) @Select * from view_TrainingHist where training_Type = @Filter0 OR training_Type = @Filter1 OR training_Type = @Filter2 OR training_Type = @Filter3 OR training_Type = @Filter4 OR training_Type = @Filter5 OR training_Type = @Filter6 OR training_Type = @Filter7 OR training_Type = @Filter8 OR training_Type = @Filter9 OR training_Type = @Filter10 OR training_Type = @Filter11 OR training_Type = @Filter12 OR training_Type = @Filter13 OR training_Type = @Filter14 OR training_Type = @Filter15 OR training_Type = @Filter16 OR training_Type = @Filter17; You may end up having this string with no null or empty values. @Select * from view_TrainingHist where training_Type = @Filter0 OR training_Type = @Filter1 OR training_Type = @Filter6 OR training_Type = @Filter9 OR training_Type = @Filter10 OR training_Type = @Filter13 OR training_Type = @Filter16; See the difference...

Bryian Tan · Answer

hello I see that you created the SqlParameters in the first post but never add it into the SqlCommand (I think com1 in your case). Please refer to these link http://asp-net-example.blogspot.com/2008/10/sqlparameter-example-how-to-use.html http://www.codeproject.com/Articles/8180/Using-SQLParameters-with-VB-NET-C

Luke Baker · Answer

Im not using a SQL stored procedure as such. The way i am displaying data in my data grid - [code] public void GainConnection(string command DataGridView dgv string strTable) { string conStr; conStr = Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NTO.mdf;Integrated Security=True;User Instance=True; SqlConnection connection = new SqlConnection(conStr); connection.Open(); com1.Connection = connection; com1.CommandType = CommandType.Text; com1.CommandText = command; DataSet ds = new DataSet(); SqlDataAdapter sqlAdp = new SqlDataAdapter(com1); sqlAdp.Fill(dsstrTable); dgv.DataSource = ds; dgv.DataMember = strTable; connection.Close(); } [/code]

Bryian Tan · Answer

hi Did you check if @Filter0 exists in the Stored Procedure?