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.OL
Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"
Using conn As New OleDb.OleDbConnection(con.
Using cmd As New OleDb.OleDbCommand(SqlStri
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
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.OL
Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"
Using conn As New OleDb.OleDbConnection(con.
Using cmd As New OleDb.OleDbCommand(SqlStri
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Visual Basic.NETMicrosoft AccessMicrosoft Visual Studio
Last Comment
Fernando Soto
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?
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.AddWithValu
ASKER
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.
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.
ASKER
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.
ASKER
This is as close as I've gotten, but it's returning the error: System.InvalidCastExceptio
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)
ASKER
Date/Time
See my above post.
ASKER
Just saw your code entry...trying it now.
ASKER
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.OL
Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"
Using conn As New OleDb.OleDbConnection(con.
Using cmd As New OleDb.OleDbCommand(SqlStri
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.AddWithValu
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Access-DB-Table-View.JPG
frm-with-datetimepicker.JPG
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.OL
Dim SqlString As String = "Insert into [patient] (ptid, fname, lname, dob) Values (idtextbox, fnametextbox, lnametextbox, dobdatetimepicker)"
Using conn As New OleDb.OleDbConnection(con.
Using cmd As New OleDb.OleDbCommand(SqlStri
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.AddWithValu
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Access-DB-Table-View.JPG
frm-with-datetimepicker.JPG
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
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
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
ASKER
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.OL
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
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.OL
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.
ASKER
Yes--here it is (attached).
sample1.accdb
sample1.accdb
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
(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!
ASKER
Excellent help!
Not a problem Maslow, glad I was able to help.
Open in new window