943,712 Members | Top Members by Rank

Ad:
  • C# Discussion Thread
  • Unsolved
  • Views: 10355
  • C# RSS
May 5th, 2009
0

Populate listbox with an sql query

Expand Post »
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
hemisphere is offline Offline
3 posts
since May 2009
May 5th, 2009
-1

Re: Populate listbox with an sql query

Give up adapters, execute query and refill listbox on your own - or you are so lazy you can not write few lines of code?
Reputation Points: 10
Solved Threads: 9
Junior Poster
VIeditorlover is offline Offline
137 posts
since Dec 2007
May 5th, 2009
0

Re: Populate listbox with an sql query

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.

C# Syntax (Toggle Plain Text)
  1. string querySQL = "";//this is where you pass in you query sting
  2. //just an example connections string
  3. string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="access.mdb";
  4. //create and open our connections
  5. OleDbConnection database database = new OleDbConnection(connectionString);
  6. database.Open();
  7.  
  8. //create a command, and datatable, and adapter
  9. OleDbCommand SQLQuery = new OleDbCommand();
  10. DataTable data = null;
  11. SQLQuery.Connection = null;
  12. OleDbDataAdapter dataAdapter = null;
  13. //---------------------------------
  14. SQLQuery.CommandText = querySQL;
  15. SQLQuery.Connection = database;
  16. data = new DataTable();
  17. dataAdapter = new OleDbDataAdapter(SQLQuery);
  18. dataAdapter.Fill(data);
  19. //you now have a full data table
  20. // now just loop through the data and add the rows to
  21. //the list box this just looks through the table for each row,
  22. //and if the row has an entry in the column "aColumn"
  23. //they are added to the list box.
  24. for (int i = 0; i < data.Rows.Count; i++)
  25. {
  26.  
  27. string data1 = data.Rows[i]["aColumn"].ToString();
  28. listView1.Items.Add(data1);
  29. }
  30.  

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
Reputation Points: 442
Solved Threads: 89
Master Poster
Diamonddrake is offline Offline
721 posts
since Mar 2008
May 5th, 2009
0

Re: Populate listbox with an sql query

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

C# Syntax (Toggle Plain Text)
  1. private void button1_Click_1(object sender, EventArgs e)
  2. {
  3. if (listBox1.SelectedItems.Count == 0)
  4. {
  5. MessageBox.Show("select items");
  6. return;
  7. }
  8. string s = "";
  9. for (int i = 0; i < listBox1.SelectedItems.Count; i++)
  10. {
  11. if(i==listBox1.SelectedItems.Count-1)
  12. s = s + " item='" + listBox1.SelectedItems[i].ToString() + "'";
  13. else
  14. s=s + " item='" + listBox1.SelectedItems[i].ToString() + "' or ";
  15.  
  16. }
  17. s = "select * from table where " + s;
  18. MessageBox.Show(s);
  19. }
Moderator
Reputation Points: 2136
Solved Threads: 1228
Posting Genius
adatapost is offline Offline
6,527 posts
since Oct 2008
May 6th, 2009
0

Re: Populate listbox with an sql query

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:

C# Syntax (Toggle Plain Text)
  1. string querystr = "SELECT DISTINCT sinif FROM FileTemp WHERE okuladi IN(";
  2.  
  3. for (int i = 0; i < schoolListBox.SelectedItems.Count; i++)
  4. {
  5. querystr = querystr + "'" + schoolListBox.SelectedItems[i] + "'";
  6.  
  7. if (i != schoolListBox.SelectedItems.Count - 1)
  8. querystr = querystr + ",";
  9. else
  10. querystr = querystr + ")";
  11. }
Reputation Points: 10
Solved Threads: 0
Newbie Poster
hemisphere is offline Offline
3 posts
since May 2009
May 7th, 2009
0

Re: Populate listbox with an sql query

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
Last edited by Diamonddrake; May 7th, 2009 at 9:34 pm.
Reputation Points: 442
Solved Threads: 89
Master Poster
Diamonddrake is offline Offline
721 posts
since Mar 2008
May 8th, 2009
0

Re: Populate listbox with an sql query

Click to Expand / Collapse  Quote originally posted by 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:

C# Syntax (Toggle Plain Text)
  1. string querystr = "SELECT DISTINCT sinif FROM FileTemp WHERE okuladi IN(";
  2.  
  3. for (int i = 0; i < schoolListBox.SelectedItems.Count; i++)
  4. {
  5. querystr = querystr + "'" + schoolListBox.SelectedItems[i] + "'";
  6.  
  7. if (i != schoolListBox.SelectedItems.Count - 1)
  8. querystr = querystr + ",";
  9. else
  10. querystr = querystr + ")";
  11. }

You can add following code to populate another listbox:
C# Syntax (Toggle Plain Text)
  1. SqlDataAdapater adp=new SqlDataAdapter(querystr,"set connection string");
  2. DataTable dt=new DataTable();
  3. adp.Fill(dt);
  4. anotherList.DataSource=dt;
  5. anotherList.DisplayMember=dt.Columns[0].ColumnName;
Moderator
Reputation Points: 2136
Solved Threads: 1228
Posting Genius
adatapost is offline Offline
6,527 posts
since Oct 2008
May 8th, 2009
0

Re: Populate listbox with an sql query

Antoher question can i populate listbox with a list of string
Last edited by hemisphere; May 8th, 2009 at 5:14 am.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
hemisphere is offline Offline
3 posts
since May 2009
May 8th, 2009
0

Re: Populate listbox with an sql query

Click to Expand / Collapse  Quote originally posted by hemisphere ...
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.
Moderator
Reputation Points: 2136
Solved Threads: 1228
Posting Genius
adatapost is offline Offline
6,527 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in C# Forum Timeline: creating buttons inside button
Next Thread in C# Forum Timeline: dll in windows form C#





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC