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 '%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/4690/parse-a-uk-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 ;)

[ComITSolutions]

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)


I think its problem with query not with datetimepicker, you can try as below.

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

[brjames32]

ComITSolutions - thanks for the reply

I get the error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE

pointing to your line of code.

Any ideas?

[ComITSolutions]

Which database are you using? SQL Server, MSAccess,MYSql ...?
You have not mentioned... IF you are using SQL server it should work!

[brjames32]

Hi

Sorry - I am using MSAccess

[ComITSolutions]

I still feel, it is nothing to do with Datetimepicker. It is something related to windows regional longdate, shortdate setting or Ms Access.

I would like to suggest following points to try

1) Try to run the query in msaccess directly and check the result set.

Select * From Sanctions Where [Group] Like '%EN%' AND [Date] BETWEEN #01/04/2009# AND
#07/05/2009# AND [Description] <> 'Merits' "


2) I have observed one more peculiar thing while sending date value to MSaccess, Usually Date value is passed to access delimited by # i.e,
#01/04/2009#, When I tried to pass #31/05/2008#, I received error but when I send the date delimited with ' (single quote){'31/05/2008'}.
it worked.

I have little knowledge in MSAccess because I work only on SQL Server.
With my above experience I would like to suggest you to try sending date value delimited by " ' " instead "#"(this is just guess work).
I may be wrong also.

3) Instead of executing query directly, try create parametrized query(Stored Procedure) in your access database and call the same in Front end coding.

If possible Post Connection String.

[ComITSolutions]

Read this article, This may be useful to you.


http://www.codeguru.com/forum/showthread.php?t=316275

http://bytes.com/topic/access/insights/575420-literal-datetimes-their-delimiters