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!

 

[demausdauth]

Is frm2 opened modal?

 

[daniness]

Hi demausdauth,

On frm1, I'm calling frm 2 as follows:

Dim frmLocation As New form
frmLocation.ComboValue = frmLocation.cboDepot.Text
'frmLocation.Show()
frmLocation.ShowDialog()

I believe this is supposed to open frm2 as modal. Please let me know.

 

[demausdauth]

Dim frmLocation As New form

First of all you are declaring frmLocation as a new Form when you should be declaring it as a new frm2.

frmLocation.ComboValue = frmLocation.cboDepot.Text

This says that you want to set some property on the instance frmLocation to the Text value selected from cboDepot that is on the frmLocation instance. You are referencing the same form.

frmLocation.ShowDialog()

And yes this will give you a modal window.

 

[demausdauth]

Your table structure really only allows one Depot per Location a more normalized structure would be:

[B]LOCATIONS[/B]
site_refnbr (PK)
site_name

[B]DEPOTLOCATIONS[/B]
site_refnbr (FK)
depot_refnbr(FK)

[B]DEPOT[/B]
depot_refnbr (PK)
depot_name

This structure would now give you the ability to have multiple depots at each location, also it will be easier to query and fill the combo boxes. I don't know the rest of the structure of the tables in question nor the database, so this may not be an issue.

 

[demausdauth]

I don't mean to bash you for your table structure or your programming so I apologize if that is how it sounds. I believe that ultimately you are looking for a way to pass the site value from frm1 to frm2. That can be done in many different ways. I use one of two ways either through properties or through the New statement.

Using New statement
frm2

Public Class frm2

    Dim SiteNumber As Integer

    Public Sub New(ByVal PassSiteNumber As Integer)

        SiteNumber = PassSiteNumber

        Me.InitializeComponent()

    End Sub
End Class

and on frm1 you would have

  Private Sub btnDepots_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDepots.Click

        Dim frmDepot As New frm2(Convert.ToInt32(cboLocations.SelectedValue))

        If frmDepot.ShowDialog() = Windows.Forms.DialogResult.OK Then
            'do processing for if the form return ok
            MessageBox.Show(String.Format("Returned ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))
        Else
            'do processing for if the form returns anything but ok
            MessageBox.Show(String.Format("Returned NOT ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))

        End If
    End Sub

Using Properties
frm2

Public Class frm2

    Dim SiteNumber As Integer

    Public Property pSiteNumber() As Integer
        Get
            Return SiteNumber
        End Get
        Set(ByVal value As Integer)
            SiteNumber = value
        End Set
    End Property
End Class

and on frm1 you would have

 Private Sub btnDepot_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDepots.Click
        Dim frmDepot As New frm2()
        frmDepot.pSiteNumber = Convert.ToInt32(cboLocations.SelectedValue)

        If frmDepot.ShowDialog() = Windows.Forms.DialogResult.OK Then
            'do processing for if the form return ok
            MessageBox.Show(String.Format("Returned ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))
        Else
            'do processing for if the form returns anything but ok
            MessageBox.Show(String.Format("Returned NOT ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))

        End If


    End Sub

 

[daniness]

Hi demausdauth,

Thanks so much for your input. It's VERY helpful!

Regarding the table structure, the way the program works is that each location has one depot, but if the user wants, they should be able to change the depot for a particular site by selecting another depot name from frm2's cboDepot. I'm assuming the relationship b/w depot and location is 1 to many b/c based on the pre-existing table structure, which looks like:

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

Depot
depot_refnbr (primary key)
depot_name

Also, I'm having the cboDepot only show the depot name, so I'm a bit confused when you say, "MessageBox.Show(String.Format("Returned ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))". Can you please advise on how I would integrate the depot_refnbr into the cboDepot to make sure the correct depot name shows, based on the foreign key, depot_refnbr, which is tying the 2 tables together?

Thanks again for all your help!

 

[demausdauth]

So based on this new info -- you want frm2 to show the current selection in textboxes and to also fill a combobox with all of the depots but to select the one associated with what was selected on frm1.

I made a simple little test project frm1 has a single combobox(cboLocations) and a single button(btnDepot). I use the button to open the Depot form which is frm2. The Depot form has a combox (cboDepot), 2 textboxes (txtDepotName and txtDepotRefNum) and a single button (btnDone). The text boxes are only there to show values. The btnDone tells when you are finished with whatever you need to do on this form and closes it.

frm1

Public Class frm1

    Dim dtLoc As DataTable

    Private Sub frm1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Fill cboLocations Combobox from the Locations table
        'SELECT * FROM locations 

        dtLoc = modVbDotNet.MakeLocTable()

        cboLocations.DisplayMember = "site_name"
        cboLocations.ValueMember = "depot_refnbr"
        cboLocations.DataSource = dtLoc



    End Sub

    Private Sub btnDepots_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDepots.Click

        'This creates the Depot form to open the depot associated with the location
        ' with the New statement we pass the selected depot_refnbr to the depot form so it
        ' knows which depot was selected.
        Dim frmDepot As New frm2(Convert.ToInt32(cboLocations.SelectedValue))

        'Here we are showing the form as a modal window and waiting for a result
        If frmDepot.ShowDialog() = Windows.Forms.DialogResult.OK Then
            'do processing for if the form return ok
            MessageBox.Show(String.Format("Returned ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))
        Else
            'do processing for if the form returns anything but ok
            MessageBox.Show(String.Format("Returned NOT ok {0} {1}", frmDepot.txtDepotName.Text, frmDepot.txtDepotRefNum))

        End If
    End Sub
End Class

frm2

Public Class frm2

    Dim DepotNumber As Integer
    Dim dtDepot As DataTable


    Public Sub New(ByVal PassDepotNumber As Integer)


        DepotNumber = PassDepotNumber

        Me.InitializeComponent()

    End Sub

    Private Sub frm2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Fill the list of depots in the combo box
        'SELECT * FROM depot

        dtDepot = modVbDotNet.MakeDepotTable2()

        cboDepot.DisplayMember = "depot_name"
        cboDepot.ValueMember = "depot_refnbr"
        cboDepot.DataSource = dtDepot

       
        'now we need to select the one that was chosen on frm1
        Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & DepotNumber)

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

        cboDepot.SelectedIndex = ComboSelectIndex




    End Sub

    Private Sub cboDepot_SelectedIndexChanged(ByVal sender As System.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())

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


        End If

    End Sub


    Private Sub btnDone_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDone.Click
        'You can do validation checks here before allowing the window to close.
        ' If you had to pull from a database or rely on some other process to run if it errors
        ' then you could return Windows.Forms.DialogResult.Abort and then frm1 would know that
        ' something was wrong and would be able to handle it.

        Me.DialogResult = Windows.Forms.DialogResult.OK
        Me.Close()

    End Sub
End Class

 

[daniness]

Wow, demausdauth...thanks so much for your guidance! It's VERY helpful and VERY appreciated!

I tried what you've suggested here, however I'm getting "Name 'modVbDotNet' is not declared" errors. I tried substituting 'modVbDotNet' with the name for my datset, but it didn't work. Can you please tell me what the 'modVbDotNet' is?

 

[demausdauth]

modVbDotNet is a module that I created to make psuedo tables to test the code that I wrote. As my comments just above those lines suggest, you would either select from your database or since you say you have dataset you would write

dtDepot = YourDataSet.Tables("YourDepotTableName")

the same with the locations part

 

[daniness]

Hi demausdauth,

I'm trying to work out some other issues I'm having, but I'll get back to you on this. Thanks for all your help.

 

[daniness]

...

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

            txtDepotName.Text = [COLOR="Red"]drRows(0)[/COLOR]("depot_name").ToString()
            txtDepotRefNum.Text = TempCombo.SelectedValue.ToString()


        End If

    End Sub

...
    End Sub
End Class

Good day demausdauth,

Thank you so much for your advice. I tried the code you've suggested. However, the part marked in red is returning an Index out of Range Exception, saying "Index was outside the bounds of the array". I tried changing it to drRows(7) and drRows(8), because in my Depot table, there are 8 different depots, but neither worked and returned the same error. I wonder what is causing this.

 

[demausdauth]

Let's break this down.
First,
DepotNumber = is a form level integer variable that holds the value of the depot_refnbr that was selected on frm1 and passed through the New constructor.

dtDepot = is a form level DataTable object that holds information for all the depots.

The form loads and we need to get the full list of depots from the database or dataset. In my case I use a method that creates datatable from scratch and fills it with bogus values, this is the modVbDotNet.MakeDepotTable2(). You would use YourDataBase.Tables["DepotTableName"].

      Private Sub frm2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Fill the list of depots in the combo box
        'SELECT * FROM depot

        dtDepot = modVbDotNet.MakeDepotTable2()

I made the assumption that when you filled the database with information that you were using all the columns from the table structure that you provided. So then your depot table would contain columns for the depot_refnbr and depot_name. We tell the combo box which column's values will pertain to either the display value or the value of the selection. In this case we want the depot name to display and the reference number to be the value of the selection. Finally we assign it the datasource, dtDepot, which is a DataTable of all the depots.

        cboDepot.DisplayMember = "depot_name"
        cboDepot.ValueMember = "depot_refnbr"
        cboDepot.DataSource = dtDepot

So now we have a combobox with all of the depots, but we want to set the combobox to the depot that has been chosen from frm1, which is stored in DepotNumber. Well we only passed the reference number from frm1, so how do we get the depot name? The fortunate thing is, is that we have a complete list of all the depots and their respective reference numbers. So what we need to do is match the DepotNumber to one of the rows in dtDepot, which is what the DataTable.Select() method does. We are selecting from the dtDepot table the one row that matches our DepotNumber number. Since the Select() method returns an array of DataRows we still need to declare drRows as an array of DataRows to hold the results, even though the results should only be a single row.

        'now we need to select the one that was chosen on frm1
        Dim drRows() As DataRow = dtDepot.Select("depot_refnbr = " & DepotNumber)

drRows should now contain a single row with all the information where the depot_refnbr = DepotNumber. Once again, drRows is an array of DataRows that through the Select() method should now contain a single row that contains all the information where depot_refnbr = DepotNumber. Since it is an array we need to make references as such and since there is only 1 row we reference it as drRows(0) or the first row in the array. The combo box has a few methods to find the index of an item based on values, we are using the FindStringExact method. This matches whatever value we pass to it to the DisplayValue column values, IOW, the depot name, and returns the index of the item within the combobox.

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

Now that we know the index of the item that has the same depot_name as the reference number that was passed from frm1, we force it to be the selected index.

        cboDepot.SelectedIndex = ComboSelectIndex
    End Sub

If you are getting an 'index out of range' error where you say, then you are not getting the correct selection. I would put a breakpoint on the following line
cboDepot.DisplayMember = "depot_name"

and then examine dtDepot or your relevant table to make sure it has values. Then step through the code and examine drRows after the Select() and see if it has values.

 

[daniness]

Hello demausdauth!

Once again, thank you VERY much for your guidance. I appreciate the breakdown. I'm a total novice to vb.net and it has been rather frustrating to learn it. I've been analyzing your last post and have been trying to implement it.

For some reason, on the cboDepot Selected Index Changed event of frm2, I'm receiving a "Syntax error: Missing operand after '=' operator". I double checked the code and it's matching with yours. I've also tried replacing dtDepot with Me.dtDepot, but no luck...I could not find anything on this error in Google, either. Any ideas? This is how it looks as of right now:

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())
[COLOR="Red"]            Dim drRows() As DataRow = Me.dtDepot.Select("depot_refnbr = " & TempCombo.SelectedValue.ToString())[/COLOR]
            Me.txtDepotName.Text = drRows(0)("depot_name").ToString()
            Me.txtDepotNbr.Text = TempCombo.SelectedValue.ToString()

        End If

        '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
    End Sub

 

[demausdauth]

I am sorry I read your previous post incorrectly, I had thought that you were referring to the Load event. Can't even read my own code. However it worked out pretty well since some of the same things were covered. Once again I am sorry if I caused any confusion.

Do you have dtDepot declared as a form level variable? If you will look back to post #8 there are 2 form level variables DepotNumber and dtDepot. That is the only thing that I can think of that would causing a Syntax error on that line. Make sure that whatever you are using as the datasource for cboDepot is the same as you are selecting from in cboDepot_SelectedIndexChanged.