DataView.Find() query

[Nessie]

Hi All,

I am trying to do a search on a index/row number taken from a dataview i.e. I enter 379437 in the input box which I know has a index/row number of 6.

Now when I replace the Rows(index) with a Rows(6) I get the results that I want.

When I run the code below the index comes out as 75 which displays a completely different set of results.

Private Sub ButFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButFind.Click

    Dim SWTrioleNo As String
    SWTrioleNo = InputBox("Enter Triole Number:")

    dv = New DataView(ds.Tables("Tracker"))
    dv.Sort = "TrioleNo"
    Dim index As Integer = dv.Find(SWTrioleNo)

    TxtTriole.Text = ds.Tables("Tracker").Rows(index).Item(1).ToString
    DateOpen.Text = ds.Tables("Tracker").Rows(index).Item(2).ToString
    ComboBoxDesc.Text = ds.Tables("Tracker").Rows(index).Item(3).ToString
    TxtQty.Text = ds.Tables("Tracker").Rows(index).Item(4).ToString
    TxtPrice.Text = ds.Tables("Tracker").Rows(index).Item(5).ToString
    TxtCostCenter.Text = ds.Tables("Tracker").Rows(index).Item(6).ToString
    TxtGatekeeper.Text = ds.Tables("Tracker").Rows(index).Item(7).ToString
    TxtUser.Text = ds.Tables("Tracker").Rows(index).Item(8).ToString
    TxtID.Text = ds.Tables("Tracker").Rows(index).Item(9).ToString
    DateOrder.Text = ds.Tables("Tracker").Rows(index).Item(10).ToString
    CBStatus.Text = ds.Tables("Tracker").Rows(index).Item(11).ToString
    CBNext.Text = ds.Tables("Tracker").Rows(index).Item(12).ToString
    DateNextAction.Text = ds.Tables("Tracker").Rows(index).Item(13).ToString
    CBChase.Text = ds.Tables("Tracker").Rows(index).Item(14).ToString
    DateDelivery.Text = ds.Tables("Tracker").Rows(index).Item(15).ToString
    CBStrike.Text = ds.Tables("Tracker").Rows(index).Item(16).ToString
    TxtNotes.Text = ds.Tables("Tracker").Rows(index).Item(17).ToString

End Sub

Any help to resolve this would be greatly appreciated.

 

[jmcilhinney]

First things first, unless you're already using the DefaultView of the DataTable, e.g. the DataTable is bound to one or more controls, there's no point your creating a new DataView. You should just use the DefaultView.

Secondly, you should absolutely not be using 'ds.Tables("Tracker").Rows(index)' over and over again. Why follow that lengthy path over and over when you know for a fact that it's just going to give you the same thing every time? Just get the DataRow once and assign it to a local variable, then use that variable over and over.

As for the question, the problem is the fact that DataView.Find gives you an index in that DataView, not in the DataTable it was created from. You are sorting your DataView so the rows are obviously in a different order in the DataView and the DataTable. The row you want may be at index 6 in the DataTable but it is obviously at index 75 in the DataView. You need to get that row from the DataView. That will give you a DataRowView that contains all the same data as the corresponding DataRow. If you really need the DataRow itself then you can get it from the Row property of the DataRowView but, in this case, there's no point because you can get the same data form the DataRowView.

Dim view As DataView = ds.Tables("Tracker").DefaultView

view.Sort = "TrioleNo"

Dim row As DataRowView = view(index)

TxtTriole.Text = row(1).ToString()

That said, do you actually need to sort the view anyway? If you don't then the view and the table will both be in the same order so the indexes will match.

Having said all that, I would much rather just bind the DataTable to a BindingSource and then bind that to all those controls. You can then call the Find method of the BindingSource to get an index and assign that to the Position property. The rest is done for you.

 

[Nessie]

Hi,

That solved the problem thanks :tennis: , now the problem arises if I have two entries in the DB with the same "TrioleNo".

Ken

 

[jmcilhinney]

You can use the DataTable.Select method to find multiple rows that match arbitrary criteria.

 

[Nessie]

Hi,

Many thanks for pointing me inthe right direction but can't seem to get the DataTable.Select to work.

    Private Sub ButFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButFind.Click

        Dim SWTrioleNo As String

        Dim dt As New DataTable

        SWTrioleNo = InputBox("Enter Triole Number:")

        Dim view As DataView = ds.Tables("Tracker").DefaultView

        view.Sort = "TrioleNo"

        Dim index As Integer = view.Find(SWTrioleNo)

        Dim rows As DataRow() = dt.Select(SWTrioleNo)

        'Dim row As DataRowView = view(index)

        TxtTriole.Text = rows(1).ToString
        DateOpen.Text = rows(2).ToString
        ComboBoxDesc.Text = rows(3).ToString
        TxtQty.Text = rows(4).ToString
        TxtPrice.Text = rows(5).ToString
        TxtCostCenter.Text = rows(6).ToString
        TxtGatekeeper.Text = rows(7).ToString
        TxtUser.Text = rows(8).ToString
        TxtID.Text = rows(9).ToString
        DateOrder.Text = rows(10).ToString
        CBStatus.Text = rows(11).ToString
        CBNext.Text = rows(12).ToString
        DateNextAction.Text = rows(13).ToString
        CBChase.Text = rows(14).ToString
        DateDelivery.Text = rows(15).ToString
        CBStrike.Text = rows(16).ToString
        TxtNotes.Text = rows(17).ToString

        TxtInc.Clear()
        TxtInc.Text = index

    End Sub

regards

 

[jmcilhinney]

Have you actually read the documentation for DataTable.Select to see how it works? What type of value does it expect? What type of value are you passing it?