How to access a Database Table from a given Variable

[Rhys_3]

Hi, For a school assenment I was tasked to make a small database solution for a medical clinic. I am wanting to select an access database table after gaining the ID for the table. I will be needing to read and write to this table as well if it makes a difference.

I have been using this code to search for the patient.

Any and all help would be fantasic!

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        'Change the following to your access database location
        dataFile = "C:\Users\BAKER113\Documents\EHRDatabase.accdb" 'Set the file path for the 
        connString = provider & dataFile
        myConnection.ConnectionString = connString

        myConnection.Open() 'Opens Connection to dataase
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [PatientList] WHERE [FirstName] = '" & txtPatFirstName.Text & "' AND [LastName] = '" & txtPatSurname.Text & "' AND [PhoneNumber] = '" & txtPhoneNumber.Text & "'", myConnection)
        'Select from the Patient Table and find the record where the Patient firstname is the same as the entered name and the paitents lastname is the same as the surname entered
        Dim dr As OleDbDataReader = cmd.ExecuteReader

        ' the following variable is hold true if user is found, and false if user is not found
        Dim PatientFound As Boolean = False
        ' the following variables will hold the Patient's first name, last name, Phone Number and ID if found.
        Dim FirstName As String = ""
        Dim LastName As String = ""
        Dim PhoneNumber As String = ""
        Dim PatID As String = ""
        'if found:
        While dr.Read
            PatientFound = True
            FirstName = dr("FirstName").ToString 'save Patients First name to the FirstName variable 
            LastName = dr("LastName").ToString 'save Patients Last name to the LastName variable
            PhoneNumber = dr("PhoneNumber").ToString 'save Patients Phone Number to the PhoneNumber variable
            PatID = dr("ID").ToString 'save Patients ID to the ID variable

        End While

[pty]

I'm not sure what you mean by "I am wanting to select an access database table after gaining the ID for the table."

To find a record with a matching first and last name using SQL you'd do something like

select * from patients where firstname = 'Joe' and lastname = 'Shabadoo';

Nobody in the real world would actually create separate variables for a single record's attributes and populate them manually like that. In Rails, for example, using ActiveRecord to find and modify a record we'd do something like this:

p = Patient.find_by(firstname: 'Joe', lastname: 'Shabadoo')
p.firstname = 'Joseph'
p.save

Simple, elegant and pretty self-explanatory.

vb.net probably has something similar. If it doesn't I'd suggest learning something that does instead.