asp.net, C# passing list i in the query

Question

i have the code of passing a selected value in the the query and that value will bind the data in the girdview. the selection is made with the Listbox.. I want to pass a list param of S_ID

Now I want to modify my program a little i want to pass a list of param. in the query.. so that this can enable multiple selection list in the listbox.. I've searched over Internet and i couldnt find a certain satisfying answer... I'm not very good at query.

 protected void Button2_Click(object sender, EventArgs e)
{
    OleDbConnection con = new OleDbConnection();
    con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database11.accdb";
    con.Open();
    for (int i = 0; i < ListBox1.Items.Count; i++)
    //foreach(Items Item in Listbox1)
  {
        if (ListBox1.Items[i].Selected == true)
        {
            string Skill_ID = ListBox1.Items[i].Value;
            string query1 = "SELECT *FROM Emp AS E, Junction AS J WHERE E.E_ID=J.E_ID And J.S_ID=@Skill_ID";
            OleDbCommand cmd1 = new OleDbCommand(query1, con);
            cmd1.Parameters.AddWithValue("@Skill_ID", Skill_ID);

            OleDbDataReader rs = cmd1.ExecuteReader();
            if (rs.HasRows)
            {
                GridView1.DataSource = rs;
                GridView1.DataBind();
                rs.Close();
            }

Matthew Cox · Accepted Answer · 2013-11-09 08:33:03Z

2

//use LINQ to obtain the selected id values as strings
var selectedItems = ListBox1.Items.Cast<ListItem>().Where(p => p.Selected == true).Select(p => p.Value).ToList();

//now concat them as a comma seperated string
var idCommaList = string.Join(', ', selectedItems);

//Now use a WHERE IN () statement instead with your query and concat your comma seperated list of ids into the sql statement
var query1 = @"SELECT * 
              FROM Emp AS E, Junction AS J 
              WHERE E.E_ID = J.E_ID AND J.S_ID IN (" + idCommaList + ")";

edited Nov 9, 2013 at 8:33

answered Nov 9, 2013 at 8:25

Matthew Cox

13.7k9 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matthew Cox Over a year ago

Ya i just doubled checked my worked. The linq component is incorrect. Edit coming (I forgot it doesnt implement the necessary interfaces).

Matthew Cox Over a year ago

Just needed to cast it to perform LINQ and this will work. See my edit

Matthew Cox Over a year ago

Can you explain what you mean by that?

Gab Over a year ago

IN STRING Join(". ", selectedItems);

Gab Over a year ago

actually in the program the separating operator is shown in ""

Collectives™ on Stack Overflow

asp.net, C# passing list i in the query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related