Returning multiple resultsets usind datadreader

[=?Utf-8?B?UGV0ZXI=?=]

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

"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[=?Utf-8?B?UGV0ZXI=?=]

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

"Cor Ligthert[MVP]" wrote:

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

"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[Michel Posseth [MCP]]

Hello Peter ,

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

Dim objConn As New SqlConnection("MultipleActiveResultSets=True;Persi st
Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")
"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[=?Utf-8?B?UGV0ZXI=?=]

Hi Michel,

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

Thanks,

Peter

"Michel Posseth [MCP]" wrote:

Hello Peter ,

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

Dim objConn As New SqlConnection("MultipleActiveResultSets=True;Persi st
Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")
"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

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

"Peter" wrote:

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

[=?Utf-8?B?UGV0ZXI=?=]

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

"Michel Posseth [MCP]" wrote:

Hello Peter ,

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

Dim objConn As New SqlConnection("MultipleActiveResultSets=True;Persi st
Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")
"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[=?Utf-8?B?UGV0ZXI=?=]

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

"Kerry Moorman" wrote:

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

"Peter" wrote:

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

[=?Utf-8?B?UGV0ZXI=?=]

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

"Michel Posseth [MCP]" wrote:

Hello Peter ,

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

Dim objConn As New SqlConnection("MultipleActiveResultSets=True;Persi st
Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")
"Peter" <Pe***@discussions.microsoft.comschreef in bericht
news:DF**********************************@microsof t.com...

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

[=?Utf-8?B?UGV0ZXI=?=]

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

"Kerry Moorman" wrote:

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

"Peter" wrote:

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

[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.

On Thu, 23 Aug 2007 11:28:02 -0700, Peter
<Pe***@discussions.microsoft.comwrote:

>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

"Kerry Moorman" wrote:

>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

"Peter" wrote:

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