Populate Combobox w/Selected Item and All Other Items

[daniness]

Hello All,

Was wondering if I could please get some assistance with this. I have a frm1 and frm2. frm1 has a combobox, cboLocations, from which a location is selected and then the "Ok" button is clicked upon which frm2 loads. frm2 contains textboxes and other comboboxes, which all should populate based on the selection made on frm1. So far, I have frm2's textboxes populating properly with the respective info for the selected location, i.e. location name, phone, fax, email, etc. There are also 3 comboboxes, which need to populate with the corresponding info as well as show the other available items when you click on the dropdown. Right now, I have one of the comboboxes, cboDepot, being populated with the corresponding Depot for the Location being selected on frm1, but I also need it to list the other available depots. Any help with this would be greatly appreciated. The below is my code for this particular combobox:

Private Sub FillDepotCombo()

       Dim sql As String = "Select distinct depot.depot_name, depot.depot_refnbr, locations.site from depot inner join locations on depot.depot_refnbr = locations.depot_refnbr and Site ='" & frmLocations.cboLocations.SelectedItem().ToString() & "'"

       Dim cmdCorrespDepot As New SqlCommand(sql, conn)

        Dim daDepot As SqlDataAdapter = New SqlDataAdapter(cmdCorrespDepot)
        Dim dsDepot As New DataSet
        Dim dtDepot As New DataTable("Depot")
        Dim depotNbr As Integer

        daDepot.Fill(dsDepot, "Depot")
        Dim drDepot As SqlDataReader = cmdCorrespDepot.ExecuteReader

        'Read records
        While drDepot.Read
            cboDepot.Items.Add(drDepot("depot_name"))
            depotNbr = drDepot("depot_refnbr")
        End While

        cboDepot.Text = cboDepot.Items(0).ToString

        drDepot.Close()
        cmdCorrespDepot.Cancel()
        cmdCorrespDepot.Dispose()

    End Sub

 

[MattP]

I'm a little confused as to what you've got going on here. It looks like you've got a DataAdapter which you use to fill a DataSet which you completely ignore when you're done filling it. You then create a SqlDataReader and loop over each record adding 1 column to a ComboBox's Items collection. depotNbr doesn't seem to be doing anything other than getting overwritten each time you come across a new record in the datareader.

I'm not trying to be rude at all, just pointing out you've got extra code you don't need. Rather than query the database again using the exact same SqlCommand just bind the ComboBoxes DataSource to dtDepot and set the DisplayMember to "depot_name". I'm not sure from your code but it looks like depotNbr is what you'd like to use as the ComboBoxes ValueMember.

cboDepot.DataSource = dtDepot
cbo.DisplayMember = "depot_name"
cbo.ValueMember = "depot_refnbr"
cbo.SelectedIndex = 0

I would suggest taking a look at the Forms over Data Video Series especially #4. You'll find that Visual Studio can do most of the heavy lifting for you so you can spend more time working on the program's logic.

 

[daniness]

Hi MattP and All,

Please pardon my ignorance as I am a novice to Vb.Net. Could you please advise at to which lines should be substitued with the code you've provided?

Thanks.

 

[MattP]

You can put that in right after the DataAdapter's Fill. The SqlDataReader code can all go because you've already got that data in the DataSet.