Findrows doesnt appear to be working on multiples

  • Thread starter Thread starter RSH
  • Start date Start date
R

RSH

I have a situation where I am filling two datasets and I need to print out
the names that appear in both datasets. I initially converted them to
dataviews, sorted on last name and performed a dataview.find on the view.
This indeed found the people that appeared in the two seperate databases
with the last name (as long as I performed a sort on the last name field
BEFORE finding). The issue immediately came up that i needed to check the
first name too. Well after searching for a bit I found the code i thought I
needed and put it in (see below) but it doesn't catch the matches, it
returns nothing everytime even though i have several matches intentionally
put in.

I have to use the datasets for comparision because the datasources with be
comparing from an Access database and a SQL Server database so a join query
will not work.

Any advice or help would be greatly appreciated.

Ron

Dim oSQLConn As SqlConnection = New SqlConnection(strConn)

Dim daCust As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
test.dbo.Customers", oSQLConn)

Dim daOldCust As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
test.dbo.OldCustomers", oSQLConn)

Dim cbCust As SqlCommandBuilder = New SqlCommandBuilder(daCust)

Dim strFN As String

Dim strLN As String

Dim dsCust As DataSet = New DataSet

Dim dsOldCust As DataSet = New DataSet

Dim intOldCustFNIndx As Integer

Dim intOldCustLNIndx As Integer

daOldCust.FillSchema(dsOldCust, SchemaType.Mapped, "OldCustomers")

daOldCust.Fill(dsOldCust, "OldCustomers")

Dim dvOldCust As New DataView

dvOldCust = dsOldCust.Tables("OldCustomers").DefaultView

oSQLConn.Open()

daCust.Fill(dsCust, "Customers")

Dim i As Integer

For i = 0 To dsCust.Tables("Customers").Rows.Count - 1

strFN = dsCust.Tables("Customers").Rows(i).Item("FN")

strLN = dsCust.Tables("Customers").Rows(i).Item("LN")

intOldCustFNIndx = 0

intOldCustLNIndx = 0

dvOldCust.Sort = "LN,FN"

Dim foundRows() As DataRowView = dvOldCust.FindRows(New Object() {strFN,
strLN})

MessageBox.Show(foundRows.Length)


Dim cnt As DataRowView

For Each cnt In foundRows

Console.WriteLine("{0}, {1}", cnt("FN").ToString(), cnt("LN").ToString())

Next

oSQLConn.Close()
 
Good suggestion...it almost worked :-)

Here is what I ended up with:


Sub main()

Dim oSQLConn As SqlConnection = New SqlConnection(strConn)

Dim daCust As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
test.dbo.Customers", oSQLConn)

Dim daOldCust As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
test.dbo.OldCustomers", oSQLConn)

Dim cbCust As SqlCommandBuilder = New SqlCommandBuilder(daCust)

Dim strFN As String

Dim strLN As String

Dim dsCust As DataSet = New DataSet

Dim dsOldCust As DataSet = New DataSet

Dim intOldCustFNIndx As Integer

Dim intOldCustLNIndx As Integer

daOldCust.FillSchema(dsOldCust, SchemaType.Mapped, "OldCustomers")

daOldCust.Fill(dsOldCust, "OldCustomers")

oSQLConn.Open()

daCust.Fill(dsCust, "Customers")

Dim colPk(2) As DataColumn

colPk(0) = dsOldCust.Tables("OldCustomers").Columns("FN")

colPk(1) = dsOldCust.Tables("OldCustomers").Columns("LN")

dsOldCust.Tables("OldCustomers").PrimaryKey = colPk

Dim i As Integer

For i = 0 To dsCust.Tables("Customers").Rows.Count - 1

Dim foundRow As DataRow

Dim findTheseVals(1) As Object

strFN = dsCust.Tables("Customers").Rows(i).Item("FN")

strLN = dsCust.Tables("Customers").Rows(i).Item("LN")

findTheseVals(0) = dsCust.Tables("Customers").Rows(i).Item("FN")

findTheseVals(1) = dsCust.Tables("Customers").Rows(i).Item("LN")

foundRow = dsOldCust.Tables("OldCustomers").Rows.Find(findTheseVals)

If Not (foundRow Is Nothing) Then

Console.WriteLine(foundRow(2).ToString() & " " & foundRow(1).ToString())

End If

Next

End Sub
 
Back
Top