How to pass multiple selected item text from DropDownListCheckBoxes to Parameterized Sql

Question

I am able to retain the DropDownListCheckbox multi-selected items text inside a label with a button click. I need to search from the database based on the DropDownListCheckBox multi selected items and its related data from a SQL-Server database.

How to achieve the search option using a button click by passing the input from DDL_CB list items or label text to parameterized SQL query?

My requirement: the search feature must display the data in JQgrid based on the text contained in the Label or DDL_CheckBox multi-selected items.

My C# code:

static string value1;
static string value2;
static string value3;

 protected void createmaincontrols()
    {
        //Create a Dynamic Panel
        DynamicPanel = new Panel();
        DynamicPanel.ID = "DynamicPanel";
        DynamicPanel.Width = 1600;

        //Create Main Table
        var dynamic_filter_table = new WebForms.Table();
        dynamic_filter_table.ID = "dynamic_filter_table_id";
        TableRow campaign_table_row = new TableRow();
        campaign_table_row.ID = "country_table_row";
        TableRow campaign_label_row = new TableRow();
        campaign_label_row.ID = "country_label_row";           
        TableCell campaignnamecell = new TableCell();
        campaignnamecell.ID = "countrynamecell";
        TableCell btncell = new TableCell();
        btncell.ID = "btncell";
        TableCell labelcell = new TableCell();
        labelcell.ID = "labelcell";
        //Create Campaigns DDL
        DropDownCheckBoxes DDL_checkbox = new DropDownCheckBoxes();
        DDL_checkbox.ID = "MainDDL_Countries";
        DDL_checkbox.AutoPostBack = true;
        DDL_checkbox.ForeColor = System.Drawing.Color.MidnightBlue;
        DDL_checkbox.Font.Size = FontUnit.Point(8);
        DDL_checkbox.Font.Bold = true;
        DDL_checkbox.Font.Name = "Arial";
        DDL_checkbox.AddJQueryReference = true;
        DDL_checkbox.UseButtons = true;
        DDL_checkbox.UseSelectAllNode = true;
        DDL_checkbox.Style.SelectBoxWidth = 200;
        DDL_checkbox.Style.DropDownBoxBoxWidth = 200;
        DDL_checkbox.Style.DropDownBoxBoxHeight = 130;
        DDL_checkbox.Texts.SelectBoxCaption = "Select Countries";
        DDL_checkbox.Items.Add(new ListItem("SINGAPORE"));
        DDL_checkbox.Items.Add(new ListItem("UNITED KINGDOM"));
        DDL_checkbox.Items.Add(new ListItem("MALAYSIA"));
        DDL_checkbox.Items.Add(new ListItem("INDIA"));
        DDL_checkbox.Items.Add(new ListItem("FRANCE"));
        DDL_checkbox.Items.Add(new ListItem("GERMANY"));
        DDL_checkbox.Items.Add(new ListItem("NORWAY"));
        DDL_checkbox.DataTextField = "Country Name";
        DDL_checkbox.DataBind();
        DDL_checkbox.AutoPostBack = true;
        DDL_checkbox.EnableViewState = false;
        Button submitbutton = new Button();
        submitbutton.ID = "mybutton";
        submitbutton.Text = "SubmitSelectedCountries";
        submitbutton.Click += new EventHandler(Buttonnew_Click);
        submitbutton.Font.Name = "Arial";
        submitbutton.Font.Bold = true;
        submitbutton.Font.Size = FontUnit.Point(8);
        submitbutton.ForeColor = System.Drawing.Color.MidnightBlue;
        submitbutton.BackColor = System.Drawing.Color.LightGray;
        submitbutton.UseSubmitBehavior = false;
        Label lblCampaignName = new Label();
        lblCampaignName.ID = "Countries";
        lblCampaignName.Font.Bold = true;
        lblCampaignName.Font.Size = FontUnit.Point(8);
        lblCampaignName.ForeColor = System.Drawing.Color.MidnightBlue;
        lblCampaignName.BackColor = System.Drawing.Color.LightGray;
        campaignnamecell.Controls.Add(DDL_checkbox);
        campaignnamecell.Controls.Add(submitbutton);
        campaignnamecell.Controls.Add(lblcountryname);
        campaign_table_row.Controls.Add(countrycell);
        dynamic_filter_table.Controls.Add(country_table_row);
        DynamicPanel.Controls.Add(dynamic_filter_table);
        SelectPanel.Controls.Add(DynamicPanel);

    }

C# code to retrieve the dropdown checked items in a label using a button click

 protected void Buttonnew_Click(object sender, EventArgs e)
    {
        Table maintable = Select.FindControl("dynamic_filter_table_id") as Table;
        DropDownCheckBoxes DDL_checkbox = maintable.FindControl("MainDDL_Contries") as DropDownCheckBoxes;
        Label lblcountryname = maintable.FindControl("Country") as Label;
        List<String> Country_List = new List<string>();
        foreach (System.Web.UI.WebControls.ListItem item in DDL_checkbox.Items)
        {
            if (item.Selected)
            {
                Country_List.Add(item.Text);
            }
            lblcountryname .Text = String.Join(",", Country_List.ToArray());

        }           
    }

How to search the country details based on a parameterized SQL query input from label or dropdowncheckbox selected items?

protected void Button4_Click(object sender, EventArgs e)
    {
       Table maintable = Select.FindControl("dynamic_filter_table_id") as Table;
        int rc = maintable.Rows.Count;
        if (rc == 2)
             {
                    //Three country selected in DDL_checkbox
                    DropDownCheckBoxes d4 = maintable.FindControl("MainDDL_Countries") as DropDownCheckBoxes;
                    Label lblcountryname = maintable.FindControl("Countries") as Label;
                    var countryname= test.ToString().Split(new[] { ',', '\n' }).ToArray();
                    if(countryname.Count() >=1 && countryname.Count() <=3)
                   {
                    value1 = countryname.ElementAt(0).ToString();
                    value2 = countryname.ElementAt(1).ToString();
                    value3 = countryname.ElementAt(2).ToString();
                   }
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "SELECT C.Country_Name,C.Address FROM COUNTRYTABLE as C WHERE C.Country_Name in(@t4,@t5,@t6)";                        
                    cmd.Parameters.Add("@t4", SqlDbType.VarChar).Value = value1;
                    cmd.Parameters.Add("@t5", SqlDbType.VarChar).Value = value2;
                    cmd.Parameters.Add("@t6", SqlDbType.VarChar).Value = value3;

                    con.Open();
                    cmd.ExecuteNonQuery();
                    SqlDataAdapter sql = new SqlDataAdapter(cmd);
                    DataSet data = new DataSet();
                    sql.Fill(data);
                    con.Close();
                    Session["DataforSearch_DDL"] = data.Tables[0];
             }
     }

score 0 · Answer 1 · answered Jul 02 '14 at 16:41

0

This is admittedly a partial answer. It will get you started.

When you submit a form with multi-selected items, the selected items are passed as comma separated values. Something like this:

value1,value2,etc

You want your query to have a where clause like this:

where someTextfield in ('value1','value2','etc')

or without the quotes for numeric fields. However, you wisely said that you wanted to use parameters.

Here endeth the partial answer.

answered Jul 02 '14 at 16:41

Dan Bracuk

20,699
4
26
43

i am not getting any exceptions with the above scenarios as per your suggestion but my gridview is not getting displayed. am i going wrong in passing the values to the scalar variables of Sql parameters ? – Shrivatsan Jul 03 '14 at 07:44

score 0 · Answer 2 · answered Jul 02 '14 at 16:46

long-long time ago, I used to do it this way:

for (int i = 0; i < param.Length; i++)
    if (param[i] != "" && param[i] != null) 
        s_comm.Parameters.AddWithValue(tParam + i.ToString(), param[i]);

where:

private string tParam = "@Param";
string[] paramName // Name of the parameters
string[] param // Values for those parameters.

and I was building a statement like this:

string where = "";

if (paramName != null)
   for (int i = 0; i < paramName.Length; i++)
       if (paramName[i] != "" && paramName[i] != null)
          if (i == 0)
             where = " WHERE " + paramName[i] + " = " + tParam + i.ToString();
          else
             where += ", " + paramName[i] + " = " + tParam + i.ToString();
       else throw new Exception(noColumnName + " at position #:" + i.ToString());
else where = "";

if (table != "") return "SELECT * FROM " + table + where;

I am sure there are more elegant solutions, but this is a start, right?

How to pass multiple selected item text from DropDownListCheckBoxes to Parameterized Sql

2 Answers2