Retrieving from database based on choice made....

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
Ok, here is my problem. I have a form with 4 comboboxes. the comboboxed are filled with items in 4 categories from a database. What I need to to is that when a user makes a selection from the combobox(the item's selectedindex.changed), I want it to go back to the database and pull the price for the item. The items have an item ID as well, and I am thinking that I need to load this value into the comboboxes as well (thoughnot make it visible) I am doing the DB by code, not by wizard.

Any ideas?
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Add the items with datasouce =, use displaymember= for the visable items, and valuemember= to store the values.

If you need any further help with this let me know

TPM
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
Ok, here is what I have so far, I am a little confused by what you mean though.

http://www.static-thought.net/TempStuff/WindowsApplication1.zip

The program runs, loads the Items into the combo boxes fine.

What I need to do is have the user select an item in a combo box, and then have the item's price appear in "txtSubtotal" as they choose more items, they will be added to the subtotal with code in the SelectedIndexChanged event like this:

subtotal+=(**insert code or variable of value from database**)
txtSubtotal=format(subtotal, "currency")

This way after each item is chosen, the item's price is added to the subtotal. But I need a way fo setting the items to their price values.

Thanks for any help!
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
I am not sure how to bind it via code, but I am populating the comboboxes like this:

Do While MenuReader.Read()
ItemName = MenuReader.GetString(0)
ItemCategory = MenuReader.GetString(1)
If MenuReader.GetString(1) = "Beverage" Then
cboBeverage.Items.Add(ItemName)
ElseIf MenuReader.GetString(1) = "Appetizer" Then
cboAppetizer.Items.Add(ItemName)
ElseIf MenuReader.GetString(1) = "Main Course" Then
cboMainCourse.Items.Add(ItemName)
ElseIf MenuReader.GetString(1) = "Dessert" Then
cboDessert.Items.Add(ItemName)
End If
Loop
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
:S

Ok to bind a dataset to a combo box you'll use:
combobox.datasource = YourDataset
combobox.displaymember = table.column 'This is what's displayed in the combobox
combobox.valuemember = table.column 'This is what the item's value is set to (in your case the price)

to get the value (or price) of the selected item you'd use:
combobox.selectedvalue

TPM
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
ok, I added this:

Dim SelectSQL As String = "Select itemID, name, category, price From menu"
cnnMenu.ConnectionString = "Provider=Microsoft.Jet.OleDB.4.0;" & _
"Data Source=menu2.mdb"
odaMenu.SelectCommand = New OleDb.OleDbCommand(SelectSQL, cnnMenu)
odaMenu.SelectCommand.Parameters.Add("category", Data.OleDb.OleDbType.BSTR, 50, "category")
dsFood.Tables.Add("menu")

cboBeverage.DataSource = dsFood.Tables("menu")
cboBeverage.DisplayMember = Menu.name
cboBeverage.ValueMember = Menu.price

but the last two lines produce errors! What am I doing wrong??
Aslo, if this does work, how do I go about sorting the items via their category?

Thank you for your help!
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Try this:
cboBeverage.DisplayMember = "Menu.name"
cboBeverage.ValueMember = "Menu.price"

T sort the items you can use orderby in your select statment.
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
TPM said:
cboBeverage.ValueMember = "Menu.price"
I get htis error:

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Cannot create a child list for field menu.
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
yes, that is right...

with teh code that I had posted above where I populated using the if..ElseIF..end, can I add the price in there (invisible) and when the selected index changes, copy the price to the txtbox? I know it isn't accessing the database, but I think it would theoretically work...
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
on looking at you code again I see that your reference the datatable as the datasource, that means you dont need it for the displaymember or valuemember. I.e.
cboBeverage.DisplayMember = "name"
cboBeverage.ValueMember = "price"

Yes you can, but this will be a lot cleaner.
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
okay, I made the change, but the valumember line still gives me trouble:

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Could not bind to the new display member.

if I comment out the valumember line, it will run, but nothing is populated in the combobox.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Aagh I see the problem.. ..there's no data or columns in table menu. try this:

Dim SelectSQL As String = "Select itemID, name, category, price From menu"
cnnMenu.ConnectionString = "Provider=Microsoft.Jet.OleDB.4.0;" & _"Data Source=menu2.mdb"
odaMenu.oledataadapter = New OleDb.OleDtaadapter(SelectSQL, cnnMenu)
odaMenu.SelectCommand.Parameters.Add("category", Data.OleDb.OleDbType.BSTR, 50, "category")
cnnMenu.open
odamenu.fill(dsfood)
cnnMenu.close

cboBeverage.DataSource = dsFood
cboBeverage.DisplayMember = "Table1.name"
cboBeverage.ValueMember = "Table1.price"
 
Last edited:

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
I get htis error:

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll

Additional information: Cannot create a child list for field Table1.

What does it mean by a child list?
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Try changing your dataset to a datatable and remove table1 from display/value member:

cboBeverage.DisplayMember = "name"
cboBeverage.ValueMember = "price"
 

gdubbs

Member
Joined
Oct 26, 2004
Messages
11
Location
USA
Programming Experience
Beginner
It took me longer to code, but what I ended up doing is to make array's where values for the price could be placed. That way, the comboboxes selectedindex value is that same as the price in the array (0, 1, 2, 3, 4...etc)
 
Top Bottom