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: Attachment 184223 Attachment 184224

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

---

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.

Quote:

---

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.

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

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

---