OleDbCommand Query Using Parameters Not Quite Working

[contentednova]

Hello,

Running VB.net and using an Access MDB as the backend database. Connection string for database is:

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=db.mdb")

I'm trying to do a select query to get all of the results where StartTime is between two dates. StartTime is a column in the Access DB of type Datetime. It contains both the date and time information, so I'm attempting to use the SQL command DateValue to strip off the time. However, this isn't working when I use parameters with the query.

For example, this doesn't return any results:

        Dim query As String = "SELECT * FROM Shifts WHERE DateValue(StartTime) >= @startperiod AND DateValue(StartTime) <= @endperiod;"

        Dim pStart As New DateTime(2023, 3, 1)
        Dim pEnd As New DateTime(2023, 3, 15)

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

        cmd.Parameters.AddWithValue("@startperiod", pStart)
        cmd.Parameters.AddWithValue("@endperiod", pEnd)

        'run cmd.ExecuteReader, and go through the results, etc

However, if I change the query string to the value of pEnd (without using a parameter), then this successfully returns the results I am looking for:

        Dim query As String = "SELECT * FROM Shifts WHERE DateValue(StartTime) >= @startperiod AND DateValue(StartTime) <= @endperiod;"

        Dim pStart As New DateTime(2023, 3, 1)
        Dim pEnd As New DateTime(2023, 3, 15)

        query = query.Replace("@endperiod", "#" & pEnd.ToShortDateString & "#")

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

        cmd.Parameters.AddWithValue("@startperiod", pStart)

        'run cmd.ExecuteReader, and go through the results, etc

Here is a sample of the database table:


Any ideas what I am doing wrong with the first option?

[contentednova]

I haven't found the reason for the problem yet, but I found a cleaner way to do what I want to do:

SELECT * FROM Shifts WHERE StartTime BETWEEN @startperiod AND @endperiod

I never knew about the BETWEEN command, I guess you learn something new everyday. Still curious why the original method I was using failed though.

[si_the_geek]

A quick web search shows:

The DateValue() function returns a date based on a string.

So what is happening is that the DateTime value is being converted to a String (to be passed to DateValue), and then DateValue converts it back to a Date... which may or may not include a Time component.


It is important to note that when converting a Date/DateTime to or from a String, there can be conversion issues that mean the value gets misinterpreted. This is most likely to mean that the Month and Day values get swapped around (eg: January 3rd gets interpreted as March 1st), and if that is happening it would explain the original issue.



For queries against Access based databases, you can actually use a seemingly strange trick to eliminate the Time portion of a Date/DateTime value, which is to use the Int() function, eg:

SELECT * FROM Shifts WHERE Int(StartTime) BETWEEN @startperiod AND @endperiod

This works because internally Date/DateTime values are stored as numbers (eg: 38549.355), with the integer portion representing the date, and the decimal portion representing the time (0.5 meaning midday).

[contentednova]

Thank you so much. I didn't realize it was converting it to a string and back to a date. I know different regions format dates differently so I was purposely trying to avoid any string representations.

[contentednova]

I think I spoke too soon, this returns nothing:

        Dim query As String = "SELECT * FROM Shifts WHERE Int(StartTime) BETWEEN @startperiod AND @endperiod;"

        Dim pStart As New DateTime(2023, 3, 1)
        Dim pEnd As New DateTime(2023, 3, 15)

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

        cmd.Parameters.AddWithValue("@startperiod", pStart)
        cmd.Parameters.AddWithValue("@endperiod", pEnd))

But if I hardcode the dates, it works:

        Dim query As String = "SELECT * FROM Shifts WHERE Int(StartTime) BETWEEN #03/01/2023# AND #03/15/2023#;"

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

Could the VB.net datetime type possibly not be compatible with the access Int() function?

[ChrisE]

try and adapt this sample to your needs

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim sDB As String = "E:\Adressen.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"

        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Dim dt As New DataTable
        Dim err As String = Nothing

    

         Dim pStart As New DateTime(2020, 1, 29)
        Dim pEnd As New DateTime(2020, 10, 29)


        Using Cn
            Using cmd As New OleDbCommand With {.Connection = Cn}
                cmd.CommandText = "SELECT * FROM tbl_Tanken WHERE TankDatum >= @nDatumStart AND TankDatum < @nDatumEnde"
                cmd.Parameters.AddWithValue("@nDatumStart", OleDbType.Date).Value = pStart.Date
                cmd.Parameters.AddWithValue("@nDatumEnde", OleDbType.Date).Value = pEnd.Date.AddDays(1)
        Try
            Cn.Open()
            dt.Load(cmd.ExecuteReader)
        Catch ex As Exception
            err = ex.Message
        End Try
            End Using
        End Using
        DataGridView1.DataSource = dt

    End Sub

[Schmidt]

I think I spoke too soon, this returns nothing:

        Dim query As String = "SELECT * FROM Shifts WHERE Int(StartTime) BETWEEN @startperiod AND @endperiod;"

        Dim pStart As New DateTime(2023, 3, 1)
        Dim pEnd As New DateTime(2023, 3, 15)

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

        cmd.Parameters.AddWithValue("@startperiod", pStart)
        cmd.Parameters.AddWithValue("@endperiod", pEnd))

...
Could the VB.net datetime type possibly not be compatible with the access Int() function?

IMO there's two things wrong with this:

1) bad advice from si_the_geek
... (because the Int() Function will enforce a FullTable-Scan, ignoring potentially defined Indexes on your Date-Column)

2) not taking into account, that you work against an OleAutomation-Object in the end (an OleDB-Driver)
... (the .NET-Date-Type should better be passed as a Double, using the aptly named method: .ToOADate)

So, here's what I think should work better (untested, don't use .NET here on this machine):

Dim query As String = "SELECT * FROM Shifts WHERE StartTime BETWEEN @startperiod AND @endperiod;"

Dim pStart As Date = New DateTime(2023, 3, 1)
Dim pEnd   As Date = New DateTime(2023, 3, 15).AddMilliSeconds(86400000-1)
Dim cmd As OleDbCommand = New OleDbCommand(query, cn)

cmd.Parameters.AddWithValue("@startperiod", pStart.ToOADate())
cmd.Parameters.AddWithValue("@endperiod", pEnd.ToOADate())

Edit: Just in case pStart and pEnd are passed "from outside"
(where you cannot be sure, whether those Date-Values contain "fractions of a day"),
then it'd be better, to do the truncation and millisecond-adding directly within the param-assignment-lines:

cmd.Parameters.AddWithValue("@startperiod", pStart.Date.ToOADate)
cmd.Parameters.AddWithValue("@endperiod", pEnd.Date.AddMilliSeconds(86400000-1).ToOADate)

HTH

Olaf