Re: DateTimePicker problems
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 :)
Re: DateTimePicker problems
Hi Hannes! :)
Cheers for the reply. I have tried this:
Code:
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??
Re: DateTimePicker problems
Have you tried :
Code:
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)
Re: DateTimePicker problems
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 ;)
Re: DateTimePicker problems
Try this (it's C#):
Code:
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());
}
}
}
Re: DateTimePicker problems
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
Re: DateTimePicker problems
Remove the ; at the end of lines, and think backwards. :)
Code:
Dim current As CultureInfo = CultureInfo.CurrentCulture
Debug.Print current
Dim SDate As String = String.Parse(sDateTime.Text, CultureInfo.InvariantCulture)
Imports instead of Using, also
Re: DateTimePicker problems
Dglienna thanks for the help so far :)
I get the error 'Parse is not a member of String' for this part:
and also the error 'Value of type 'System.Globalization.CultureInfo' cannot be converted to 'String' for this line:
Quote:
Debug.Print(current)
where the emphasis is on current
Re: DateTimePicker problems
you can just separate the day , month & year instead of passing all the date
sDateTime.Value.Day
sDateTime.Value.Month
sDateTime.Value.Year
Re: DateTimePicker problems
komalo
Thing is, I need the whole date though :(
Re: DateTimePicker problems
Re: DateTimePicker problems
My code is now:
Code:
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!!
Re: DateTimePicker problems
Just noticed the OleTableAdapter. Why not try the SQL Table Adapter?
Re: DateTimePicker problems
Ah yes - didn't think about that. Will give it a go and let ya know how I get on.
Cheers for suggesting that ;)
Re: DateTimePicker problems
Quote:
Originally Posted by
brjames32
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:
Code:
SDate = sDateTime.Text
FDate = fDateTime.Text
Select Statement:
Code:
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.
Code:
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)
Re: DateTimePicker problems
ComITSolutions - thanks for the reply
I get the error:
Quote:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE
pointing to your line of code.
Any ideas?
Re: DateTimePicker problems
Which database are you using? SQL Server, MSAccess,MYSql ...?
You have not mentioned... IF you are using SQL server it should work!
Re: DateTimePicker problems
Hi
Sorry - I am using MSAccess
Re: DateTimePicker problems
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.
Code:
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.
Re: DateTimePicker problems
