sql query give incorrect results (date/time) issue

S

seegoon

Hi to all.
I am pretty new to this stuff , so forgive me if I am asking the
obvious.
I am trying to extract some data from an Access database and am having
some trouble.
The SQL query that I am using (in VB.net) searches for products
depending on selected operator and a timespan.
The query works fine is my PC's system date setting is mm/dd/yyyy.
If I change it to dd/mm/yyyy the query does not return any info.

Here is the relevant code. I get the "timespan" from 2 sets of
comboboxes , that set the start and end date. ie: 21 June 2005 to 30
June 2005. The operator is also from a combobox.
-------------------------------------------------------------------


stday = Val(comStartday.SelectedItem)
stmnth = Val(comstartMnth.SelectedIndex + 1)
styr = Val(comstartyr.SelectedItem)

finday = Val(comfinday.SelectedItem)
finmnth = Val(comfinmnth.SelectedIndex + 1)
finyr = Val(comfinyr.SelectedItem)

Dim findate As DateTime = New Date(finyr, finmnth, finday, 23,
59, 59)
Dim stdate As DateTime = New Date(styr, stmnth, stday)


Dim getproducts As New OleDb.OleDbCommand("select
distinct(prod) from loginfo where oper = '" & comoperator.SelectedItem
& "' and DT >= # " & stdate & "# and DT <= #" & findate & "# ",
startup_form.mycon)


Hope you can work out what is going on there!!

The results of the query are used to populate a listbox(code not shown)

How can I make the code work on any system with any regional settings.

Thanks
Robin
 
A

Armin Zingler

seegoon said:
Hi to all.
I am pretty new to this stuff , so forgive me if I am asking the
obvious.
I am trying to extract some data from an Access database and am having
some trouble.
The SQL query that I am using (in VB.net) searches for products
depending on selected operator and a timespan.
The query works fine is my PC's system date setting is mm/dd/yyyy.
If I change it to dd/mm/yyyy the query does not return any info.

Here is the relevant code. I get the "timespan" from 2 sets of
comboboxes , that set the start and end date. ie: 21 June 2005 to 30
June 2005. The operator is also from a combobox.
-------------------------------------------------------------------


stday = Val(comStartday.SelectedItem)
stmnth = Val(comstartMnth.SelectedIndex + 1)
styr = Val(comstartyr.SelectedItem)

finday = Val(comfinday.SelectedItem)
finmnth = Val(comfinmnth.SelectedIndex + 1)
finyr = Val(comfinyr.SelectedItem)

Dim findate As DateTime = New Date(finyr, finmnth, finday, 23,
59, 59)
Dim stdate As DateTime = New Date(styr, stmnth, stday)


Dim getproducts As New OleDb.OleDbCommand("select
distinct(prod) from loginfo where oper = '" & comoperator.SelectedItem
& "' and DT >= # " & stdate & "# and DT <= #" & findate & "# ",
startup_form.mycon)


Hope you can work out what is going on there!!

The results of the query are used to populate a listbox(code not shown)

How can I make the code work on any system with any regional settings.
Click to expand...

Use parameters instead of concatenating the string yourself. See
getproducts.Parameters (and it's documentation).

See also:
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp


Armin
 
O

Oenone

seegoon said:
How can I make the code work on any system with any regional settings.
Click to expand...

Always specify dates in strings in ISO8601 format: yyyy-mm-dd. This is the
only non-ambiguous date format. VB.NET, VB6, MS Access and SQL Server (at
least) all understand this format.
 
S

seegoon

Hi guys.
Thanks for the help. As I said I'm new to this. I read this page
mentioned above , but I may as well be reading a foreign language for
all that I understood :0(
How can I get the date into the yyyy-mm-dd format.
I tried Format(stdate , "yyyy/mm/dd') and all I get ti stdate =
yyyy/mm/dd . ie I get those letters!! Stdate had 12/07/2005 befor the
format command.
Cheers
Robin the confused
 
S

seegoon

Hi again .
Finally got it working !!
I created the date in the format yyyy/mm/dd .
the next line does this: stdate = Format(tmpdate, " MM/dd/yyyy ") ,
which should swop it back again , but it seens to be working , don't
understand ,
but it works so I'm not going to touch it!!
Thanks for the tips , I'll carry on reading if anyone has any more to
offer.
Robin the slightly more confused , but less irritated
 
Top