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

Maslow Raffo
Maslow Raffo used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
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

Author

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 SotoRetired
Distinguished Expert 2017

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Officer

Commented:
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.

Author

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.

Author

Commented:
This is as close as I've gotten, but it's returning the error: System.InvalidCastException  (see attached screen shot)
Fernando SotoRetired
Distinguished Expert 2017

Commented:
What is the DOB data type in the database, is it a DateTime or is it a String data type?
Fernando SotoRetired
Distinguished Expert 2017

Commented:
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

Author

Commented:
Date/Time
Fernando SotoRetired
Distinguished Expert 2017

Commented:
See my above post.

Author

Commented:
Just saw your code entry...trying it now.

Author

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
Retired
Distinguished Expert 2017
Commented:
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.

Author

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 SotoRetired
Distinguished Expert 2017

Commented:
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

Author

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 SotoRetired
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Yes--here it is (attached).
sample1.accdb
Fernando SotoRetired
Distinguished Expert 2017
Commented:
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

Author

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!

Author

Commented:
Excellent help!
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Not a problem Maslow, glad I was able to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial