multiple recordset and dataset question

[Support]

Hello:
I have a stored procedure that returns two record sets:
the first recordset returns two columns that have to do with internal error
handling processes
the second recordset returns a recodset containing multiple records
I would like to capture the second recordset into a datatable.......

Dim cn As New SqlConnection
cn.ConnectionString = GetConnectionString()
Dim cmd As New SqlCommand("Sproc_GenericGetDocTypes", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim p1 As SqlParameter = cmd.Parameters.Add("@intAPPLICATIONSunid",
SqlDbType.Int, 4)
p1.Value = Applicationid
cn.Open()
Dim stuff As SqlDataReader
stuff = cmd.ExecuteReader()
While stuff.Read
GetDocumentTypes.RETURNED_ERROR = stuff("ERROR")
GetDocumentTypes.RETURNED_Answer = stuff("ANSWER")
End While

' go fetch the next recordset
stuff.NextResult()

Dim myDA As SqlDataAdapter = New SqlDataAdapter

Dim MyDataTable As New DataTable("DOCTYPES")

If GetDocumentTypes.RETURNED_ERROR = "000" Then

myDA.Fill(GetDocumentTypes.RETURNED_Dataset .............
<================== what should I put here ?

GetDocumentTypes.RETURNED_ERROR = TheError.Source
GetDocumentTypes.RETURNED_Answer = TheError.Message
myDA.Dispose()

End If

Thanks

 

[Cor Ligthert [MVP]]

Support,

I see you not returning any recordset in your code question, what do you
mean?

Cor

 

[Support]

Ok - I agree - how about: forward-only stream of rows instead of dataset...
via SqlDatareader.
I think I need to return a datatable not a dataset
I will explore that possibility..
Thanks

 

[Cor Ligthert [MVP]]

Support,

You don't use the dataadapter at all, has that a reason.

(To try a stored procedure by the way is easier to set it in text first in
your program).
Using a stored procedure than is than only to change the command.text
property and removing the SP

Now it is impossible to see if you have created a joined resultset which
returns one table or a SP with two tables.

I hope this helps,

Cor