VS Application/Access Db Date Conversion Issues - Very Weird

[richo.mw]

Hi There,

I am experiencing a very weird problem with a visual basic.net program I am creating. The app uses an access database.

Note: I am an Australian user so use date format dd/mm/yyyy

Problem: my frmMain has a list box that retrieves a list of date values from the access db and displays them in the correct format (15/12/2000 - dd/mm/yyyy). When I update the DB using that same date the date format becomes reversed (ie interprets as 12/15/2000 - mm/dd/yyyy).

Test cases:
01/02/2000 - WRONG... Updates database records for 2/1/2000
31/02/2000 - CORRECT... updates 31st Feb

I believe the problem is something to do with the fact I am enclosing my date in my code with #'s and that somewhere between visual studio and access, the dates are getting flipped.

Here is a sample of my code...

Private Sub UpdateDatabase()
        MessageBox.Show(lbxDate2.Text)
        Dim strUpdateDates As String = _
            "UPDATE Dates SET Observation = '" & txtObservation.Text & _
            "' WHERE Dates.Date2 = #" & lbxDate2.Text & "#"
 
        Dim cmdWeatherDbDates As New System.Data.OleDb.OleDbCommand(strUpdateDates, connWeatherDb)
        Dim intDatesUpdateRows As Integer
 
        cmdWeatherDbDates.Connection.Open()
        intDatesUpdateRows = cmdWeatherDbDates.ExecuteNonQuery()
        connWeatherDb.Close()
    End Sub

Any help would be much appreciated!
I have tried everything I can think of and more. As well as spending a whole day trying to work out the issue.

Matt

[dadelsen]

There seems to be some logic in the access DB engine to recognize that 31 cannot be the number of a month. But not enough to recognize that Feb 31th is very unusual.

maybe VBA documentation is helpful:
http://msdn.microsoft.com/en-us/library/aa212163(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa172286(office.11).aspx

When using SQL and # # to write dates to Access, MS recommends to use USA dateformat.

[richo.mw]

Cheers for the response. I have since resolved this issue.

"The SQL standard was set up to always use the US format for dates (and numbers, etc), and not what you have picked elsewhere.

The problem with some dates being 'flipped' and others not is because Access/Jet decides to be 'nice' (or to be more accurate, Evil!), and convert values that don't fit.. 01/02/2000 is apt in US format so is interpreted as such; 31/02/2000 isn't apt because 31 is too big to be a month, and so it gets automatically converted to 02/31/2000.

What you could do is add the value to your SQL string in US format, however it is much safer instead to add it as a Parameter to the Command - as that will ensure that it is delimited and formatted correctly (assuming of course that you actually give it a Date value, rather than Text)."

Used parameters and started to make progress.

My code (working as it should) below: ...there are additional components now to the original code

Private Sub UpdateDatabase()
        'Update the date related information into Dates table.
        Dim strUpdateDates As String = _
            "UPDATE Dates SET Observation = '" & txtObservation.Text & _
            "' WHERE Dates.Date2 = @DateParam"

        'Update all reading information into readings table.
        Dim strUpdateReadings As String = _
            "Update Readings Set Readings.Rained = " & cbxRained.Checked & _
                ", Readings.Tmax = " & txtTempMax.Text & _
                ", Readings.Tmin = " & txtTempMin.Text & _
                ", Readings.Rain = " & txtRain.Text & _
                ", Readings.Evap = " & txtEvaporation.Text & _
                ", Readings.VapourPressure = " & txtVapourPressure.Text & _
            " WHERE Readings.Date2 = @DateParam"

        'Create Db command using update statements and Db connection string
        Dim cmdWeatherDbDates As New System.Data.OleDb.OleDbCommand(strUpdateDates, connWeatherDb)
        Dim cmdWeatherDbReadings As New System.Data.OleDb.OleDbCommand(strUpdateReadings, connWeatherDb)

        'Add Date parameter for update queries to respective commands. Required to keep the date in 
        'correct format and prevent date changing between AUS and US date formats.
        cmdWeatherDbDates.Parameters.AddWithValue("@DateParam", lbxDate2.SelectedValue)
        cmdWeatherDbReadings.Parameters.AddWithValue("@DateParam", lbxDate2.SelectedValue)

        Dim intDatesUpdateRows As Integer
        Dim intReadingsUpdateRows As Integer

        cmdWeatherDbDates.Connection.Open()                             'Open connection to Db
        intDatesUpdateRows = cmdWeatherDbDates.ExecuteNonQuery()        'Execute query for Dates table
        intReadingsUpdateRows = cmdWeatherDbReadings.ExecuteNonQuery()  'Execute query for Readings table
        connWeatherDb.Close()                                           'Close Db connection
    End Sub

Thanks for response.
Hope this helps anyone who has the same problem in the future