[RESOLVED] ado.net join 2 dbfs

[DebbieInFlorida]

I am trying to use ADO.net, but I find the datatables and datasets very unfriendly.
I could easily do joins before, but cannot do it now. Bascially, I have 2 dbf's, I need to join them on a common field (Value). I have checked, the field exists. there is a 1-1 relationship, with 1 table having more records, but I want all records to show. this is my code, but it blows up on the Relations.Add line

'=================================================================
' join 2 tables together with common field
'=================================================================
Public Function Join_ADO_Datatables(ByVal sQuery1 As String, _
ByVal sQuery2 As String, _
ByVal sFilePath1 As String, _
ByVal sTableName1 As String, _
ByVal sFilePath2 As String, _
ByVal sTableName2 As String, _
ByVal sJoinFieldName As String) As DataTable


Dim OLE_CN As OleDb.OleDbConnection
Dim OLE_Command As OleDb.OleDbCommand
Dim OLE_DataAdapter As OleDb.OleDbDataAdapter
Dim OLE_DataTable As DataTable = Nothing
Dim OLE_DataSet As New DataSet

'== make sure file exists first

'If CheckFile(sFilePath & "\" & sFileName & ".dbf") = False Then
' MsgBox("File - " & sFilePath & "\" & sFileName & ".dbf is missing!", MsgBoxStyle.Exclamation)
' Return OLE_DataTable

'End If

Dim sConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source=" & sFilePath1

Try

OLE_CN = New OleDb.OleDbConnection(sConnect)
OLE_CN.Open()
OLE_Command = New OleDb.OleDbCommand(sQuery1, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)
OLE_DataAdapter.Fill(OLE_DataSet, sTableName1)

OLE_Command = New OleDb.OleDbCommand(sQuery2, OLE_CN)
OLE_DataAdapter = New OleDb.OleDbDataAdapter(OLE_Command)
OLE_DataAdapter.Fill(OLE_DataSet, sTableName2)

'== tried both ways, getting error
' Dim Tran_Detail As New DataRelation("ds", OLE_DataSet.Tables(sTableName1).Columns("Value"), OLE_DataSet.Tables(sTableName2).Columns("Value"))
' OLE_DataSet.Relations.Add(Tran_Detail) '"test", OLE_DataSet.Tables(sTableName1).Columns("Value"), OLE_DataSet.Tables(sTableName2).Columns("Value"))
OLE_DataSet.Relations.Add("ds", OLE_DataSet.Tables(sTableName1).Columns("Value"), OLE_DataSet.Tables(sTableName2).Columns("Value"))

Return OLE_DataSet.Tables(0)


Catch ex As Exception
MsgBox("ERROR: " & m_Current_Address & " - Get ADO Datatable - " & ex.Message)
Finally
If OLE_CN.State = ConnectionState.Open Then
OLE_CN.Close()
OLE_CN = Nothing
OLE_Command = Nothing
OLE_DataAdapter = Nothing
OLE_DataTable = Nothing
End If


End Try
End Function

thanks for help

[Jenner]

How's it blowing up? Looks good to me. What error is it giving?

[DebbieInFlorida]

Hi Jenner,


I got the error to go away, in case anyone else has this error...

This constraint cannot be enabled as not all values have corresponding parent values.

the problem was that I had the table with more records in the second query, so I guess the parent must have the most records.

BUT, now that I have the results DataTable, I checked the columns and there are only the columns from the first table and the same number of rows. I was expecting the same rows, but extra columns where the join would work??? How do I get data combined from both tables????? I used to be able to do this with standard ADO

thanks

Table1 has 51 rec, Table2 has 96 recs. I don't see how this should matter, if there isn't a match, then won't it place zeros in the result columns???

[DebbieInFlorida]

DUH! of course I'm only getting the columns from table1,
I return .TABLES(0)

how do I return the merged table???

[DebbieInFlorida]

OK, I figured it out. Holy cow, they couldn't make it more complicated!!!!

this is how you read back the merged tables, first put in "true" as option in the Relations.add statement

then to read the Datatable, follow this..

Dim ParentRow, ChildRow As DataRow
For Each ParentRow In pDT.Rows
Debug.Print("Parent - " & ParentRow(0).ToString() & " " & ParentRow(1).ToString())
For Each ChildRow In ParentRow.GetChildRows("ds")
Debug.Print(ChildRow(0).ToString() & " " & ChildRow(1).ToString())

Next
Next

Cheers