September 22, 2009 at 4:15 pm
In Sql Server 2005, I have a stored procedure with three select statements. When I execute the sp I get back three tables.
These three tables need to go back to the client all at once and be placed in a dataset.
How can I do this.
I tried:
Dim sql As String = "sp_GetFamily"
Dim str As New SqlConnection
str.ConnectionString = ConfigurationManager.ConnectionStrings("OneFamConnect").ConnectionString
Dim cmd As New SqlCommand
With cmd
.Connection = str
.CommandText = sql
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("profileid", SqlDbType.Int)
.Value = id
End With
With .Parameters.Add("fid", SqlDbType.Int)
.Value = fid
End With
With .Parameters.Add("mmid", SqlDbType.Int)
.Value = mmid
End With
.Connection.Open()
.ExecuteNonQuery()
If .Connection.State = ConnectionState.Open Then
.Connection.Close()
End If
End With
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds.Tables(0))
da.Fill(ds.Tables(1))
da.Fill(ds.Tables(2))
da.Dispose()
ds.Dispose()
str.Close()
str.Dispose()
cmd.Dispose()
However, I get an error.
Also, how do I handle the result sets when one of the tables come back with nothing?
Any help will be appreciated.
September 22, 2009 at 4:59 pm
instead of
da.Fill(ds.Tables(0))
da.Fill(ds.Tables(1))
da.Fill(ds.Tables(2))
use this
da.fill(ds)
the dataadapter fills the dataset with the resultset of your stored procedure , if your stored procedure returns three resultsets, then the dataadapter will fill your dataset with three tables.
September 22, 2009 at 6:31 pm
Are they three queries that generate different structures and you want all three in one resultset? Or three seperate queries to go in three seperate resultsets.. If it is the later then the code already posted should work.. For the former you should look at the UNION operator..
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply