DataTable.Select() is bugged?

R

ronchese

Hi.

I'm trying to make a criteria string to use in Select() method of a datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 
J

Jay B. Harlow [MVP - Outlook]

Cesar,
I suspect that '2006/05/09' is not being translated properly.

Try:

arrRows = dtbSample.Select("COM_STARTDATE >= #05/09/2006#")


As Date values in DataSet expressions need to be delimited by #.

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

I believe the date values are in m/d/y format.


Something like:

Dim table As New DataTable
table.Columns.Add("COM_STARTDATE", GetType(DateTime))
table.Rows.Add(#5/8/2006 9:00:00 AM#)

Dim rows() As DataRow = table.Select("COM_STARTDATE >=
#05/09/2006#")


--
Hope this helps
Jay B. Harlow [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


"ronchese" <info(a)carsoftnet.com.br> wrote in message
Hi.

I'm trying to make a criteria string to use in Select() method of a
datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the
following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified
criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is
returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning
row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 
R

ronchese

OUCH! Found it... the Select() method by default, searches only for commited data.... and my DataRow was not yet commited by the AcceptChanges() method.

Then, to fix it, i should use:
arrRows = dtbSample.Select("COM_STARTDATE >= #May/09/2006#", "", DataViewRowState.ModifiedCurrent)

The third parameter now makes the method works like I want.

[]s
Cesar







"ronchese" <info(a)carsoftnet.com.br> wrote in message Hi.

I'm trying to make a criteria string to use in Select() method of a datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 
R

ronchese

I tryied # also, like showed in PS area, at end of e-mail :D
ps.:
For you know, I tryied to build criteria like below and still is returning
row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #
Click to expand...

But the problem was the rows not commited, and the Select() only searches in commited data.
I posted the solution in a other reply.

Thanks anyway, for response.

[]s
Cesar



Jay B. Harlow said:
Cesar,
I suspect that '2006/05/09' is not being translated properly.

Try:

arrRows = dtbSample.Select("COM_STARTDATE >= #05/09/2006#")


As Date values in DataSet expressions need to be delimited by #.

http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

I believe the date values are in m/d/y format.


Something like:

Dim table As New DataTable
table.Columns.Add("COM_STARTDATE", GetType(DateTime))
table.Rows.Add(#5/8/2006 9:00:00 AM#)

Dim rows() As DataRow = table.Select("COM_STARTDATE >=
#05/09/2006#")


--
Hope this helps
Jay B. Harlow [MVP - Outlook]
.NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net


"ronchese" <info(a)carsoftnet.com.br> wrote in message
Hi.

I'm trying to make a criteria string to use in Select() method of a
datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the
following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified
criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is
returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning
row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
Click to expand...
 
R

ronchese

Hmmm... After some testes, I noticed that the value DataViewRowState.CurrentRows works better than the previous I told.

Example:
arrRows = dtbSample.Select(strCrit, "", DataViewRowState.CurrentRows)

[]s
Cesar

"ronchese" <info(a)carsoftnet.com.br> wrote in message OUCH! Found it... the Select() method by default, searches only for commited data.... and my DataRow was not yet commited by the AcceptChanges() method.

Then, to fix it, i should use:
arrRows = dtbSample.Select("COM_STARTDATE >= #May/09/2006#", "", DataViewRowState.ModifiedCurrent)

The third parameter now makes the method works like I want.

[]s
Cesar







"ronchese" <info(a)carsoftnet.com.br> wrote in message Hi.

I'm trying to make a criteria string to use in Select() method of a datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 
R

ronchese

Working more, I got another problems with some other uses for select()... After break the head a bit more, I found that DataBindings was the root for causing all the troubles. Then, after I set:

BindingContext(dtb).SuspendLayout()

... when the form closes, everything work like nothing was happened.
And then, I even dont needed that 3rd parameter in the Select() commands I used. Rewrote everything again. :D

[]s
Cesar


"ronchese" <info(a)carsoftnet.com.br> wrote in message Hmmm... After some testes, I noticed that the value DataViewRowState.CurrentRows works better than the previous I told.

Example:
arrRows = dtbSample.Select(strCrit, "", DataViewRowState.CurrentRows)

[]s
Cesar

"ronchese" <info(a)carsoftnet.com.br> wrote in message OUCH! Found it... the Select() method by default, searches only for commited data.... and my DataRow was not yet commited by the AcceptChanges() method.

Then, to fix it, i should use:
arrRows = dtbSample.Select("COM_STARTDATE >= #May/09/2006#", "", DataViewRowState.ModifiedCurrent)

The third parameter now makes the method works like I want.

[]s
Cesar







"ronchese" <info(a)carsoftnet.com.br> wrote in message Hi.

I'm trying to make a criteria string to use in Select() method of a datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 
C

Cor Ligthert [MVP]

Ronchese,

That the Select is buggy is in my idea not even testable because the lack of documentation around the Expression.

However, AFAIK has a date in the select in VB to be the USA date literal as you have used in your example. The Expression is AFAIK totaly based on USA behaviour.

I hope this helps a little bit.

Cor
"ronchese" <info(a)carsoftnet.com.br> schreef in bericht Hi.

I'm trying to make a criteria string to use in Select() method of a datatable, searching for a date, but it is apparently not working!

In one of my tests, I have a datatable with 1 row and a field containing the following value (extracted from Immediate Window):

?dtbSample.Rows(0).Item("COM_STARTDATE")
#5/8/2006 9:00:00 AM# {Date}
Date: #5/8/2006 9:00:00 AM#
(for better understand, consider this date as '2006/05/08')


Then, I test a criterium to DON'T show that row, using this:
arrRows = dtbSample.Select("COM_STARTDATE >= '2006/05/09'")

Now, look the results in immediate window:
?arrRows.Length
1

!!!!!!
The Select() is returning a DataRow with a date less than the specified criterium, which is requiring a date higher than existing in the DataTable!!
In resume, '2006/05/08' is less than "COM_STARTDATE >= '2006/05/09'", but is returning like if '2006/05/08' is higher.

Someone already noticed that behaviour? There is a way to fix it?


Cesar

ps.:
For you know, I tryied to build criteria like below and still is returning row (not working):


("COM_STARTDATE >= #'2006/05/09#") 'yyyy/MM/dd and #
("COM_STARTDATE >= '05/09/2006'") 'MM/dd/yyyy
("COM_STARTDATE >= '" & TheDate.ToString & "'") 'a Date datatype
("COM_STARTDATE >= #05/09/2006#") 'MM/dd/yyyy and #
("COM_STARTDATE >= #May/09/2006#") 'MMM/dd/yyyy and #

I repeat, these don't works!! They are returning the row. :^P
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with query dates 4
Changing Data format 3
UNABLE TO SORT DATE 1
Date time format 3
Getting MM-DD HH:MM out of Julian date 4
Fromat Date Wrong value for month 2
date conversion problem 4
What format should webservice DateTime be? 8

Top