Returning multiple resultsets usind datadreader

[Guest]

I'm using this coding to get 2 resultsets thru datareader and then load them
into 2 datatables and bind the datatables to datagridviews. But
sdrGrid.NextResult() is returning false for some reason. Is that possible
the connection is closed?

Dim strConn As String = "Server=localhost;Database=northwind;" + _
"Integrated Security=SSPI"
Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
Try
Dim strSql As String = "select * from customers;select * from
products"
Dim cmdGrid As New SqlCommand(strSql, cnnNwind)
cmdGrid.CommandType = CommandType.Text
cnnNwind.Open()
Dim sdrGrid As SqlDataReader = cmdGrid.ExecuteReader
Dim dt1 As New DataTable
dt1.Load(sdrGrid)
DGVP.DataSource = dt1
Dim dt2 As New DataTable
If sdrGrid.NextResult() = True Then
dt2.Load(sdrGrid)
DGVC.DataSource = dt2
End If
Catch exc As Exception
MsgBox(exc.Message)
Finally
cnnNwind.Close()
End Try

 

[Cor Ligthert[MVP]]

Peter,

Can you explain me why you use this method, as far as I can see it now, it
is going from NY to Wasinton to Jersey City over LA.

Normally we use direct the DataAdapter Fill, however there should be a
reason?
It cannot be that it is quicker, because that is AFAIK culprit, you have to
write at least more code while the result is the same, although you need
probably after what you do now at least an acceptchanges of course, which is
automaticly done by the fill.

However, there must be a reason and I am currious about that?

Cor

 

[Guest]

Hi Cor,

I'm just trying different ways to retrieve data: datareader, dataadapter,
tableadapter, DAAB , and LINQ.

One of the objectives is to retrieve master-details resultsets and populate
them into datagridviews for read-only purpose. This probably will be
implemented as a class for generic usage.

Peter

 

[Michel Posseth [MCP]]

Hello Peter ,

did you set the MARS parameter to true in the connection string ?

Dim objConn As New SqlConnection("MultipleActiveResultSets=True;Persist
Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")

 

[Guest]

Hi Michel,

I guess this may be the problem. Will need to try it tomorrow.

Thanks,

Peter

 

[Guest]

Peter,

If you change this:

If sdrGrid.NextResult() = True Then
dt2.Load(sdrGrid)
DGVC.DataSource = dt2
End If

to this:

dt2.Load(sdrGrid)
DGVC.DataSource = dt2

then I think it will work.

Kerry Moorman

 

[Guest]

Hi Michel,

I have tried setting this to true or false and has no effect. I'm
connecting to SQL Server 2005 and I believe MARS is true by default.

The coding suddenly works after I removed the If statement from the
following coding:

Original coding:
If sdrGrid.NextResult() = True Then
dt2.Load(sdrGrid)
DGVC.DataSource = dt2
End If

Revised coding:
dt2.Load(sdrGrid)
DGVC.DataSource = dt2

This is kind of confusing since I think I'm getting multiple resultsets and
need to the NextResult method to move to next resultset. With the revised
coding, I have tried both MARS=true and MARS=fales and it works on both
settings.


Peter

 

[Guest]

Hi Kerry,

It works but I have no idea why. I believe my coding was originally
without the If sdrGrid.NextResult() = True and it didn't work. So, I tried
the NextResult coding but still not working.


Peter

 

[Guest]

Hi Michel,

I think my case is not multiple ACTIVE resultsets. My case is simply a
batch which return multiple resultsets. I have not found any articles
explaining the difference between them. But the MARS parameter will only
have effect if I try to use 2 datareaders without closing the 1st datareader.


Peter

 

[Guest]

Hi Kerry,

I think I find out the reason why NextResult is not needed. The
datatable.load will position to the next resultset automatically.


Peter

 

[Jack Jackson]

It appears that DataTable.Load() implicitly calls
DataReader.NextResult(). I found a couple of references to this with
a Google search.

You can also use DataAdapter.Fill() to put all of the result sets into
one DataSet.

 

[Cor Ligthert[MVP]]

Peter,

I never used it, however as I see the documentation than the load is using
the reader, however does not give you access to that. In my idea is it
another way of getting to the part of the fill without things as the
standard acceptchanges what is in the fill.

You are as well not using the reader way to fill the properties/fields.

However, I did not try it.

Cor