I'm trying to search a date using DateTimePicker in 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:
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
CodePudding user response:
Use the true date value of the datetime picker:
Dim dt1 As DateTime = DateTimePicker1.Value
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Table1] where [TDateField] = #" & dt1.ToString("yyyy'/'MM'/'dd") & "#", connection)