Populate Combobox Text with Query Result

[daniness]

Hello All,

I would appreciate your assistance with the following issue I'm having:

I have 2 tables: locations and depot:

Locations:
site_refnbr (primary key)
site_name
depot_refnbr(foreign key)

Depot
depot_refnbr (primary key)
depot_name

I have 2 forms, frm1 and frm2. Frm1 has a combobox, cboLocations which pulls from the Locations table. Frm2 has textboxes which populate based upon the location selected from cboLocations on frm1. Frm2 also has a combobox, cboDepot, which pulls from the Depot table.

Here is my dilemma:
So far, I've been able to get cboDepot to populate with the first depot_refnbr. This is not the corresponding depot_refnbr that matches with the selected Location; it is just the first depot_refnbr in the Depot table.

What I need it to do:
I need to populate cboDepot's text property with the depot_name, and not the depot_refnbr that corresponds with the Location selected on frm1 from cboLocations.

Frm1 has a button, and I'm not sure if I'd have to code this in the button click event, the cboLocation_Selected IndexChanged event, or in frm2's load event.

Your assistance is greatly appreciated as always!

 

[daniness]

Hi again demausdauth,

Sorry for the delayed response.

Here is what I have in my SelectIndexChanged event for cboDepot on my frm2:

 Private Sub cboDepot_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboDepot.SelectedIndexChanged

        If sender IsNot Nothing Then
            Dim TempCombo As ComboBox = CType(sender, ComboBox)

            'set the textboxes based on the selected value in the combobox
            'Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())
            'Dim drRows() As DataRow = Me.dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())

           [COLOR="Red"] Dim drRows() As DataRow = Me.cboDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())[/COLOR]
            Me.txtDepotName.Text = drRows(0)("depot_name").ToString()
            Me.txtDepotNbr.Text = TempCombo.SelectedValue.ToString()

        End If
...    End Sub

I've tried what you suggested, but the line in red is resulting in an "Overload resolution failed b/c no accessible 'Select' accepts this number of arguments" error. Also, as you can see in the commented lines, when I use dtDepot.Select, I'm getting the syntax error I mentioned in my previous post. As always, I appreciate any help you can provide.

 

[demausdauth]

You won't be able to do the .Select() from cboDepot, that method activates the control or it selects a portion of the displayed text not what we're looking for.
I have rerun my code and it it works fine so I am curious about the error.

Private Sub cboDepot_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboDepot.SelectedIndexChanged

        If sender IsNot Nothing Then
            Dim TempCombo As ComboBox = CType(sender, ComboBox)

            'set the textboxes based on the selected value in the combobox
            Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())
           

            Me.txtDepotName.Text = drRows(0)("depot_name").ToString()
            Me.txtDepotNbr.Text = TempCombo.SelectedValue.ToString()

        End If
End Sub

Did you make sure that the DepotNumber and dtDepot were declared as form level variables? Could you please post the syntax error you are getting again?

 

[daniness]

Here is what the error is appearing as (I'm attaching it). Please advise. Thanks in advance.

 

[demausdauth]

1) Place a breakpoint on that line and then check to make sure that TempCombo.SelectedValue actually has a value.

2) We can change the code a little to help see

Dim drRows() As DataRow 

drRows = dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())

That will tell us which equal sign it is looking at.

3)Also code you post all your code again for this form? I want to see it all together.

 

[daniness]

Okay, so I've tried 1 and 2, but when I hover the mouse over TempCombo.SelectedValue, no value is showing.

Here is my code for this form (Please disregard the commented out lines; they're just code I tried):

Public Class form
    Dim DepotNumber As Integer
    Dim dtDepot As DataTable

    Private FormIsLoading As Boolean

    Private Sub frmLocation_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Dos_trackDataSet.depot' table. You can move, or remove it, as needed.
        'Me.DepotTableAdapter.FillByMatchDepotRefNbr(Me.Dos_trackDataSet.depot)
        'TODO: This line of code loads data into the 'Dos_trackDataSet.depot' table. You can move, or remove it, as needed.
        Me.DispatchTableAdapter.Fill(Me.Dos_trackDataSet.dispatch)
        'TODO: This line of code loads data into the 'Dos_trackDataSet.freight' table. You can move, or remove it, as needed.
        Me.FreightTableAdapter.FillFreightInfo(Me.Dos_trackDataSet.freight)
        'TODO: This line of code loads data into the 'Dos_trackDataSet.depot' table. You can move, or remove it, as needed.

        Me.txtLocation.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "site")
        Me.txtAAC.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "aac")
        Me.txtPhone.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "telephone_nbr")
        Me.txtFax.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "fax_nbr")
        Me.cboFreight.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "freight_refnbr")
        Me.cboDispatcher.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "dispatch_refnbr")
        Me.txtEmail.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "Email")

        'TODO: This line of code loads data into the 'Dos_trackDataSet.locations' table. You can move, or remove it, as needed.
        Me.LocationsTableAdapter.FillbySiteName(Me.Dos_trackDataSet.locations)

        ''TODO: This line of code loads data into the 'Dos_trackDataSet.freight' table. You can move, or remove it, as needed.
        Me.FreightTableAdapter.FillFreightInfo(Me.Dos_trackDataSet.freight)
        'Me.FreightTableAdapter.FillFreightInfo(frmEditDel.Dos_trackDataSet.freight)

        dtDepot = Dos_trackDataSet.depot

        'TODO: This line of code loads data into the 'Dos_trackDataSet.depot' table. You can move, or remove it, as needed.
        Me.DepotTableAdapter.FillByMatchDepotRefNbr(Me.Dos_trackDataSet.depot)

        'Set combobox to datasources
        Me.cboDepot.DisplayMember = "depot_name"
        Me.cboDepot.ValueMember = "depot_refnbr"
        Me.cboDepot.DataSource = dtDepot
        'Me.cboDepot.SelectedValue = "depot_refnbr"

        'Select the one that was chosen on frmEditDel
        'Dim drRows() As DataRow = frmEditDel.Dos_trackDataSet.depot.Select("depot_refnbr = " & DepotNumber)
        Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & DepotNumber)

        Dim ComboSelectIndex As Integer = cboDepot.FindStringExact(drRows(0)("depot_name").ToString())

        cboDepot.SelectedIndex = ComboSelectIndex

        'TODO: This line of code loads data into the 'Dos_trackDataSet.dispatch' table. You can move, or remove it, as needed.
        Me.DispatchTableAdapter.Fill(Me.Dos_trackDataSet.dispatch)

        'Find out if the form is loading, if yes, ignore "SelectedIndexChanged"
        'event of cboDepot
        FormIsLoading = True

        'Allow the combobox to do its work again
        FormIsLoading = False
    End Sub

    Public Sub New(ByVal PassDepotNumber As Integer)
        DepotNumber = PassDepotNumber
        Me.InitializeComponent()
    End Sub

    Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click
        Me.Close()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

    End Sub

    Private Sub cboDepot_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboDepot.SelectedIndexChanged

        'If the form is not loading and the user changes the depot, we want to save it
        If FormIsLoading = False Then
            Try
                'First validate all controls

                Me.Validate()
                'Tell bindingsource to go to end edit mode
                Me.LocationsBindingSource.EndEdit()
                'Update the locations
                Me.LocationsTableAdapter.FillByUpdate(Me.Dos_trackDataSet.locations)

            Catch ex As Exception
                MsgBox("Update failed")
            End Try

        End If

[B]If sender IsNot Nothing Then
            Dim TempCombo As ComboBox = CType(sender, ComboBox)

            'set the textboxes based on the selected value in the combobox
            'Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())
            'Dim drRows() As DataRow = Me.dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())

            'Dim drRows() As DataRow = Me.cboDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())

            [COLOR="Red"]Dim drRows() As DataRow
            drRows = dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())[/COLOR]

            Me.txtDepotName.Text = drRows(0)("depot_name").ToString()
            Me.txtDepotNbr.Text = TempCombo.SelectedValue.ToString()

        End If[/B]
       
    End Sub

    ...End Class

 

[demausdauth]

Okay if TempCombo.SelectedValue, then we know the why we are getting the error now -- there is no value.
Does this error happen when the form loads or when you select something from the combo box?

 

[daniness]

The error occurs after selecting a Location from the combobox on frm1 and then clicking on the "OK" button to load frm2. Any idea of why this is happening?