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