[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pull First name Last name, etc into textbox.

Posted on 2004-12-01
7
Medium Priority
?
346 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:JGutches
7 Comments
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 500 total points
ID: 12719856
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
 

Assisted Solution

by:Ilianam
Ilianam earned 500 total points
ID: 12719927
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
 

Author Comment

by:JGutches
ID: 12720178
Thank you both.  Both answers help me with different aspects of my problem.  I appreciate the prompt and accurate answers.
Your adherent,
Jason
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:mani_sai
ID: 12720198
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
 
LVL 8

Expert Comment

by:mani_sai
ID: 12720280
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
 
LVL 8

Expert Comment

by:mani_sai
ID: 12720294
Also for the ComboBox1_SelectedIndexChanged event to fire

you have to set ComboBox1 autopostback =  true.


Good luck.
0
 

Author Comment

by:JGutches
ID: 12724135
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month19 days, 19 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question