Multiple Resultsets and the Dataset

[Matthew Dill]

I have two resultsets that I'm returning via a command object. When I use
the data adapter to 'fill' the dataset I specify the srcTable name. Since
there are multiple resultsets the name is incremented for the second table
(I end up with CarrierData and CarrierData1. I'm getting all the data I
want and can deal with it accordingly. However, I would like to control the
names of the second srcTable in the Dataset. How do I do this?

This is the current code (minus error trapping). Any help would be
appreciated.

Public Function Fetch(ByVal vlngCarrierID As Long) As DataSet



'Create Command object

Dim lobjCommand As New SqlClient.SqlCommand("SELECT * FROM vwCarrier
WHERE CarrierID = " & vlngCarrierID & _

"SELECT * FROM vwNote
WHERE OrigEntityID = 1 AND OrigRefID = " &
vlngCarrierID, mobjSQLConn)



'Create Data Adapter

Dim lobjDataAdapter As New SqlClient.SqlDataAdapter(lobjCommand)



'Create an empty Dataset

Dim lobjDataSet As New DataSet



'Use the DataAdapter to create & fill a new DataTable called
'ListData'

lobjDataAdapter.Fill(lobjDataSet, "CarrierData")



*** What I would like is two Tables one called "CarrierData" and the
other call "CarrierNotes" ***



Return lobjDataSet



End Function

 

[Kathleen Dollard]

Mathew,

lobjDataSet.Tables(0).TableName = "CarrierData"
lobjDataSet.Tables(1).TableName = "CarrierNotes"

If it is more convenient for you to do this ahead of time - look into
TableMappings which allow this step to be done as part of the Fill.

 

[Matthew Dill]

Kathleen,

Thanks for pointing me in the right direction. I add a TableMapping to
the DataAdapter and that lets me control the second srcTable name. Thanks.

Public Function Fetch(ByVal vlngCarrierID As Long) As DataSet



'Create Command object

Dim lobjCommand As New SqlClient.SqlCommand("SELECT * FROM vwCarrier
WHERE CarrierID = " & vlngCarrierID & _

"SELECT * FROM vwNote
WHERE OrigEntityID = 1 AND OrigRefID = " &
vlngCarrierID, mobjSQLConn)



'Create Data Adapter

Dim lobjDataAdapter As New SqlClient.SqlDataAdapter(lobjCommand)



'Create an empty Dataset

Dim lobjDataSet As New DataSet



*** Solution *** 'Map Notes data to CarrierNotes

lobjDataAdapter.TableMappings.Add("CarrierData1", "CarrierNotes")



'Use the DataAdapter to create & fill a new DataTable called
'ListData'

lobjDataAdapter.Fill(lobjDataSet, "CarrierData")



Return lobjDataSet



End Function