-
August 1st, 2011, 07:19 AM
#1
Database Frontend
Hi, Im trying to develop a frontend for a database project I have been working on, I am using a combobox to select a meal_name and need the primary key to update another table for orders, How can I retreive the primary key using the meal name selected.
below is the code I am using to add meal names to the combobox
Private Sub ComboBox1_MouseEnter(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.MouseEnter
Dim myRow As DataRow
Dim CmdStr As String
Dim myDataSet As DataSet = New DataSet()
Dim ConStr As String = "server = USER-PC\SQLEXPRESS ; Database = happy_meals ; integrated security=true"
CmdStr = "SELECT Meal_Name FROM Meal"
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(CmdStr, ConStr)
myDataAdapter.Fill(myDataSet, "Meal")
ComboBox1.Items.Clear()
With myDataSet
For Each myRow In myDataSet.Tables("Meal").Rows
ComboBox1.Items.Add(myRow("Meal_Name"))
Next
End With
End Sub
-
August 1st, 2011, 09:58 AM
#2
Re: Database Frontend
You would need to either
Add the needed field to your initial select statement and then search through you datatable for the row containing the meal name selected.
or
Use a select statement with a where clause to retrive the correct value from the database when the combo box selection has changed.
"Select MyKeyField from MyDB where MyField='MyData'"
Always use [code][/code] tags when posting code.
-
August 1st, 2011, 10:02 AM
#3
Re: Database Frontend
Also you do not want your code which populates the Combobox to reside in the mouse enter event rather it should be in a seperate sub routine that gets called only if needed like during form loading or some other case where the contents need to be updated.
As is your combo box will be clearing itself and reloading from the database anytime the user moves to mouse to that location. Will cause the program to use a lot more resources than needed for this functionality.
Always use [code][/code] tags when posting code.
-
August 1st, 2011, 06:11 PM
#4
Re: Database Frontend
Thanks, have you got some code example I could use as a guideline to search through the datatable after ammending the select statement to include the required data, also any ideas, I could use in a scenario simmilar to this one but where theres a possibility that names are duplicated?
-
August 1st, 2011, 07:38 PM
#5
Re: Database Frontend
I would most likely go with a sql statement rather than scanning the data table but then I am not clear on what you are doing. You can of course loop through your datatable for a match or perhaps move directly to the record based on the index of the selected item in the combo, assuming that the combo items and data table rows are in the same order and also assuming that there is a method to move to a record by number in the datatable [ I haven't worked much with the data table object so I do not know if this method exists or not]
As for duplicates there has to be a way to tell one from another otherwise there is no way for the user to pick the right one.
Another option that I have used in VB6 and I assume will work in VB.Net but haven't tested is the use of a null seperator. In theory you can concantonate a string which contains your Name a Null and the RecordKey. If the combos still work as they did in VB6 nothing after the null will be displayed but when the user selects an item you can use the Split() method to to parse the selection into Name and Key fields which you can then process as needed.
Always use [code][/code] tags when posting code.
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
|