TableAdaptor Configure and Query Builder Differences

[paulb]

Hi,

I am using VS2005 vb.net. I have a dataset.xsd file where I have
added a dataAdapter and built my queries using the built in Query
Builder.

I am connecting to an Access database with the following connection
string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\127.0.0.1\database
\databse.mdb"

Everything is ok but I have a problem selecting unique dates. Some
dates are return twice!

My sql is as follows:

SELECT DISTINCT DateValue([DateTime]) AS [Date]
FROM Job
WHERE ([DateTime] >= ?) AND ([DateTime] < ?)

I tested this with the QueryBuilder using the following parameters. I
am using the format yyyy-mm-dd:
#2007-12-17#
#2007-01-19#
and I always get the correct results i.e. a list of unique dates

However, when I run call this query via the dataset function that is
created I get different results for the same dates and same SQL.

The dataset query autogenerates the following code:

Public Overridable Overloads Function GetUniqueDay(ByVal
DateTime As Date, ByVal DateTime1 As Date) As
DataSetFilters.UniqueDayDataTable
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Me.Adapter.SelectCommand.Parameters(0).Value =
CType(DateTime, Date)
Me.Adapter.SelectCommand.Parameters(1).Value =
CType(DateTime1, Date)
Dim dataTable As DataSetFilters.UniqueDayDataTable = New
DataSetFilters.UniqueDayDataTable
Me.Adapter.Fill(dataTable)
Return dataTable
End Function

I noticed that the dates values passed into this function are
converted by the Date type as:
#12/18/2007# and #1/19/2008#

The results of this query looks like this:
02/01/2008 00:01
02/01/2008 23:58 ---> This should be 03/01/2008
04/01/2008 00:00
07/01/2008 00:01
07/01/2008 23:58 ---> This should be 08/01/2008
09/01/2008 00:01
09/01/2008 23:57 ---> This should be 10/01/2008
10/01/2008 23:59
14/01/2008 00:00
14/01/2008 23:58 ---> This should be 15/01/2008

When I run the exact same query using the Query Builder->Execute and
follows for the same date parameters (and doesn't matter if I use
"yyyy-mm-dd" or "dd/mm/yyyy" as the results are always the same) I get
the following:

02/01/2008 00:00
03/01/2008 00:00 --> Now ok!
04/01/2008 00:00
07/01/2008 00:00
08/01/2008 00:00 --> Now ok!
09/01/2008 00:00
10/01/2008 00:00 --> Now ok!
11/01/2008 00:00
14/01/2008 00:00
15/01/2008 00:00 --> Now ok!

Does anyone have any advice what I can do to correct this?

Thank you

Regards

Paul

 

[Cor Ligthert[MVP]]

Paulb,

In my idea is using DateTime as a Field description in SQL server and in
..Net is asking for troubles.

Can't you no use better names?

Cor