-
Mar 7th, 2022, 05:16 AM
#1
Thread Starter
Junior Member
Search by specific date from MS Access database
I'm trying to search a date using DateTimePicker from an Access database and have the results show in the DataGridView but it doesn't work. No errors but it just doesn't show the database/results. Here's pictures of the database and the debugged form when it doesn't work in case they are of any use:
and here's my code:
Code:
Imports System.Data.OleDb
Public Class Form10
Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Daily Sales.accdb;")
Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
Dim dt1 As DateTime = DateTime.Parse(DateTimePicker1.Value)
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Table1] where [TDateField] = #" & dt1.ToString("MM/dd/yyyy"), connection)
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
DataGridView2.DataSource = da
connection.Close()
End Sub
Private Sub Form10_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "MM/dd/yyyy"
End Sub
End Class
-
Mar 7th, 2022, 05:37 AM
#2
Re: Search by specific date from MS Access database
Just a quick coding tip:
Code:
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
can be shortened to:
Code:
Dim da As New OleDbDataAdapter With {.SelectCommand = cmd}
Also, I recommend giving controls and forms more meaningful names than Form10.
-
Mar 7th, 2022, 06:16 AM
#3
Thread Starter
Junior Member
Re: Search by specific date from MS Access database
Originally Posted by Peter Swinkels
Just a quick coding tip:
Code:
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
can be shortened to:
Code:
Dim da As New OleDbDataAdapter With {.SelectCommand = cmd}
Also, I recommend giving controls and forms more meaningful names than Form10.
Thanks, will keep that in mind.
-
Mar 7th, 2022, 08:16 AM
#4
Re: Search by specific date from MS Access database
1) Are you sure it's running correctly?
2) Because you should be getting a SQL Syntax error with this line:
Code:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Table1] where [TDateField] = #" & dt1.ToString("MM/dd/yyyy"), connection)
You're missing a # after the date...
Code:
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Table1] where [TDateField] = #" & dt1.ToString("MM/dd/yyyy") & "#", connection)
But you report no errors/exceptions, which tells me the code isn't running the way you think it should.
-tg
-
Mar 8th, 2022, 06:15 AM
#5
Re: Search by specific date from MS Access database
I'd recommend placing all data operations outside of forms, in a class such as shown below. Also, discard the idea of a single connection for all operations, instead create each connection per operation and never use string concatenation for insertion of values, use parameters.
Code:
Public Class DataOperations
Private Shared connectionString As String =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Daily Sales.accdb;"
Public Shared Function LoadByDate(DateValue As Date) As DataTable
Dim dt As New DataTable
'
' Recommend getting out of the habit of SELECT *, instead select
' only the columns needed even if all columns are needed
'
Dim selectStatement = "SELECT * FROM [Table1] WHERE [TDateField] = @DateValue"
Using cn As New OleDbConnection With {.ConnectionString = connectionString}
Using cmd As New OleDbCommand With {.CommandText = selectStatement, .Connection = cn}
cn.Open()
'
' Always use parameters
'
cmd.Parameters.Add("@DateValue", OleDbType.Date).Value = DateValue
dt.Load(cmd.ExecuteReader())
End Using
End Using
Return dt
End Function
End Class
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|