Error : Must declare the scalar variable

Luke Baker

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]

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard14y ago
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 < strQualified.Length; 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 "that"and 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 Tan14y ago
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 Baker14y ago
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(ds,strTable);
            dgv.DataSource = ds;
            dgv.DataMember = strTable;
            connection.Close();

        }
[/code]
Bryian Tan14y ago
hi,

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