Question Date format issue while saving in database

skagr

New member
Joined
Sep 2, 2021
Messages
2
Programming Experience
3-5
I am new in VB.net, while I save date in MS-Access 2007 database using MaskedText in Vb.Net, it automaticaly converts given date in month. E.g. I supplied 01.05.2021 it becomes 05.01.2021. Please help me to resolve this issue. Using DateTimePicker control also arise same issue.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,548
Location
Sydney, Australia
Programming Experience
10+
If it doesn't work then you did it wrong. If you don't show us what you did, we can't see what's wrong with it. Most likely the issue is that you are actually saving a String instead of a Date and an implicit conversion is causing the issue. For instance, if you use a date literal in a SQL string then it will assume a format of 'MM/dd/yyyy', regardless of culture or language settings on the current machine. If you do things properly, i.e. use a parameter with a Date value, then it will just work because there is never any text involved so there is never any format to be an issue. Your should definitely be using a DateTimePicker too. Here's a quick example:
VB.NET:
Dim sql = "INSERT INTO Person (GivenName, FamilyName, DateOfBirth)
           VALUES (@GivenName, @FamilyName, @DateOfBirth)"

Using connection As New OleDbConnection(connectionString),
      command As New OleDbCommand(sql, connection)
    With command.Parameters
        .Add("@GivenName", OleDbType.VarChar, 50).Value = givenNameTextBox.Text
        .Add("@FamilyName", OleDbType.VarChar, 50).Value = familyNameTextBox.Text
        .Add("@DateOfBirth", OleDbType.Date).Value = dateOfBirthPicker.Value.Date
    End With
    
    connection.Open()
    command.ExecuteNonQuery()
End Using
Note that that code uses the Value property of the DateTimePicker, which is type Date, and not the Text property, which is type String. At no point should you ever convert a Date to a String unless you explicitly need a String for display or serialisation.
 
Top Bottom