How to use two nested dataset w/ only one connection

  • Thread starter Thread starter Damian Franco
  • Start date Start date
D

Damian Franco

Can anyone tell me the best way to do this?

I just want to go through a dataset and as I read I want to run
another store procedure using some of the data from datset1.

I don't know how to do it using dataset and I cant use datareader bec.
only one connection can be open at a time.

Help !!!

example:

Dim order_id as Integer = 1254
Dim myDataReader1 As SqlDataReader
Dim myDataReader2 As SqlDataReader

Dim myConnection As SqlConnection = New SqlConnection...

Dim myCommand1 As SqlCommand = New SqlCommand("s_sp1", myConnection)
myCommand1.CommandType = CommandType.StoredProcedure
myCommand1.Parameters.Add("@Order_id", order_id)

Try
myConnection.Open()
myDataReader1 = myCommand1.ExecuteReader()

While myDataReader1.Read()
....
Cat_Id = myDataReader1("category_id")

Dim myCommand2 As SqlCommand = New SqlCommand("s_sp2",
myConnection)
myCommand2.Parameters.Add("@cat_id", cat_id)
myDataReader2 = myCommand2.ExecuteReader()
if myDataReader2.Read Then
...
end if
myDataReader2.Close
End While
Catch sql_exce As SqlException
....
Finally
myConnection.Close()
myDataReader1.Close
End Try
 
Hello Damian

You can use the same connection for as many DataSets or DataTables as you
need.

Use the same connect object for both command objects, then do your stuff :-D

Close the connection after all data operations are finished

Ibrahim
 
You can update using a command object rather than a DataSet. It just has to
use a different connection than the one that has the DataReader open.

Create myConnection2 at the start of your code, and create myCommand2 like
so:

Dim myCommand2 As SqlCommand = New SqlCommand("s_sp2",myConnection2);

--Bob
 
I think you may be making more trouble for yourself than you need to. Try to
write a single SP that executes a "smart" update that precludes the need to
drag the first DataSet rowset to the client to drive the second SP.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Damian,
You are not using a dataset but the datareader.
\\\\
A dataset is a datatable, that you read and update using the same SQL
connection but then for only reading something as (the easiest way to update
is adding the SQLcommandbuilder)
Dim ds1 As New DataSet
Dim ds2 as New Dataset
da1 = New SqlDataAdapter(mycommand1) 'Select command
da2 = new sqldataAdapter(mycommand2) 'Select command
da1.Fill(ds1)
da2.fill(ds2)
///
I hope this give you the idea?

Cor
 
Back
Top