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
JGutchesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
The easiest way to combine the data into a single field is to add a column to the datatable, and use this new column as the data source for the combobox:
dataset111.tables("PatientInformation").columns.add("PatientName", Type.GetType("System.String"), "FirstName + ' ' + MidInitial + ' ' + LastName")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IlianamCommented:
You can also have a variable lets say
Dim fullname as string

fullname=Trim(Convert.ToString((dataset111.Tables(0).Rows(0)("PatientLastName"))))
fullname=fullname +" "
fullname=Trim(Convert.ToString((dataset111.Tables(0).Rows(0)("PatientFirstName"))))
0
JGutchesAuthor Commented:
Thank you both.  Both answers help me with different aspects of my problem.  I appreciate the prompt and accurate answers.
Your adherent,
Jason
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

mani_saiCommented:
First Step:

Getting information from database and binding it to Your Combo Box:

Private Function FillComboBox()

        'Getting Connection Information
        Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConnection As New SqlConnection(strConnection)

        '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(CommandBehavior.CloseConnection)
        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


---------------------------------------------------------------------------------------------------
0
mani_saiCommented:
Second Step:

Getting the Other information from Database for the selected names:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        Dim strPatientID As String

        Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
        Dim objConnection As New SqlConnection(strConnection)

        Dim dr As SqlDataReader

        If ComboBox1.Items.Count <> 0 Then
            strPatientID = ComboBox1.SelectedItem.Value   '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(CommandBehavior.CloseConnection)

         '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.Close()
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Close()
        objConnection = Nothing


    End Sub

Good luck
0
mani_saiCommented:
Also for the ComboBox1_SelectedIndexChanged event to fire

you have to set ComboBox1 autopostback =  true.


Good luck.
0
JGutchesAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.