.Select function in a dataset question

[fniles]

I am using VB.NET 2005 and Access.
In my Access database I have a table called Test with 3 columns (id, col1
and col2).
I have a query called "qtest" as "SELECT test.id, test.col1, test.col2,
col1-col2 AS x FROM test"
In VB.NET I run the query as "select * from qtest order by x desc"
The result of query qtest look like the following:
ID col1 col2 x
a 100 0 100
b 30 0 30
c 25 0 25

Then, I select a record where x > 0 like so:
m_rowSelect = m_ds.Tables(0).Select("x > 0")
What it returns is the following record:
ID col1 col2 x
c 25 0 25

instead of the following record:
ID col1 col2 x
a 100 0 100

Since my query is sorted by "x desc", why the row select does not return ID
a, instead it returns ID c ?
It looks like the rowselect look for the record closest to x > 0, instead of
the 1st record it finds that meet x > 0.
Thank you.


Dim m_rowSelect As DataRow()
Private m_cmd As OleDb.OleDbCommand
Dim m_ds As DataSet
Private m_da As OleDb.OleDbDataAdapter

m_cmd = New OleDb.OleDbCommand
With m_cmd
.Connection = adoConOLE
.CommandText = "select * from qtest order by x desc"
End With
m_da = New OleDb.OleDbDataAdapter
m_ds = New DataSet
m_da.SelectCommand = m_cmd
m_da.Fill(m_ds)
m_rowSelect = m_ds.Tables(0).Select("x > 0")
qrem = m_rowSelect(0).Item("x") -> returns 25
refid = m_rowSelect(0).Item("id") -> returns c

Thank you

 

[JT]

Hi,
What "m_rowSelect = m_ds.Tables(0).Select("x > 0")" is actually an array of
datarows that meet the criteria x>0, which all three do. "qrem =
m_rowSelect(0).Item("x")" is then returning whatever happens to be the first
row in that array. The order you are specifying order the rows in the
datatable, but not in the datarow array returned by your select statement.
To do that, you need the overload of select that specifies the sort criteria
as its second parameter. See this link:

http://msdn2.microsoft.com/en-us/library/way3dy9w.aspx

 

[Cor Ligthert[MVP]]

fniles,

m_rowSelect = m_ds.Tables(0).Select("x > 0")

You don't show what kind of type m_rowSelect is, that makes asnswering very
difficult.
It has to be a collection of rows, I don't know how this reacts if Option
strict is off in this case.

However as Rich already wrote, there should be 3 rows in it. If you want
them sorted you can set the defaultview sort.

Cor

 

[JT]

"Rich?"
;>
--
John

fniles,


You don't show what kind of type m_rowSelect is, that makes asnswering very
difficult.
It has to be a collection of rows, I don't know how this reacts if Option
strict is off in this case.

However as Rich already wrote, there should be 3 rows in it. If you want
them sorted you can set the defaultview sort.

Cor

 

[fniles]

Thank you ! That did it.

Hi,
What "m_rowSelect = m_ds.Tables(0).Select("x > 0")" is actually an array
of
datarows that meet the criteria x>0, which all three do. "qrem =
m_rowSelect(0).Item("x")" is then returning whatever happens to be the
first
row in that array. The order you are specifying order the rows in the
datatable, but not in the datarow array returned by your select statement.
To do that, you need the overload of select that specifies the sort
criteria
as its second parameter. See this link:

http://msdn2.microsoft.com/en-us/library/way3dy9w.aspx