Link to home
Start Free TrialLog in
Avatar of Maslow Raffo
Maslow Raffo

asked on

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
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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

Avatar of Maslow Raffo
Maslow Raffo

ASKER

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?
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.
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.
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.
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.
This is as close as I've gotten, but it's returning the error: System.InvalidCastException  (see attached screen shot)
What is the DOB data type in the database, is it a DateTime or is it a String data type?
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

Date/Time
See my above post.
Just saw your code entry...trying it now.
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
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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

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
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.
Yes--here it is (attached).
sample1.accdb
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
Excellent help!
Not a problem Maslow, glad I was able to help.