[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?