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

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"  schreef in bericht 
news:DFE56B5A-9795-4E18-99B8-C2F7829C1CCF@microsoft.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 

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"  schreef in bericht 
> news:DFE56B5A-9795-4E18-99B8-C2F7829C1CCF@microsoft.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 
> 

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")


"Peter"  schreef in bericht 
news:DFE56B5A-9795-4E18-99B8-C2F7829C1CCF@microsoft.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 

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;Persist 
> Security Info=False;User ID=sa;Password=pwd;Initial Catalog=tempdatabase")
> 
> 
> "Peter"  schreef in bericht 
> news:DFE56B5A-9795-4E18-99B8-C2F7829C1CCF@microsoft.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 
> 
> 
> 

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