CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    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

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  3. #3
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  4. #4
    Join Date
    Aug 2011
    Posts
    3

    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?

  5. #5
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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
  •  





Click Here to Expand Forum to Full Width

Featured