access switches days and months

[vampke]

Hi guys,

I'm habing a problem with an access database i'm trying to fill programmatically with vb.net.

I have a datatimepicker to set a date with a custom format like "dd/mm/yyyy hh:mm"
If I insert this date in my access database the days and months are switched (mm/dd instead of dd/mm).
What could be causing this?

Cheers!

v.

[sknake]

Date formatting issues -- which is why you should use parameterized queries so this won't be a problem. Here is an example of SQL Server but you can change the class names over to OleDb:

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";

command.Parameters.Add(       new SqlParameter("@CustomerID", SqlDbType.NChar, 5)).Value = customerID;

When you do this it will work regardless of the datetime format.

[kvprajapati]

>If I insert this date in my access database the days and months are switched (mm/dd instead of dd/mm).

It is not a problem.

>What could be causing this?

Regional Settings - Control Panel. (Don't change country/language/datetime settings)

Solution:
Use format characters when displaying data.

[vampke]

thank you for your help guys.
I've tried parameterized queries but I got a datatime mismatch error while executing the insert query.
I figured it would have been a globalisation problem.
Apparently the error occurs when I set my classes datatime to the value in a datetimepicker with custom format(dd-MM-yyyy H:mm:ss)
It is weird though: if I return a messagebox with the month of the datetimepicker value, it will return what I need, but if I set my classes datetime, it goes wrong.
I managed to solve the problem using

Dim strCurrentCulture As String = Thread.CurrentThread.CurrentCulture.ToString
        Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US", True)
        _cls.myDateTime = DateTimePicker1.Value
        Thread.CurrentThread.CurrentCulture = New CultureInfo(strCurrentCulture, False)

I'm not sure if I will run into problems on other machines with different culturesettings.
Could anyone enlighten me on this?

Cheers!

v.

edit: Hmmmm.... apparently this only works now and then :(
I don't understand....

[vampke]

I found a workaround using

Dim intDay, intMonth, intYear, intHour, intMinute, intSecond As Integer
        intDay = (DateTimePicker1.Value).Day
        intMonth = (DateTimePicker1.Value).Month
        intYear = (DateTimePicker1.Value).Year
        intHour = (DateTimePicker1.Value).Hour
        intMinute = (DateTimePicker1.Value).Minute
        intSecond = (DateTimePicker1.Value).Second
        _cls.myDateTime = New System.DateTime(intYear, intMonth, intDay, intHour, intMinute, intSecond)

is this an acceptable solution? Is there a reason why I should not be using this?

[sknake]

That is perfectly OK to do, but I am perplexed why it doesn't work when using the datetimePicker.Value. As long as it works though :)

[vampke]

but I am perplexed why it doesn't work when using the datetimePicker.Value.

as am i :)

As long as it works though :)

I guess so :)

[sknake]

I'm glad its working though.

Please mark this thread as solved if you have found an answer to your question and good luck!