1,105,644 Community Members

Populate listbox with an sql query

Member Avatar
hemisphere
Newbie Poster
3 posts since May 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
VIeditorlover
Junior Poster
141 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 9 [?]
Skill Endorsements: 0 [?]
 
-1
 

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

Member Avatar
Diamonddrake
Master Poster
721 posts since Mar 2008
Reputation Points: 396 [?]
Q&As Helped to Solve: 92 [?]
Skill Endorsements: 8 [?]
 
0
 

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

Member Avatar
__avd
Posting Genius (adatapost)
6,703 posts since Oct 2008
Reputation Points: 1,811 [?]
Q&As Helped to Solve: 1,272 [?]
Skill Endorsements: 61 [?]
Moderator
 
0
 

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);
        }
Member Avatar
hemisphere
Newbie Poster
3 posts since May 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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 + ")";                                        
                }
Member Avatar
Diamonddrake
Master Poster
721 posts since Mar 2008
Reputation Points: 396 [?]
Q&As Helped to Solve: 92 [?]
Skill Endorsements: 8 [?]
 
0
 

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

Member Avatar
__avd
Posting Genius (adatapost)
6,703 posts since Oct 2008
Reputation Points: 1,811 [?]
Q&As Helped to Solve: 1,272 [?]
Skill Endorsements: 61 [?]
Moderator
 
0
 

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;
Member Avatar
hemisphere
Newbie Poster
3 posts since May 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Antoher question can i populate listbox with a list of string

Member Avatar
__avd
Posting Genius (adatapost)
6,703 posts since Oct 2008
Reputation Points: 1,811 [?]
Q&As Helped to Solve: 1,272 [?]
Skill Endorsements: 61 [?]
Moderator
 
0
 

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.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article