-
March 14th, 2009, 07:21 PM
#1
Populate ComboBox from db, Select RecordID
I can populate the drop down list no problem. What I want to do is save the selected data into another table which is related via foreign key. So I want the list to show the text description(Name) but when it is selected I want the EventID to be saved.
Code:
SqlConnection conn = new SqlConnection(@"
server = .\sqlexpress;
integrated security = true;
database = HoopDataSample");
string qryEvents = @"select EventID,Name from Events order by Name";
try
{
conn.Open();
//****Get Event Info to Fill Event Combo Box
SqlCommand cmd = new SqlCommand(qryEvents, conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
comboBoxEvents.Items.Add(rdr[1]); //rdr[0] is the EventID,rdr[1] is the Name
}
rdr.Close();
}
Thanks
Last edited by alephNull; March 15th, 2009 at 09:21 PM.
-
March 15th, 2009, 01:11 PM
#2
Re: Populate ComboBox from db, Select RecordID
At first please kep forum rules using code tags because as you see format goes lost if you dont.#
Code:
SqlConnection conn = new SqlConnection(@"
server = .\sqlexpress;
integrated security = true;
database = HoopDataSample");
string qryEvents = @"select EventID,Name from Events order by Name";
try{
conn.Open();
//****Get Event Info to Fill Event Combo Box
SqlCommand cmd = new SqlCommand(qryEvents, conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()){
MyItem myItem = new MyItem( rdr[0], rdr[1]);
comboBoxEvents.Items.Add(myItem); //rdr[0] is the EventID,rdr[1] is the Name
}
rdr.Close();
}
public class MyItem{
private int _id;
private string _item;
public MyItem( int id, string item){
_id = id;
_item = item;
}
// properties
public int ID{
get {return _id;}
set{ _id = value;}
}
public string Item {
get { return _item;}
set { _item = value;}
}
// This way the object in the combobox will be seen with its text
public override string ToString(){
return _item;
}
}
In the selectedIndexChanged deegate you retriev the item as
Code:
MyItem myItem = comboBoxEvents.SelectedItem as MyItem;
if ( myItem != null){
int id = myItem.ID;
// whatever you want here
}
BTW in a real code I also would make sure that neither rdr[0] nor rdr[1] was read as dbNull value by checkuĂ*ng before adding to the class. This was only done quick to give you the idea
Last edited by JonnyPoet; March 15th, 2009 at 01:16 PM.
Jonny Poet
To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
If anyone felt he has got help, show it in rating the post.
Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
My latest articles :
Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7
-
March 17th, 2009, 08:26 PM
#3
Re: Populate ComboBox from db, Select RecordID
Jonny,
Thanks for the excellent reply. I figured something a little more straighforward.
Code:
// create query
string qryEvents = @"select EventID,Name from Events order by Name";
// create connection
SqlConnection conn = new SqlConnection(@"
server = .\sqlexpress;
integrated security = true;
database = HoopDataSample");
// create Data Adapter & Data Set
SqlDataAdapter da;
DataSet ds;
try
{
//****Get Event Info to Fill Event Combo Box
da = new SqlDataAdapter(qryEvents, conn);
ds = new DataSet();
// fill data set
da.Fill(ds, "eventList");
// load the combobox
comboBoxEvents.DataSource = ds;
comboBoxEvents.DisplayMember = "eventList.Name";
comboBoxEvents.ValueMember = "eventList.EventID";
}
The tricky part was realizing that I had to use the 'dataSetName.columnName' format(ie eventList.Name) when setting the DisplayMember and ValueMember properties.
If I understand it correctly, I can actually combine queries and separate the results by using the Data Tables Collection and populate multiple combo boxes with one SQL Query. I will post that later when I get it to work.
Thanks again
Last edited by alephNull; March 17th, 2009 at 08:49 PM.
-
March 18th, 2009, 03:49 AM
#4
Re: Populate ComboBox from db, Select RecordID
Originally Posted by alephNull
....If I understand it correctly, I can actually combine queries and separate the results by using the Data Tables Collection and populate multiple combo boxes with one SQL Query. I will post that later when I get it to work.
Thanks again
I thought about if I should change your design to dataset, datatable, but as you have already used Datareader I didn't. Sure if you have a lot of database access you can easily bind your database to your cotrols and as we are just talking about tips and tricks: I would not have the names of the adabase fields as strings in the program. What will happen if you are changing the database some years later. You would need to search everywhere in your program for this strings. I'm using the Propertys Resources String table for this and instead of having the names directly in the code I would do
Code:
comboBoxEvents.DisplayMember = Properties.Settings.Default.EventListName;
...
This way going on. So if you ever will have a need to change something on your database you only need to look into your properties Settings table and change the names there.
Additionally I wouldn't have my database access in the same layer as my combobox but instead having a datalayer where I get all my database access and in the view I would have the combobox.
Maybe read a bit about MVP paterrn or MVC pattern
Jonny Poet
To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
If anyone felt he has got help, show it in rating the post.
Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
My latest articles :
Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|