Results 1 to 5 of 5

Thread: Search by specific date from MS Access database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2021
    Posts
    19

    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: Name:  ds2.jpg
Views: 496
Size:  7.7 KB Name:  ds1.jpg
Views: 438
Size:  19.5 KB

    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

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    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.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2021
    Posts
    19

    Re: Search by specific date from MS Access database

    Quote Originally Posted by Peter Swinkels View Post
    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    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
  •  



Click Here to Expand Forum to Full Width