MS Visual Studio 2017 and MS Access: Retrieve records based on query parameters

I'm using MS Visual Studio 2017, VB.NET,  and connecting to an MS Access database.
I can write to the database (see code below), but I can't find online examples or instructions how to query the database (i.e., I type in a DOB and it returns all the people in the database with that DOB).

The DB has the following tables/fields:
Table = "patient"
Fields = ptid, fname, lname, dob

I want to type a dob into the text field on my form, press a button, and have it return the fname and lname of any record that has that DOB.  Thanks!


************This Works to write to the database.****************
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\tleng\source\repos\WindowsApp3\WindowsApp3\db1.accdb"

        Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"

        Using conn As New OleDb.OleDbConnection(con.ConnectionString)

            Using cmd As New OleDb.OleDbCommand(SqlString, con)

                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("column", IDTextBox.Text)
                cmd.Parameters.AddWithValue("column", FnameTextBox.Text)
                cmd.Parameters.AddWithValue("column", LnameTextBox.Text)
                cmd.Parameters.AddWithValue("column", DobDateTimePicker.Text)

                con.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub
Maslow RaffoAsked:
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.

Fernando SotoRetiredCommented:
Try changing the code to look like this.
cmd.Parameters.AddWithValue("idtextbox", IDTextBox.Text)
cmd.Parameters.AddWithValue("fnametextbox", FnameTextBox.Text)
cmd.Parameters.AddWithValue("lnametextbox", LnameTextBox.Text)
cmd.Parameters.AddWithValue("dobdatetimepicker", DobDateTimePicker.Text)

Open in new window

Maslow RaffoAuthor Commented:
Do I still need to include the values in the following line if I use your approach?
Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"

Any idea how I can retrieve the information as described in my question?
Fernando SotoRetiredCommented:
Yes, because the cmd.Parameters.AddWithValue method call is creating an association between what is in the SQL statement with the value you want to assign to it.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Maslow RaffoAuthor Commented:
Ok, understood.

Do you know how to retrieve information from the db based on a search for DOB?  So, if I have 3 records as follows:
Name     DOB
Jon T.      03/15/1985
Sue R.     11/07/1990
Mike O.   07/04/1976

I want to type "07/04/1976" in a search field and have it return "Mike O." in a textbox on my form.
Mark EdwardsChief Technology OfficerCommented:
A google search on "use vb.net to retrieve data from an Access database" came up with a LOT of solutions, depending on what approach you're using.  If all else fails, take a look at the google links retrieved and see if you find your answer in there.
Maslow RaffoAuthor Commented:
Yes, I've reviewed close to 20-30 pages from the links and have found--as you mentioned--a lot of different answers, but none that address my question of  using a text field for the search string and having vb.net search the db and return the other columns associated with that row/search string.
Maslow RaffoAuthor Commented:
This is as close as I've gotten, but it's returning the error: System.InvalidCastException  (see attached screen shot)
Fernando SotoRetiredCommented:
What is the DOB data type in the database, is it a DateTime or is it a String data type?
Fernando SotoRetiredCommented:
See if this fits your needs.
'' Where cmd is the OleDbCommand 
cmd.CommandText = "SELECT fname, lname, dob FROM patient WHERE FORMAT(dob, 'Short Date') = DateValue([@SearchDate])"
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@SearchDate", DobDateTimePicker.Text)

Open in new window

Maslow RaffoAuthor Commented:
Date/Time
Fernando SotoRetiredCommented:
See my above post.
Maslow RaffoAuthor Commented:
Just saw your code entry...trying it now.
Maslow RaffoAuthor Commented:
Ok, I created a button and put your code in it (copied below).
Attached you will find the Table View of the DB so you can see how it looks, and the form with the datetimepicker on it so you can see what text is in it.

When I run the code, nothing happens: no error, no change, etc.


   Private Sub btn_getData_Click(sender As Object, e As EventArgs) Handles btn_getData.Click
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\tleng\source\repos\WindowsApp3\WindowsApp3\db1.accdb"
        Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"

        Using conn As New OleDb.OleDbConnection(con.ConnectionString)
            Using cmd As New OleDb.OleDbCommand(SqlString, con)
                cmd.CommandType = CommandType.Text
                '' Where cmd is the OleDbCommand
                cmd.CommandText = "SELECT fname, lname, dob FROM patient WHERE FORMAT(dob, 'Short Date') = DateValue([@SearchDate])"
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@SearchDate", dtp_dob.Text)
                con.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub
Access-DB-Table-View.JPG
frm-with-datetimepicker.JPG
Fernando SotoRetiredCommented:
Hi Maslow;

Can you close this question because the original post has been answered and open a new question with your las post. EE Like only one question per thread. Meanwhile I'll look at your last post and answer in the new question you post.

Thanks.

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
Maslow RaffoAuthor Commented:
This is my original post (copied below, but scroll all the way to the top), which is the question at hand. I can close and resubmit, if that helps.

I'm using MS Visual Studio 2017, VB.NET,  and connecting to an MS Access database.
I can write to the database (see code below), but I can't find online examples or instructions how to query the database (i.e., I type in a DOB and it returns all the people in the database with that DOB).

The DB has the following tables/fields:
Table = "patient"
Fields = ptid, fname, lname, dob

I want to type a dob into the text field on my form, press a button, and have it return the fname and lname of any record that has that DOB.  Thanks!
Fernando SotoRetiredCommented:
OK, in your last post you have this, cmd.ExecuteNonQuery(), which does not return a result set from the database.

What are you using when retrieving data from the database are you using OleDbDataAdapter or are you using a data reader?

Please show code where you are getting data from the database.

You need something like this
Private Sub btn_getData_Click(sender As Object, e As EventArgs) Handles btn_getData.Click
   con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\tleng\source\repos\WindowsApp3\WindowsApp3\db1.accdb"

   Using conn As New OleDb.OleDbConnection(con.ConnectionString)
       Using cmd As New OleDb.OleDbCommand(SqlString, con)
           cmd.CommandText = "SELECT fname, lname, dob FROM patient WHERE FORMAT(dob, 'Short Date') = DateValue([@SearchDate])"
           cmd.CommandType = CommandType.Text
           cmd.Parameters.AddWithValue("@SearchDate", dtp_dob.Text)
           con.Open()
           
		   '' you need to use something to get the data something like the following
		   '' Dim reader = cmd.ExecuteReader()
		   '' then read through the records.
		   
       End Using
   End Using
End Sub

Open in new window

Maslow RaffoAuthor Commented:
Ok, this code below successfully pulls an specific item from my sample database. Let's use this as our starting point. Attached find the database items and the output of the code.

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\sample1.accdb"
        con.Open()
        ShowItems()
    End Sub
    Private Sub ShowItems()
        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

        da = New OleDbDataAdapter("SELECT * FROM phonebooktable", con)
        da.Fill(dt)

        txt_ID.Text = dt.Rows(0).Item(0)
        txt_fullName.Text = dt.Rows(0).Item(1)

        con.Close()
    End Sub
End Class
sample-db.JPG
code-and-output.JPG
Fernando SotoRetiredCommented:
Is the database that you are working with a sample database, one that you can zip it up and upload to where I can download it from. If so please do so.
Maslow RaffoAuthor Commented:
Yes--here it is (attached).
sample1.accdb
Fernando SotoRetiredCommented:
Hi Maslow;

OK, a couple of things. First the DOB field in the database is NOT in DateTime data type but is actually in string format as I asked in the first question. Also querying the DOB field will sometimes return multiple records because two persons can have the same DOB and so you will need to handle that. The below code works with the database you provided.
Imports System.Data.OleDb
Imports System.Text


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    '' Connection string needs to be changed
    Dim dbName = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Working Directory\Forum\SampleDbCode\SampleDbCode\sample1.accdb"""
    '' Find this date
    Dim getDate = "01/01/1970"
    '' Build the resulting output
    Dim fields As New StringBuilder()

    Using cn As New OleDbConnection With {.ConnectionString = dbName}
        Using cmd As New OleDbCommand With {.Connection = cn}
            '' This is different from my previous post
            cmd.CommandText = "SELECT ID, ShrtTextFullName, ShrtTextDOB FROM testTable WHERE ShrtTextDOB = [@SearchDate]"
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@SearchDate", getDate)
            cn.Open()
            '' ExecuteReader returns a DataReader
            Dim reader = cmd.ExecuteReader()
            '' Read one record at a time if no records left to read returns a False
            While reader.Read()
                '' Read each field of the record and assign to the StringBuilder
                For i As Integer = 0 To reader.FieldCount - 1
                    fields.Append(reader.Item(i).ToString())
                    fields.Append(vbTab)
                Next
                '' Get ready for the next record.
                fields.Append(vbNewLine)
            End While
        End Using
    End Using

    '' TextBox1 is set to Multiline = True
    TextBox1.Text = fields.ToString()

End Sub

Open in new window

Maslow RaffoAuthor Commented:
OMG IT WORKED!! THANKS SO MUCH!!

(Yes, the DOB in this sample I set to text, but the DOB in the original we were working before was a DTP)

So happy and grateful for the time you took to get this working--I've been trying to get this to work for 7 hours today--You're the best!
Maslow RaffoAuthor Commented:
Excellent help!
Fernando SotoRetiredCommented:
Not a problem Maslow, glad I was able to help.
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.