Joining Tables in Dataset

[Lenvdb]

Hi Gurus!
I have a problem creating Relations in a Dataset holding 3 tables. When I attempt it I get the error that says:
"Object reference not set to an instance of an object"

I have a SQL statement using an SQL2K Stored Proc which returns 3 tables.

I need to display the resultsets in a Grid where there are Parent/Child rows.

So the Dataset returns the rows fine. Setting up the relations is where the problem occurs.

Does anyone have a solution for me?

This is my Code:

Private Sub FillGrid()
Dim clsData As clsDataclass
Dim sSQL As String = "up_ClientSearchLists"
Dim dsClientList As New DataSet
Dim daClientList As SqlClient.SqlDataAdapter

Try
'First Set the Data Adapter
daClientList = New SqlClient.SqlDataAdapter

clsData = New clsDataclass
clsData.GetDataset_withOpenCNN(sSQL, dsClientList, daClientList)

daClientList.TableMappings.Add("Clients", "Clients") 'Holds 3 tables
daClientList.TableMappings.Add("ClientProgs", "ClientProgs")
daClientList.TableMappings.Add("JobOutcomes", "JobOutcomes")

'This is where the error is generated....I follow all the examples and still get the error!

dsClientList.Relations.Add("ClientProgs", dsClientList.Tables.Item("Clients").Columns("pkClientID"), dsClientList.Tables.Item("ClientProgs").Columns("fkClientID"))
dsClientList.Relations.Add("JobOutcome", dsClientList.Tables.Item("ClientProgs").Columns("pkClientProgramID"), dsClientList.Tables.Item("JobOutcomes").Columns("fkClientProgramID"))

lstClients.DataSource = dsClientList.Tables.Item("Clients")
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Loading List", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
clsHelp = Nothing
cCNN_Open = Nothing
clsData = Nothing
End Try

End Sub

Public Sub GetDataset_withOpenCNN(ByVal sSQL As String, ByVal dsData As DataSet, ByVal daData As SqlClient.SqlDataAdapter)
Dim cmdSQL As New SqlClient.SqlCommand

Try
cCNN_Open = CNN_Open
cmdSQL.CommandType = CommandType.Text
cmdSQL.CommandText = sSQL
cmdSQL.Connection = cCNN_Open
daData.SelectCommand = cmdSQL
cCNN_Open.Open()
daData.Fill(dsData)

Catch ex As Exception
MessageBox.Show(ex.Message, "Get Dataset Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try


End Sub


Private ReadOnly Property CNN_Open() As SqlClient.SqlConnection

Get
Dim sCNN As String
' If the connection string is null, use a default.
If CNN_Open Is Nothing Then
'sCNN = "Initial Catalog=NDEnterprise;Data Source=NDE;Integrated Security=SSPI;"
sCNN = "Initial Catalog=NDEnterprise;Data Source=DEVELOPMENT;Integrated Security=SSPI;"

CNN_Open = New SqlClient.SqlConnection
CNN_Open.ConnectionString = sCNN
End If
cCNN_Open = CNN_Open
End Get

End Property


If someone could tell me what I am doing wrong......I follow samples of the code and yet my code keeps failing!

Rgds
Len

Quiet as a bulldozer Mate!