DateTimePicker problems

[brjames32]

I am using a DateTimePicker to allow the user to select dates between which, info from a database is selected and displayed in a DataGridView.

This works fine exept that the dates selected by the DateTimePicker seem to bring back results in US date format!

If I do a debug.print (DateTime1.text), the date is in UK format i.e 11/05/2009 but when these variables are used in a Select statement, the dates returned seem to be in US format e.g. selecting dates between 01/04/2009 and 7/05/2009 selects dates between 04/01/2009 and 05/07/2009 when the results are displayed in the DataGridView!

Getting DateTime variables:

SDate = sDateTime.Text
FDate = fDateTime.Text

Select Statement:

Dim da As New OleDb.OleDbDataAdapter("Select * From Sanctions Where [Group] Like '%EN%' AND [Date] BETWEEN #" & SDate & "# AND #" & FDate & "# AND [Description] <> 'Merits' ", cnDept)

Does anyone know why this is happening?

Thanks

[HanneSThEGreaT]

You cold either change the format of the Date column in your DB - if you have access to it, but, I think the best would be to use the Format function with which you can set the proper display order

[brjames32]

Hi Hannes!

Cheers for the reply. I have tried this:

SDate = sDateTime.Value.ToString("dd MM yyyy")

and also changing the Format to custom and entering dd/mm/yyyy in the CustomFormat box but none of these make any difference.

Any ideas??

[HanneSThEGreaT]

Have you tried :

Dim da As New OleDb.OleDbDataAdapter("Select * From Sanctions Where [Group] Like '&#37;EN%' AND [Date] BETWEEN #" & Format(SDate, "dd/MM/yyyy") & "# AND #" & Format(FDate, "dd/MM/yyyy") & "# AND [Description] <> 'Merits' ", cnDept)

[brjames32]

Just tried that and selected the dates: 01/05/2009 and 07/05/2009 but when the DataGridView is populated, the dates range from 05/01/2009 up until the present day!!

Something is swapping the dates into US format but haven't a clue what it is!

The Database Date field is setup as Date/Time.

Can you think what it is??

Cheers Hannes

[dglienna]

Try this (it's C#):

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Resources;
using System.Text;
using System.Threading;
using System.Globalization;

public class MainClass
{
    public static void Main()
    {
        CultureInfo current = CultureInfo.CurrentCulture;
        string numberString = "33,223.510";

            try
            {
                double number = double.Parse(numberString, CultureInfo.InvariantCulture);
                Console.WriteLine("Parsed! {0}", number);
            }
            catch (Exception e)
            {
                Console.WriteLine("Caught exception: {0}", e.ToString());
            }
    }
}

[brjames32]

Hi DGlienna

Thanks for the reply. I can convert most of the code you gave me but not all of it. Any chance you can help me out??

Thanks

[dglienna]

Remove the ; at the end of lines, and think backwards.

Dim current As CultureInfo = CultureInfo.CurrentCulture
Debug.Print current
Dim SDate As String = String.Parse(sDateTime.Text, CultureInfo.InvariantCulture)

Imports instead of Using, also

[brjames32]

Dglienna thanks for the help so far

I get the error 'Parse is not a member of String' for this part:

String.Parse

and also the error 'Value of type 'System.Globalization.CultureInfo' cannot be converted to 'String' for this line:

Debug.Print(current)

where the emphasis is on current

[komalo]

you can just separate the day , month & year instead of passing all the date
sDateTime.Value.Day
sDateTime.Value.Month
sDateTime.Value.Year

[brjames32]

komalo

Thing is, I need the whole date though

[komalo]

okay
check this : http://www.developerfusion.com/code/...k-date-string/

[brjames32]

My code is now:

Dim ukCulture As CultureInfo = New CultureInfo("en-GB")
        Dim ukSDate As DateTime = DateTime.Parse(SDate, ukCulture.DateTimeFormat)
        Dim ukFDate As DateTime = DateTime.Parse(FDate, ukCulture.DateTimeFormat)

but this still makes no difference. The dates are still being selected in US format even though doing a debug.print sDate shows them the right way round!

This is driving me insane!!

[dglienna]

Just noticed the OleTableAdapter. Why not try the SQL Table Adapter?

[brjames32]

Ah yes - didn't think about that. Will give it a go and let ya know how I get on.

Cheers for suggesting that