We help IT Professionals succeed at work.

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

122 Views
Last Modified: 2018-11-03
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

Fernando SotoRetired
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
CERTIFIED EXPERT
Distinguished Expert 2017

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