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).
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.