[RESOLVED] Getting information from DataSet to populate defaults in controls.

[BrailleSchool]

Hi all,

I have a form that has many controls. My next task is to populate the phone number (MaskedTextBox) and phone number type (ComboBox) when the cursor has lost focus from a ComboBox that has auto complete functionality. I have tried to do it and when the cursor leaves focus, the other controls are not populated. Nothing happens. I do not get any errors either. Screen shot attached for the form and I am also using a stored procedure to do this. There are two tables in question here. A client table that the name and phone number and then the second table that has the phone number type (cell, work, home, etc). There is an FK relationship between the two.

This is my stored procedure. It is called sp_GetClientInfoByName.

CREATE PROCEDURE sp_GetClientInfoByName
@ClientName VARCHAR(50)
AS
BEGIN
SELECT ClientInformation.ClientName,
       ClientInformation.ClientPhoneNumber,
       ClientInformation.PhoneNumberTypeID_PKFK,
       PhoneNumber_Type.PhoneNumberTypeID_PK, 
       PhoneNumber_Type.PhoneNumberTypeName
FROM ClientInformation
INNER JOIN PhoneNumber_Type
ON ClientInformation.PhoneNumberTypeID_PKFK = PhoneNumber_Type.PhoneNumberTypeID_PK
WHERE ClientInformation.ClientName = @ClientName
END

The code I am trying to use is as follows (partially completed).

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database for existing clients based on name.
        'If multiple phone numbers exist for the client, show a form to be able to select a phone number.
        'If single phone number exists, just populate the form.
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            Dim TableName As String = "ClientInformation"
            Dim ColumnName As String = "ClientName"
 
            Try
                'Create a new SQLCommand.
                Dim GetClientInfo As SqlCommand = New SqlCommand
 
                'Open the database connection.
                SetDatabaseConnection.Open()
 
                'Set some values to the SQLCommand object.
                With GetClientInfo
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "sp_GetClientInfoByName"
                    .Parameters.AddWithValue("@ClientName", cboClientName.Text.Trim)
                End With
 
                'Create a new DataSet.
                Dim ClientInfoDS As New DataSet(GetClientInfo.CommandText)
                'Create a new DataAdapter.
                Dim ClientInfoDA As SqlDataAdapter = New SqlDataAdapter(GetClientInfo)
 
                'Set the Current Culture of the DataSet.
                ClientInfoDS.Locale = CurrentCulture
 
                'Clear the DataSet of any previously obtained database information.
                ClientInfoDS.Clear()
 
                'Refill the DataAdapter with new information obtained from the database.
                ClientInfoDA.Fill(ClientInfoDS, TableName)
 
                'First check to see if any records.  If no records, add a default item.
                Dim ClientInfoDT As DataTable = New DataTable
 
                'Populate the DataTable.
                ClientInfoDT = ClientInfoDS.Tables(TableName)
 
                'Set the culture of the DataTable.
                ClientInfoDT.Locale = CurrentCulture
 
                If ClientInfoDT.Rows.Count > 0 Then 'Has records.
                    'Now that there are records returned, see if there is only one or multiple.
                    'If there is one record, just populate the form.
                    'If there are multiple, show a form to choose the phone number and phone number type.
                    Select Case ClientInfoDT.Rows.Count
                        Case 1 'One record only.
                            'Just populate the form.
                            Dim TheClientInfoDR As DataRow = ClientInfoDS.Tables(TableName).Rows.Find(cboClientName.Text.Trim)
                            mtbClientPhoneNumber.Text = TheClientInfoDR(1).ToString 'Phone Number.
                            cboPhoneNumberType.Text = TheClientInfoDR(2).ToString 'Phone Number Type.
                        Case Else 'More than one record returned.
                            'Show a form to choose the phone number and phone number type.
 
                    End Select
                Else 'Has no records.
                    'Focus Client Phone Number.
                    mtbClientPhoneNumber.Focus()
                End If
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error retrieving data from table name " & TableName & " for the column " & ColumnName & "." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database " & TableName & " to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As InvalidOperationException
                MessageBox.Show(ex.Message, _
                                "Invalid Operation Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As NullReferenceException
                MessageBox.Show(ex.Message, _
                                "Null Reference Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

What am I doing wrong?

[BrailleSchool]

OK, I have been doing some research and I have found that because there are two tables involved here, I have to create a DataRelation between the two tables. I will try this and be back if I have any problems.

[BrailleSchool]

OK, I have never worked with a data relation before, so this is my first time. I am getting a Null Reference Exception. The error is generated when I tried to add the DataRelation. This is my code. Please assist if you can, thanks.

Null Reference Exception.
Object reference not set to an instance of an object.

Error happens on this line:

'Setup a DataRelation between ClientInformation and PhoneNumber_Type tables.
                ClientInfoDS.Relations.Add("Phone Numbers", _
                                           ClientInfoDS.Tables(TableName1).Columns(TableName1_PKFKColumn), _
                                           ClientInfoDS.Tables(TableName2).Columns(TableName2_PKColumn))

Code

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database for existing clients based on name.
        'If multiple phone numbers exist for the client, show a form to be able to select a phone number.
        'If single phone number exists, just populate the form.
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            Dim TableName1 As String = "ClientInformation"
            Dim TableName1_ColumnName As String = "ClientName"
            Dim TableName1_PKFKColumn As String = "PhoneNumberTypeID_PKFK"
            Dim TableName2 As String = "PhoneNumber_Type"
            Dim TableName2_ColumnName As String = "PhoneNumberTypeName"
            Dim TableName2_PKColumn As String = "PhoneNumberTypeID_PK"
 
            Try
                'Open the database connection.
                SetDatabaseConnection.Open()
 
                'Create a new SQLCommand.
                Dim GetClientInfo As SqlCommand = New SqlCommand
 
                'Set the attributes for the SQLCommand.
                With GetClientInfo
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "sp_GetClientInfoByName"
                    .Parameters.AddWithValue("@ClientName", cboClientName.Text.Trim)
                End With
 
                'Create a new DataSet.
                Dim ClientInfoDS As DataSet = New DataSet(GetClientInfo.CommandText)
 
                'Create a new DataAdapter.
                Dim ClientInfoDA As SqlDataAdapter = New SqlDataAdapter(GetClientInfo)
 
                'Set the CurrentCulture of the DataSet.
                ClientInfoDS.Locale = CurrentCulture
 
                'Clear the DataSet of any previously obtained information.
                ClientInfoDS.Clear()
 
                'Refill the DataAdapter with new information obtained from the database.
                ClientInfoDA.Fill(ClientInfoDS, TableName1)
 
                'Create two DataRows because there are two tables to work with.
                Dim ParentDR, ChildDR As DataRow
 
                'Setup a DataRelation between ClientInformation and PhoneNumber_Type tables.
                ClientInfoDS.Relations.Add("Phone Numbers", _
                                           ClientInfoDS.Tables(TableName1).Columns(TableName1_PKFKColumn), _
                                           ClientInfoDS.Tables(TableName2).Columns(TableName2_PKColumn))
 
                'Display the content from the tables.
                For Each ParentDR In ClientInfoDS.Tables(TableName1).Rows
                    For Each ChildDR In ParentDR.GetChildRows(TableName2)
                        'Check to see if anything is within the DataSet.
                        If ClientInfoDS.Tables(TableName1).Rows.Count > 0 Then
                            'Information has been returned.
                            'Now if there is only one phone number returned, populate the other controls.
                            'If there are multiple phone numbers returned, show a form for the end-user to
                            'choose the phone number and phone number type they want.
                            Select Case ClientInfoDS.Tables(TableName1).Rows.Count
                                Case 1 'One record returned.
                                    'Populate the other controls.
                                    mtbClientPhoneNumber.Text = ParentDR(TableName1_ColumnName).ToString
                                    cboPhoneNumberType.Text = ChildDR(TableName2_ColumnName).ToString
                                Case Else 'Multiple records returned.
                                    'Show a form for the end user to choose which phone number/phone number type to select.
                                    'TODO
                            End Select
                        Else 'Nothing returned.
                            'Focus the client phone number field.
                            mtbClientPhoneNumber.Focus()
                        End If
                    Next ChildDR
                Next ParentDR
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error retrieving data from the databse." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As InvalidOperationException
                MessageBox.Show(ex.Message, _
                                "Invalid Operation Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As NullReferenceException
                MessageBox.Show(ex.Message, _
                                "Null Reference Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

[jmcilhinney]

You've got your parent and child around the wrong way.

[BrailleSchool]

You've got your parent and child around the wrong way.

I tried that and I got the same error as before.

[jmcilhinney]

OK, I just had a closer look at your code. A DataRelation exists between two DataTables. You're only populating one DataTable though. That would explain why you're getting a NullReferenceException. You're trying to get two DataTables from the DataSet but at least one of them doesn't exist. You do this:

ClientInfoDA.Fill(ClientInfoDS, TableName1)

so that one's in there but there's no other call to Fill.

[BrailleSchool]

I am also seeing that I am only filling the DataAdapter with the first table. Don't I have to do it with both tables?

[jmcilhinney]

You might want to follow the CodeBank link in my signature and check out my thread on Master/Detail Data-binding. Your situation is not exactly the same but it's similar enough.

[BrailleSchool]

OK, I tried it again and it still isn't working for me.

Argument Exception
These columns don't currently have unique values

The error happens on the Relations.Add line.

'Create a new relationship between the two DataTables in the DataSet.
                ClientInfoDS.Relations.Add("Phone Numbers", _
                                           ClientInfoNamePhoneDT.Columns(TableName1_PKFKColumn), _
                                           ClientPhoneNumberTypeDT.Columns(TableName2_PKColumn))

My current code is:

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database for existing clients based on name.
        'If multiple phone numbers exist for the client, show a form to be able to select a phone number.
        'If single phone number exists, just populate the form.
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            Dim TableName1 As String = "ClientInformation"
            Dim TableName1_PKFKColumn As String = "PhoneNumberTypeID_PKFK"
            Dim TableName1_ColumnName As String = "ClientName"
            Dim TableName1_ColumnName2 As String = "ClientPhoneNumber"
            Dim TableName2 As String = "PhoneNumber_Type"
            Dim TableName2_PKColumn As String = "PhoneNumberTypeID_PK"
            Dim TableName2_ColumnName As String = "PhoneNumberTypeName"
 
            Try
                'Open the database connection.
                SetDatabaseConnection.Open()
 
                'Create a new DataSet to hold two DataTables.
                Dim ClientInfoDS As DataSet = New DataSet()
 
                'Set the locale of the DataSet.
                ClientInfoDS.Locale = CurrentCulture
 
                'Create two DataTables for each database table.
                Dim ClientInfoNamePhoneDT As DataTable = New DataTable("ClientInfoNamePhone")
                Dim ClientPhoneNumberTypeDT As DataTable = New DataTable("ClientPhoneNumberType")
 
                'Set the locale of the DataTables.
                ClientInfoNamePhoneDT.Locale = CurrentCulture
                ClientPhoneNumberTypeDT.Locale = CurrentCulture
 
                'Add some columns to the DataTables.
                With ClientInfoNamePhoneDT.Columns
                    .Add(TableName1_ColumnName, GetType(String))
                    .Add(TableName1_ColumnName2, GetType(String))
                    .Add(TableName1_PKFKColumn, GetType(Integer))
                End With
 
                With ClientPhoneNumberTypeDT.Columns
                    .Add(TableName2_PKColumn, GetType(Integer))
                    .Add(TableName2_ColumnName, GetType(String))
                End With
 
                'Create two SQLCommands to get the information from the database.
                Dim ClientInfoNamePhoneCommand As SqlCommand = New SqlCommand
                Dim ClientPhoneNumberTypeCommand As SqlCommand = New SqlCommand
 
                'Clear the dataset of previously obtained information.
                ClientInfoDS.Clear()
 
                'Define the information from the SQLCommands.
                With ClientInfoNamePhoneCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & TableName1_PKFKColumn & "," _
                                   & TableName1_ColumnName & "," _
                                   & TableName1_ColumnName2 & _
                                   " FROM " & TableName1
                    .ExecuteNonQuery()
                End With
 
                With ClientPhoneNumberTypeCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & TableName2_PKColumn & "," _
                                   & TableName2_ColumnName & _
                                   " FROM " & TableName2
                    .ExecuteNonQuery()
                End With
 
                'Create two DataAdapters so that we can fill the DataTables with information.
                Dim ClientInfoNamePhoneDA As SqlDataAdapter = New SqlDataAdapter(ClientInfoNamePhoneCommand)
                Dim ClientPhoneNumberTypeDA As SqlDataAdapter = New SqlDataAdapter(ClientPhoneNumberTypeCommand)
 
                'Fill the DataAdapters.
                ClientInfoNamePhoneDA.Fill(ClientInfoNamePhoneDT)
                ClientPhoneNumberTypeDA.Fill(ClientPhoneNumberTypeDT)
 
                'Now add the DataTables to the DataSet.
                With ClientInfoDS
                    .Tables.Add(ClientInfoNamePhoneDT)
                    .Tables.Add(ClientPhoneNumberTypeDT)
                End With
 
                'Create a new relationship between the two DataTables in the DataSet.
                ClientInfoDS.Relations.Add("Phone Numbers", _
                                           ClientInfoNamePhoneDT.Columns(TableName1_PKFKColumn), _
                                           ClientPhoneNumberTypeDT.Columns(TableName2_PKColumn))
 
                'Now create two DataRows for the records.
                Dim ParentDR, ChildDR As DataRow
 
                'Loop through the records.
                For Each ParentDR In ClientInfoDS.Tables(TableName1).Rows
                    For Each ChildDR In ParentDR.GetChildRows(TableName2)
                        'Check to see if there are actually records available.
                        If ClientInfoDS.Tables(TableName1).Rows.Count > 0 Then
                            'Records available.
                            ParentDR = ClientInfoDS.Tables(TableName1).Rows.Find(cboClientName.Text.Trim)
 
                            Select Case ClientInfoDS.Tables(TableName1).Rows.Count
                                Case 1 'One record only.
                                    'Now if there is only one phone number returned, populate the other controls.
                                    'Populate the phone number field.
                                    mtbClientPhoneNumber.Text = ParentDR(1).ToString
                                    cboPhoneNumberType.Text = ChildDR(2).ToString
                                Case Else 'Multiple records returned.
                                    'Show a form for the end-user to choose the phone number and phone number type they want.
 
                            End Select
                        Else
                            'Nothing returned.
                            'Focus the phone number field.
                            mtbClientPhoneNumber.Focus()
                        End If
                    Next ChildDR
                Next ParentDR
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error retrieving data from the databse." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As InvalidOperationException
                MessageBox.Show(ex.Message, _
                                "Invalid Operation Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As NullReferenceException
                MessageBox.Show(ex.Message, _
                                "Null Reference Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As ArgumentException
                MessageBox.Show(ex.Message, _
                                "Argument Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

Does this mean that the column names can't be the same names as in the database?

[jmcilhinney]

Refer to post #4.

[BrailleSchool]

Refer to post #4.

I switched them and then got:

Argument Exception
'column' argument cannot be null.
Parameter name: column

Happens on the same line each time. I the put a break point on that offending line and it shows the right values. This is starting to frustrate me

[BrailleSchool]

OK, I have been trying to work through this and now I do not get any errors and nothing happens when the cursor loses focus in the control.

My code is as follows:

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database for existing clients based on name.
        'If multiple phone numbers exist for the client, show a form to be able to select a phone number.
        'If single phone number exists, just populate the form.
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            Dim TableName1 As String = "ClientInformation"
            Dim TableName1_PKFKColumn As String = "PhoneNumberTypeID_PKFK"
            Dim TableName1_ColumnName As String = "ClientName"
            Dim TableName1_ColumnName2 As String = "ClientPhoneNumber"
            Dim TableName2 As String = "PhoneNumber_Type"
            Dim TableName2_PKColumn As String = "PhoneNumberTypeID_PK"
            Dim TableName2_ColumnName As String = "PhoneNumberTypeName"
 
            Try
                'Open the database connection.
                SetDatabaseConnection.Open()
 
                'Create a new DataSet to hold two DataTables.
                Dim ClientInfoDS As DataSet = New DataSet()
 
                'Set the locale of the DataSet.
                ClientInfoDS.Locale = CurrentCulture
 
                'Create two DataTables for each database table.
                Dim ClientInfoNamePhoneDT As DataTable = New DataTable(TableName1)
                Dim ClientPhoneNumberTypeDT As DataTable = New DataTable(TableName2)
 
                'Set the locale of the DataTables.
                ClientInfoNamePhoneDT.Locale = CurrentCulture
                ClientPhoneNumberTypeDT.Locale = CurrentCulture
 
                'Add some columns to the DataTables.
                With ClientInfoNamePhoneDT.Columns
                    .Add(TableName1_ColumnName, GetType(String))
                    .Add(TableName1_ColumnName2, GetType(String))
                    .Add(TableName1_PKFKColumn, GetType(Integer))
                End With
 
                With ClientPhoneNumberTypeDT.Columns
                    .Add(TableName2_PKColumn, GetType(Integer))
                    .Add(TableName2_ColumnName, GetType(String))
                End With
 
                'Create two SQLCommands to get the information from the database.
                Dim ClientInfoNamePhoneCommand As SqlCommand = New SqlCommand
                Dim ClientPhoneNumberTypeCommand As SqlCommand = New SqlCommand
 
                'Clear the dataset of previously obtained information.
                ClientInfoDS.Clear()
 
                'Define the information from the SQLCommands.
                With ClientInfoNamePhoneCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & TableName1_PKFKColumn & "," _
                                   & TableName1_ColumnName & "," _
                                   & TableName1_ColumnName2 & _
                                   " FROM " & TableName1
                    .ExecuteNonQuery()
                End With
 
                With ClientPhoneNumberTypeCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & TableName2_PKColumn & "," _
                                   & TableName2_ColumnName & _
                                   " FROM " & TableName2
                    .ExecuteNonQuery()
                End With
 
                'Create two DataAdapters so that we can fill the DataTables with information.
                Dim ClientInfoNamePhoneDA As SqlDataAdapter = New SqlDataAdapter(ClientInfoNamePhoneCommand)
                Dim ClientPhoneNumberTypeDA As SqlDataAdapter = New SqlDataAdapter(ClientPhoneNumberTypeCommand)
 
                'Fill the DataAdapters.
                ClientInfoNamePhoneDA.Fill(ClientInfoNamePhoneDT)
                ClientPhoneNumberTypeDA.Fill(ClientPhoneNumberTypeDT)
 
                'Now add the DataTables to the DataSet.
                With ClientInfoDS
                    .Tables.Add(ClientInfoNamePhoneDT)
                    .Tables.Add(ClientPhoneNumberTypeDT)
                End With
 
                'Create a new relationship between the two DataTables in the DataSet.
                Dim ClientInfoDRe As DataRelation
                Dim ParentTable, ChildTable As DataColumn
 
                With ClientInfoDS
                    ParentTable = .Tables(TableName2).Columns(TableName2_PKColumn) 'PK (PhoneNumber_Type)
                    ChildTable = .Tables(TableName1).Columns(TableName1_PKFKColumn) 'PKFK (ClientInformation)
                    ClientInfoDRe = New DataRelation("Phone Numbers", ParentTable, ChildTable)
                    .Relations.Add(ClientInfoDRe)
                End With
 
                'Now create two DataRows for the records.
                Dim ParentDR, ChildDR As DataRow
 
                'Loop through the records.
                For Each ParentDR In ClientInfoDS.Tables(TableName2).Rows
                    For Each ChildDR In ParentDR.GetChildRows(TableName1)
                        'Check to see if there are actually records available.
                        If ClientInfoDS.Tables(TableName2).Rows.Count > 0 Then
                            'Records available.
                            ChildDR = ClientInfoDS.Tables(TableName1).Rows.Find(cboClientName.Text.Trim)
 
                            Select Case ClientInfoDS.Tables(TableName1).Rows.Count
                                Case 1 'One record only.
                                    'Now if there is only one phone number returned, populate the other controls.
                                    'Populate the phone number field.
                                    mtbClientPhoneNumber.Text = ChildDR(TableName1_ColumnName2).ToString
                                    cboPhoneNumberType.Text = ParentDR(TableName2_ColumnName).ToString
                                Case Else 'Multiple records returned.
                                    'Show a form for the end-user to choose the phone number and phone number type they want.
 
                            End Select
                        Else
                            'Nothing returned.
                            'Focus the phone number field.
                            mtbClientPhoneNumber.Focus()
                        End If
                    Next ChildDR
                Next ParentDR
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error retrieving data from the databse." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As InvalidOperationException
                MessageBox.Show(ex.Message, _
                                "Invalid Operation Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As NullReferenceException
                MessageBox.Show(ex.Message, _
                                "Null Reference Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As ArgumentException
                MessageBox.Show(ex.Message, _
                                "Argument Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

[BrailleSchool]

OK, I am going insane and will need a straight jacket before long. I am making progress though. The new error message I am getting is:

MissingPrimaryKeyException
Table doesn't have a primary key.

The thing is, BOTH tables have a primary key and I have added them to the datatables. The line the error is thrown on is:

ChildDR = ClientInfoDS.Tables(ClientInformationTable).Rows.Find(cboClientName.Text.Trim)

[BrailleSchool]

My code:

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database for existing clients based on name.
        'If multiple phone numbers exist for the client, show a form to be able to select a phone number.
        'If single phone number exists, just populate the form.
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            Dim ClientInformationTable As String = "ClientInformation"
            Dim ClientInformationTable_PKColumn As String = "ClientInfoID_PK"
            Dim ClientInformationTable_PKFKColumn As String = "PhoneNumberTypeID_PKFK"
            Dim ClientInformationTable_ClientNameColumn As String = "ClientName"
            Dim ClientInformationTable_ClientPhoneNumberColumn As String = "ClientPhoneNumber"
            Dim PhoneNumberTypeTable As String = "PhoneNumber_Type"
            Dim PhoneNumberTypeTable_PKColumn As String = "PhoneNumberTypeID_PK"
            Dim PhoneNumberTypeTable_PhoneNumberTypeNameColumn As String = "PhoneNumberTypeName"
 
            Try
                'Open the database connection.
                SetDatabaseConnection.Open()
 
                'Create a new DataSet to hold two DataTables.
                Dim ClientInfoDS As DataSet = New DataSet()
 
                'Set the locale of the DataSet.
                ClientInfoDS.Locale = CurrentCulture
 
                'Create two DataTables for each database table.
                Dim ClientInfoNamePhoneDT As DataTable = New DataTable(ClientInformationTable)
                Dim ClientPhoneNumberTypeDT As DataTable = New DataTable(PhoneNumberTypeTable)
 
                'Set the locale of the DataTables.
                ClientInfoNamePhoneDT.Locale = CurrentCulture
                ClientPhoneNumberTypeDT.Locale = CurrentCulture
 
                'Add some columns to the DataTables.
                With ClientInfoNamePhoneDT.Columns
                    .Add(ClientInformationTable_PKColumn, GetType(Integer))
                    .Add(ClientInformationTable_ClientNameColumn, GetType(String))
                    .Add(ClientInformationTable_ClientPhoneNumberColumn, GetType(String))
                    .Add(ClientInformationTable_PKFKColumn, GetType(Integer))
                End With
 
                With ClientPhoneNumberTypeDT.Columns
                    .Add(PhoneNumberTypeTable_PKColumn, GetType(Integer))
                    .Add(PhoneNumberTypeTable_PhoneNumberTypeNameColumn, GetType(String))
                End With
 
                'Create two SQLCommands to get the information from the database.
                Dim ClientInfoNamePhoneCommand As SqlCommand = New SqlCommand
                Dim ClientPhoneNumberTypeCommand As SqlCommand = New SqlCommand
 
                'Clear the dataset of previously obtained information.
                ClientInfoDS.Clear()
 
                'Define the information from the SQLCommands.
                With ClientInfoNamePhoneCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & ClientInformationTable_PKColumn & "," _
                                   & ClientInformationTable_PKFKColumn & "," _
                                   & ClientInformationTable_ClientNameColumn & "," _
                                   & ClientInformationTable_ClientPhoneNumberColumn & _
                                   " FROM " & ClientInformationTable
                    .ExecuteNonQuery()
                End With
 
                With ClientPhoneNumberTypeCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT " & PhoneNumberTypeTable_PKColumn & "," _
                                   & PhoneNumberTypeTable_PhoneNumberTypeNameColumn & _
                                   " FROM " & PhoneNumberTypeTable
                    .ExecuteNonQuery()
                End With
 
                'Create two DataAdapters so that we can fill the DataTables with information.
                Dim ClientInfoNamePhoneDA As SqlDataAdapter = New SqlDataAdapter(ClientInfoNamePhoneCommand)
                Dim ClientPhoneNumberTypeDA As SqlDataAdapter = New SqlDataAdapter(ClientPhoneNumberTypeCommand)
 
                'Fill the DataAdapters.
                ClientInfoNamePhoneDA.Fill(ClientInfoNamePhoneDT)
                ClientPhoneNumberTypeDA.Fill(ClientPhoneNumberTypeDT)
 
                'Now add the DataTables to the DataSet.
                With ClientInfoDS
                    .Tables.Add(ClientInfoNamePhoneDT)
                    .Tables.Add(ClientPhoneNumberTypeDT)
                End With
 
                'Create a new relationship between the two DataTables in the DataSet.
                Dim ClientInfoDRe As DataRelation
                Dim ParentTable, ChildTable As DataColumn
 
                With ClientInfoDS
                    ParentTable = .Tables(PhoneNumberTypeTable).Columns(PhoneNumberTypeTable_PKColumn) 'PK (PhoneNumber_Type)
                    ChildTable = .Tables(ClientInformationTable).Columns(ClientInformationTable_PKFKColumn) 'PKFK (ClientInformation)
                    ClientInfoDRe = New DataRelation("Phone Numbers", ParentTable, ChildTable)
                    .Relations.Add(ClientInfoDRe)
                End With
 
                'Now create two DataRows for the records.
                Dim ParentDR, ChildDR As DataRow
 
                'Loop through the records.
                For Each ParentDR In ClientInfoDS.Tables(PhoneNumberTypeTable).Rows
                    For Each ChildDR In ParentDR.GetChildRows(ClientInfoDRe)
                        'Check to see if there are actually records available.
                        If ClientInfoDS.Tables(PhoneNumberTypeTable).Rows.Count > 0 Then
                            'Records available.
                            ChildDR = ClientInfoDS.Tables(ClientInformationTable).Rows.Find(cboClientName.Text.Trim)
 
                            Select Case ClientInfoDS.Tables(ClientInformationTable).Rows.Count
                                Case 1 'One record only.
                                    'Now if there is only one phone number returned, populate the other controls.
                                    'Populate the phone number field.
                                    mtbClientPhoneNumber.Text = ChildDR(ClientInformationTable_ClientPhoneNumberColumn).ToString
                                    cboPhoneNumberType.Text = ParentDR(PhoneNumberTypeTable_PhoneNumberTypeNameColumn).ToString
                                Case Else 'Multiple records returned.
                                    'Show a form for the end-user to choose the phone number and phone number type they want.
 
                            End Select
                        Else
                            'Nothing returned.
                            'Focus the phone number field.
                            mtbClientPhoneNumber.Focus()
                        End If
                    Next ChildDR
                Next ParentDR
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error retrieving data from the databse." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As InvalidOperationException
                MessageBox.Show(ex.Message, _
                                "Invalid Operation Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As NullReferenceException
                MessageBox.Show(ex.Message, _
                                "Null Reference Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            Catch ex As ArgumentException
                MessageBox.Show(ex.Message, _
                                "Argument Exception.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

[BrailleSchool]

OK, I got rid of the error by changing my code but as soon as the cursor has left the control (lost focus), still nothing happens and no errors!!!!

I changed my code to reflect the following:

'Add some columns to the DataTables.
                With ClientInfoNamePhoneDT
                    .Columns.Add(New DataColumn(ClientInformationTable_PKColumn, GetType(Integer)))
                    .Columns.Add(New DataColumn(ClientInformationTable_ClientNameColumn, GetType(String)))
                    .Columns.Add(New DataColumn(ClientInformationTable_ClientPhoneNumberColumn, GetType(String)))
                    .Columns.Add(New DataColumn(ClientInformationTable_PKFKColumn, GetType(Integer)))
 
                    'Declare a primary key column for the table.
                    Dim PrimaryKey(1) As DataColumn
                    PrimaryKey(1) = .Columns(ClientInformationTable_PKColumn)
                    .PrimaryKey = PrimaryKey
                End With
 
                With ClientPhoneNumberTypeDT
                    .Columns.Add(New DataColumn(PhoneNumberTypeTable_PKColumn, GetType(Integer)))
                    .Columns.Add(New DataColumn(PhoneNumberTypeTable_PhoneNumberTypeNameColumn, GetType(String)))
 
                    'Declare a primary key column for the table.
                    Dim PrimaryKey(1) As DataColumn
                    PrimaryKey(1) = .Columns(PhoneNumberTypeTable_PKColumn)
                    .PrimaryKey = PrimaryKey
                End With

It shouldn't be this difficult to do something simple! Definitely shouldn't have to write 186 lines of useless code to do a simple task!

[jmcilhinney]

There's a lot going wrong here. For one thing, I don't understand why you would be building the whole DataSet on the LostFocus event of a ComboBox. You really shouldn't be handling LostFocus at all, but surely you want the DataSet populated before that regardless.

Please provide a clear, step by step explanation of what you're trying to accomplish from a functionality point of view, not code.

[BrailleSchool]

There's a lot going wrong here. For one thing, I don't understand why you would be building the whole DataSet on the LostFocus event of a ComboBox. You really shouldn't be handling LostFocus at all, but surely you want the DataSet populated before that regardless.

Please provide a clear, step by step explanation of what you're trying to accomplish from a functionality point of view, not code.

There definitely has to be an easier way but not sure of that way. The form in question has a combobox that is editable (can type in it) and it also is autocomplete suggest/append. The client names are populated in this combobox from the ClientInfo database table. As the end user types, the names are suggested. there might even be times that the end user keeps typing and the client name not be in the database.

If the name selected by the end user is in the database and the cursor loses focus from this combobox, i am looking to check to see how many phone numbers this client name has. If there is only one phone number, the phone number and phone number type is auto populated in the other controls (maskedtextbox) and a non-editable combobox. If there are more than one phone number for this particular client, then a form comes up with a list of their phone numbers for the end user to choose from. Once chosen, the selection is defaulted. What I am doing with those datasets, datatables, relations and stuff is pretty interesting, even though very frustrating, but I think it's doing too much for a simple task.

The phone number type is in another table with a FK relationship.

[BrailleSchool]

OK, I am making some progress. I am able to populate the MaskedTextBox for the phone number based on the Client Name. However, when trying to get the Phone Number Type from another table, I get an error. Looks like I still have to go the one DataSet, two DataTables and a DataRelation route. Ugh! I did have two tables previously but had to restructure to include a third because of name duplication.

I am told an instance is not set for this line:

cboPhoneNumberType.Text = CStr(.Tables("ClientInformation_PhoneNumberType").Rows(0).Item("PhoneNumberTypeName"))

Code:

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database to see if the client name exists.
        'If the client name exists, check to see how many phone numbers the client has.
        'If there is only one phone number, populate the MaskedTextBox and ComboBox.
        'If there are multiple phone numbers, show a form for the end user to choose the phone number
        'they want.
        Using SetDatabaseConnection As SqlConnection = New SqlConnection(GetDatabaseConnectionString)
            Try
                'Open the connection to the database.
                SetDatabaseConnection.Open()
 
                'Create a dataset.
                Dim ClientInfoDS As DataSet = New DataSet
 
                'Create a new SQLCommand.
                Dim MySQLCommand As SqlCommand = New SqlCommand
 
                With MySQLCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT ClientInformation.ClientInfoID_PK, " & _
                                   "ClientInformation.ClientName, " & _
                                   "ClientInformation_PhoneNumbers.ClientPhoneNumber, " & _
                                   "ClientInformation_PhoneNumbers.ClientInfoID_PKFK, " & _
                                   "ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK, " & _
                                   "ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK, " & _
                                   "ClientInformation_PhoneNumberType.PhoneNumberTypeName " & _
                                   "FROM ClientInformation " & _
                                   "INNER JOIN ClientInformation_PhoneNumbers " & _
                                   "ON ClientInformation.ClientInfoID_PK = ClientInformation_PhoneNumbers.ClientInfoID_PKFK " & _
                                   "INNER JOIN ClientInformation_PhoneNumberType " & _
                                   "ON ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK = ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK " & _
                                   "WHERE ClientInformation.ClientName =" & "'" & cboClientName.Text.Trim & "'"
                    .ExecuteNonQuery()
                End With
 
                'Create a DataAdapter.
                Dim ClientInfoDA As SqlDataAdapter = New SqlDataAdapter(MySQLCommand.CommandText, _
                                                                        SetDatabaseConnection)
 
 
                'Clear the DataSet prior to doing anything.
                ClientInfoDS.Clear()
 
                'Fill the DataSet with the information found.
                ClientInfoDA.Fill(ClientInfoDS, "ClientInformation_PhoneNumbers")
 
                'Check to see if any records were populated within the DataSet.
                With ClientInfoDS
                    If .Tables("ClientInformation_PhoneNumbers").Rows.Count > 0 Then
                        'Records were returned.
                        'Lets see how many records.  If a single record or multiple.
                        Select Case .Tables("ClientInformation_PhoneNumbers").Rows.Count
                            Case 1 'Single record.
                                'Populate the phone number into the MaskedTextBox.
                                mtbClientPhoneNumber.Text = CStr(.Tables("ClientInformation_PhoneNumbers").Rows(0).Item("ClientPhoneNumber"))
 
                                'Populate a default selection for the phone number type.
                                cboPhoneNumberType.Text = CStr(.Tables("ClientInformation_PhoneNumberType").Rows(0).Item("PhoneNumberTypeName"))
                            Case Else 'Multiple records.
                                'Show a form for the end user to be able to select the phone number they want
                                'to use.
                                'TODO
                        End Select
                    Else
                        'Records were not returned.
                        'Focus the phone number field.
                        mtbClientPhoneNumber.Focus()
                    End If
                End With
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error getting data from the database. The error is:" & vbNewLine & _
                                ex.Message, _
                                "Error Retrieving Data.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Information, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub

Would my assumption be correct?

[BrailleSchool]

OK, I went slow with my thinking and I finally did it! I have successfully achieved my goal with the use of a DataSet, 2 DataTables and a DataRelation. First time ever working with this. What a trip!

The code is not fully completed but I am going to include it here in the event anyone in the future needs a reference point.

Private Sub cboClientName_LostFocus(ByVal sender As Object, _
                                        ByVal e As System.EventArgs) Handles cboClientName.LostFocus
 
        'Check the backend database to see if the client name exists.
        'If the client name exists, check to see how many phone numbers the client has.
        'If there is only one phone number, populate the MaskedTextBox and ComboBox.
        'If there are multiple phone numbers, show a form for the end user to choose the phone number
        'they want.
        Using SetDatabaseConnection As SqlConnection = New SqlConnection(GetDatabaseConnectionString)
            Try
                'Open the connection to the database.
                SetDatabaseConnection.Open()
 
                'Create a dataset.
                Dim ClientInfoDS As DataSet = New DataSet
 
                'Create two DataTables.
                Dim ClientPhoneDT As DataTable = New DataTable("ClientInformation_PhoneNumbers")
                Dim ClientPhoneTypeDT As DataTable = New DataTable("ClientInformation_PhoneNumberType")
 
                'Create the columns for the DataTables.
                With ClientPhoneDT
                    .Columns.Add(New DataColumn("PhoneNumberID_PK", GetType(Integer)))
                    .Columns.Add(New DataColumn("ClientPhoneNumber", GetType(String)))
                    .Columns.Add(New DataColumn("PhoneNumberTypeID_PKFK", GetType(Integer)))
                    .Columns.Add(New DataColumn("ClientInfoID_PKFK", GetType(Integer)))
 
                    'Set the primary key column.
                    Dim PrimaryKey(1) As DataColumn
                    PrimaryKey(1) = .Columns("PhoneNumberID_PK")
                    .PrimaryKey = PrimaryKey
                End With
 
                With ClientPhoneTypeDT
                    .Columns.Add(New DataColumn("PhoneNumberTypeID_PK", GetType(Integer)))
                    .Columns.Add(New DataColumn("PhoneNumberTypeName", GetType(String)))
 
                    'Set the primary key column.
                    Dim PrimaryKey(1) As DataColumn
                    PrimaryKey(1) = .Columns("PhoneNumberTypeID_PK")
                    .PrimaryKey = PrimaryKey
                End With
 
                'Add the DataTables to the DataSet.
                With ClientInfoDS
                    .Tables.Add(ClientPhoneDT)
                    .Tables.Add(ClientPhoneTypeDT)
                End With
 
                'Create a DataRelation for the two tables.
                Dim ClientInfoDRe As DataRelation
 
                'Create a new SQLCommand.
                Dim MySQLCommand As SqlCommand = New SqlCommand
 
                'Set the attributes for the SQLCommand.
                With MySQLCommand
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.Text
                    .CommandText = "SELECT ClientInformation.ClientInfoID_PK, " & _
                                   "ClientInformation.ClientName, " & _
                                   "ClientInformation_PhoneNumbers.ClientPhoneNumber, " & _
                                   "ClientInformation_PhoneNumbers.ClientInfoID_PKFK, " & _
                                   "ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK, " & _
                                   "ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK, " & _
                                   "ClientInformation_PhoneNumberType.PhoneNumberTypeName " & _
                                   "FROM ClientInformation " & _
                                   "INNER JOIN ClientInformation_PhoneNumbers " & _
                                   "ON ClientInformation.ClientInfoID_PK = ClientInformation_PhoneNumbers.ClientInfoID_PKFK " & _
                                   "INNER JOIN ClientInformation_PhoneNumberType " & _
                                   "ON ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK = ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK " & _
                                   "WHERE ClientInformation.ClientName =" & "'" & cboClientName.Text.Trim & "'"
                    .ExecuteNonQuery()
                End With
 
                'Create a DataAdapter.
                Dim ClientInfoDA As SqlDataAdapter = New SqlDataAdapter(MySQLCommand.CommandText, _
                                                                        SetDatabaseConnection)
 
 
                'Clear the DataSet prior to doing anything.
                ClientInfoDS.Clear()
 
                'Fill the DataTables with the information found.
                With ClientInfoDA
                    .Fill(ClientPhoneDT)
                    .Fill(ClientPhoneTypeDT)
                End With
 
                'Create two DataColumns for the DataRelation relationship.
                Dim ParentTableDC, ChildTableDC As DataColumn
 
                With ClientInfoDS
                    'Create the DataRelation between the PK/FK columns.
                    ParentTableDC = .Tables("ClientInformation_PhoneNumberType").Columns("PhoneNumberTypeID_PK")
                    ChildTableDC = .Tables("ClientInformation_PhoneNumbers").Columns("PhoneNumberTypeID_PKFK")
 
                    'Create a new DataRelation.
                    ClientInfoDRe = New DataRelation("Phone Numbers", ParentTableDC, ChildTableDC)
 
                    'Add the DataRelation to the DataSet housing the two DataTables.
                    .Relations.Add(ClientInfoDRe)
                End With
 
                'Create two DataRows.  One for the parent and another for the child.
                Dim ParentTableDR, ChildTableDR As DataRow
 
                'Now the fun begins, let's go ahead and get the information from the DataTables.
                'If there is one phone number, auto populate the MaskedTextBox with the phone number and ComboBox with the phone number type.
                'If there are multiple phone numbers, show a form to allow the end user to select the desired phone number.
                'If there are no records returned, focus the MaskedTextBox.
                For Each ParentTableDR In ClientInfoDS.Tables("ClientInformation_PhoneNumberType").Rows 'PK.
                    For Each ChildTableDR In ParentTableDR.GetChildRows(ClientInfoDRe) 'FK.
                        'Now see how many records were returned.
                        Select Case ClientInfoDS.Tables("ClientInformation_PhoneNumberType").Rows.Count
                            Case 0 'No records.
                                'No records returned, go ahead and focus the MaskedTextBox.
                                mtbClientPhoneNumber.Focus()
 
                                'Go no further because nothing returned.
                                Exit For
                            Case 1 'One record.
                                'Yippee!  A record was returned.
                                'Auto populate the phone number in the MaskedTextBox (Phone Number).
                                'Auto populate a default value in the ComboBox (Phone Number Type).
                                mtbClientPhoneNumber.Text = ChildTableDR("ClientPhoneNumber").ToString
                                cboPhoneNumberType.Text = ParentTableDR("PhoneNumberTypeName").ToString
 
                                'Focus the Partner Name ComboBox.
                                cboPartnerName.Focus()
                            Case Else 'Multiple records.
                                'Show a form to the end user, so that s/he can select from a list of available phone numbers.
                                'TODO
                        End Select
                    Next ChildTableDR
                Next ParentTableDR
 
                'Close the database connection.
                SetDatabaseConnection.Close()
            Catch ex As SqlException
                MessageBox.Show("There was an error getting data from the database. The error is:" & vbNewLine & _
                                ex.Message, _
                                "Error Retrieving Data.", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Information, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, _
                                False)
            End Try
        End Using
    End Sub