.Select function in a dataset question - ADO DAO RDO RDS

This is a discussion on .Select function in a dataset question - ADO DAO RDO RDS ; 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 ...

+ Reply to Thread
Results 1 to 5 of 5

.Select function in a dataset question

  1. Default .Select function in a dataset question

    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



  2. Default RE: .Select function in a dataset question

    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

    --
    John


    "fniles" wrote:

    > 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
    >
    >
    >


  3. Default Re: .Select function in a dataset question

    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



  4. Default Re: .Select function in a dataset question

    "Rich?"
    ;>
    --
    John


    "Cor Ligthert[MVP]" wrote:

    > 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
    >
    >


  5. Default Re: .Select function in a dataset question

    Thank you ! That did it.

    "JT" <Jthayer@online.nospam> wrote in message
    news:A0BC78A7-2818-40D0-A7E2-C8887B76D6C8@microsoft.com...
    > 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
    >
    > --
    > John
    >
    >
    > "fniles" wrote:
    >
    >> 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
    >>
    >>
    >>




+ Reply to Thread