JGutches
asked on
Pull First name Last name, etc into textbox.
Hello,
I have created a database in Access that has several tables related to Patient Information Tables (Patient_Information, Patient_Primary_Insurance, ect) The primary key is PtID in all related tables. I have forms set up in vb that allow viewing information and adding/appending and delete. I have another form that gathers all information together for billing insurance comapanies. My problem is I have a combobox that is bound to DataSet111 wich hold Patient_Information and shows PatientLastName I would like for the combobox to combine FirstName, MidInitial, and LastName from Patient_Information table and further Once a name is selected Infromation is filled in the form such as address (which is broken down as PateintSteet, PatientCity, etc...) and Primary Insurance information from another tabe (Patient_Primary_Insurance ). I guess at the most basic I need to figure-out how to pull information from related databases and save the information into variables that I can then combine as I need into textboxes etc...
Thanks in Advance,
Jason
I have created a database in Access that has several tables related to Patient Information Tables (Patient_Information, Patient_Primary_Insurance,
Thanks in Advance,
Jason
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First Step:
Getting information from database and binding it to Your Combo Box:
Private Function FillComboBox()
'Getting Connection Information
Dim strConnection As String = ConfigurationSettings.AppS ettings("C onnectionS tring")
Dim objConnection As New SqlConnection(strConnectio n)
'Build Sql Query(modify the sql query with your field names)
Dim strSQL As String = "Select Distinct PtID, PatientFirstName + ' ' + PatientMiddleIntial + ' ' + PatientLastName as PatientName From Patient_Information"
Dim objCommand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
Dim dr As SqlDataReader
dr = objCommand.ExecuteReader(C ommandBeha vior.Close Connection )
ComboBox1.DataTextField = "PatientName" 'Concatinated Name is stored as text to display
ComboBox1.DataValueField = "PtID" 'ID is stored as value for retrival
ComboBox1.DataSource = dr
ComboBox1.DataBind()
'CleanUp Code
dr.Close()
objCommand.Dispose()
objCommand = Nothing
End Function
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Getting information from database and binding it to Your Combo Box:
Private Function FillComboBox()
'Getting Connection Information
Dim strConnection As String = ConfigurationSettings.AppS
Dim objConnection As New SqlConnection(strConnectio
'Build Sql Query(modify the sql query with your field names)
Dim strSQL As String = "Select Distinct PtID, PatientFirstName + ' ' + PatientMiddleIntial + ' ' + PatientLastName as PatientName From Patient_Information"
Dim objCommand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
Dim dr As SqlDataReader
dr = objCommand.ExecuteReader(C
ComboBox1.DataTextField = "PatientName" 'Concatinated Name is stored as text to display
ComboBox1.DataValueField = "PtID" 'ID is stored as value for retrival
ComboBox1.DataSource = dr
ComboBox1.DataBind()
'CleanUp Code
dr.Close()
objCommand.Dispose()
objCommand = Nothing
End Function
--------------------------
Second Step:
Getting the Other information from Database for the selected names:
Private Sub ComboBox1_SelectedIndexCha nged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexCha nged
Dim strPatientID As String
Dim strConnection As String = ConfigurationSettings.AppS ettings("C onnectionS tring")
Dim objConnection As New SqlConnection(strConnectio n)
Dim dr As SqlDataReader
If ComboBox1.Items.Count <> 0 Then
strPatientID = ComboBox1.SelectedItem.Val ue 'Get the Patient ID for the selected patient name
End If
' you may use sql join to get information from different tables for the selected patientID.
Dim strSQL As String = "Select xxx , xxx, xxx, " _
& " Where PtID= '" & Trim(strPatientID ) & "'"
Dim objCommand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
dr = objCommand.ExecuteReader(C ommandBeha vior.Close Connection )
'Retrive the other information through data reader and display it in your form.
If dr.Read Then
txtContact.Text = dr("PatientStreet")
txtContactPhone.Text = dr("PatientCity")
etc etc.....
End If
'CleanUp
dr.Close()
objCommand.Connection.Clos e()
objCommand.Dispose()
objCommand = Nothing
objConnection.Close()
objConnection = Nothing
End Sub
Good luck
Getting the Other information from Database for the selected names:
Private Sub ComboBox1_SelectedIndexCha
Dim strPatientID As String
Dim strConnection As String = ConfigurationSettings.AppS
Dim objConnection As New SqlConnection(strConnectio
Dim dr As SqlDataReader
If ComboBox1.Items.Count <> 0 Then
strPatientID = ComboBox1.SelectedItem.Val
End If
' you may use sql join to get information from different tables for the selected patientID.
Dim strSQL As String = "Select xxx , xxx, xxx, " _
& " Where PtID= '" & Trim(strPatientID ) & "'"
Dim objCommand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
dr = objCommand.ExecuteReader(C
'Retrive the other information through data reader and display it in your form.
If dr.Read Then
txtContact.Text = dr("PatientStreet")
txtContactPhone.Text = dr("PatientCity")
etc etc.....
End If
'CleanUp
dr.Close()
objCommand.Connection.Clos
objCommand.Dispose()
objCommand = Nothing
objConnection.Close()
objConnection = Nothing
End Sub
Good luck
Also for the ComboBox1_SelectedIndexCha nged event to fire
you have to set ComboBox1 autopostback = true.
Good luck.
you have to set ComboBox1 autopostback = true.
Good luck.
ASKER
mani sai,
Your awsome. The datavaluefield set to the PtID is gold I was having problems trying to keep track of what record was being displayed. Thank you for the logical step by step I have learned a great deal from this answer.
Again Thank you,
Jason
Your awsome. The datavaluefield set to the PtID is gold I was having problems trying to keep track of what record was being displayed. Thank you for the logical step by step I have learned a great deal from this answer.
Again Thank you,
Jason
ASKER
Your adherent,
Jason