Populate listbox with an sql query

[hemisphere]

have two listboxes. first one of these is multiselect. what i want is after i choose one or more item from the first listbox the other listbox should be populated by an sql query.
what i mean is i will use the first listbox's items in sql and populate the second listbox with the results.

But the problem is i can't create a table adapter because the user can select more than one item in the first listbox.

program is a windows form application.
if anyone answer my question i will be very happy

[VIeditorlover]

Give up adapters, execute query and refill listbox on your own - or you are so lazy you can not write few lines of code?

[Diamonddrake]

I don't understand where your problem is. are you having trouble getting data from a database into a listbox? because that can be done via an adapter and datatable. or not, depending on how you want to go about it.

but what's this multiselect to build a query? what does that have to do with anything, do you mean you take data from the listbox A, concatenate it into a string and query a database? if so. that has nothing to do with how you get the data to the second list box.

string querySQL = "";//this is where you pass in you query sting
//just an example connections string
           string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="access.mdb";
//create and open our connections
OleDbConnection database database = new OleDbConnection(connectionString);
                database.Open();

//create a command, and datatable, and adapter
            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            //---------------------------------
            SQLQuery.CommandText = querySQL;
            SQLQuery.Connection = database;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
           //you now have a full data table
           // now just loop through the data and add the rows to
           //the list box this just looks through the table for each row,
          //and if the row has an entry in the column "aColumn"
           //they are added to the list box.
            for (int i = 0; i < data.Rows.Count; i++)
             {

            string data1 = data.Rows[i]["aColumn"].ToString();
            listView1.Items.Add(data1);
             }

that should be everything you need to know (in the path of using an adapter, like VIeditorlover mention, it can also easily be done without adapters and tables. but it doesn't hurt to use them.

Happy Coding

[kvprajapati]

I hope you will add your sql statement. The code snippet show the sql query when you press a button.

private void button1_Click_1(object sender, EventArgs e)
        {
            if (listBox1.SelectedItems.Count == 0)
            {
                MessageBox.Show("select items");
                return;
            }
            string s = "";
            for (int i = 0; i < listBox1.SelectedItems.Count; i++)
            {
                  if(i==listBox1.SelectedItems.Count-1)
                      s = s + " item='" + listBox1.SelectedItems[i].ToString() + "'";
                  else
                    s=s + " item='" + listBox1.SelectedItems[i].ToString() + "'  or ";
                
            }
            s = "select * from table where " + s;
            MessageBox.Show(s);
        }

[hemisphere]

thank you for your answers.
the problem with the multiselect thing is this:
if there was only one selected item i could create a tableadapter than bind it easily to the listbox.
but i dont know how many selecteditems there will be, so i should create the query dynamically.
my problem was i don't know how to execute sql in c#.
thanks to "Diamonddrake" now i can populate my listbox.

for just info this will be my query:

string querystr = "SELECT DISTINCT sinif FROM FileTemp WHERE okuladi IN(";
             
                for (int i = 0; i < schoolListBox.SelectedItems.Count; i++)
                {
                    querystr = querystr + "'" + schoolListBox.SelectedItems[i] + "'";

                    if (i != schoolListBox.SelectedItems.Count - 1)
                        querystr = querystr + ",";
                    else
                        querystr = querystr + ")";                                        
                }

[Diamonddrake]

I am glad I could help. I never use the automated databinding. It can make things simpler, but its better to write the code, that way you really know whats happening, so you can understand it, and alter it as necessary.

Best of luck with your project, and as always, Happy Coding

[kvprajapati]

thank you for your answers.
the problem with the multiselect thing is this:
if there was only one selected item i could create a tableadapter than bind it easily to the listbox.
but i dont know how many selecteditems there will be, so i should create the query dynamically.
my problem was i don't know how to execute sql in c#.
thanks to "Diamonddrake" now i can populate my listbox.

for just info this will be my query:

string querystr = "SELECT DISTINCT sinif FROM FileTemp WHERE okuladi IN(";
             
                for (int i = 0; i < schoolListBox.SelectedItems.Count; i++)
                {
                    querystr = querystr + "'" + schoolListBox.SelectedItems[i] + "'";

                    if (i != schoolListBox.SelectedItems.Count - 1)
                        querystr = querystr + ",";
                    else
                        querystr = querystr + ")";                                        
                }

You can add following code to populate another listbox:

SqlDataAdapater adp=new SqlDataAdapter(querystr,"set connection string");
     DataTable dt=new DataTable();
    adp.Fill(dt);
     anotherList.DataSource=dt;
     anotherList.DisplayMember=dt.Columns[0].ColumnName;

[hemisphere]

Antoher question can i populate listbox with a list of string

[kvprajapati]

Antoher question can i populate listbox with a list of string

Create - .mdf (SQL Server Database) for your desktop or web applciation. .sdf is a SQL Server Compact DB and it is used with .NET Compact Framework.