• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

ADO.NET - sqlDataReader.NextResult()

Hi Experts,

I have got an sqlDataReader DR that holds several result sets.

I wish to bind each result set to a different grid.

I used the below but I am getting errors. I am sure I did something wrong:


With grd_1
            .DataSource = DR
        End With

        With grd_2
            DR.NextResult()
            DR.Read()
            .DataSource = DR
        End With

Open in new window

0
feesu
Asked:
feesu
  • 4
  • 2
1 Solution
 
krunal_shahCommented:
what error you are getting?
 try the below code
DR= objCmd.ExecuteReader()

With grd_1 
            .DataSource = DR 
              .DataBind()

        End With 
   DR.NextResult()
        With grd_2 
              .DataSource = DR 
             .DataBind()

        End With

Open in new window

0
 
RameshSCommented:
Why are you using DR.Read()?

Remove that line and try again. Also post the error that you are getting.
0
 
feesuAuthor Commented:
krunal,

I did what you said and got the following error on the NextResult line:


Invalid attempt to call NextResult when reader is closed.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
krunal_shahCommented:
k.. your salconnection needs to be open while you are reading the datareader,
refer this link for details,
http://www.dotnetjohn.com/articles.aspx?articleid=23
0
 
krunal_shahCommented:
your code should be in between,
objConn.Open()
...
objConn.Close()


where objConn is the object of your SqlConnection
0
 
feesuAuthor Commented:
Find below the function that returns my DR from the stored procedure, what exactly should be changed?


    Public Function lid_report_weekly_get(ByVal valueDate As Date) As SqlDataReader

        Dim CN As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SiteSqlServer").ToString)

        Dim CMD As New SqlCommand("lid_report_weekly_get", CN)

        CMD.CommandType = CommandType.StoredProcedure

        Dim P As New SqlParameter

        P = New SqlParameter("@today", SqlDbType.DateTime)
        P.Value = valueDate
        CMD.Parameters.Add(P)

        CN.Open()
        Dim DR As SqlDataReader = CMD.ExecuteReader(CommandBehavior.CloseConnection)

        Return DR

    End Function
0
 
krunal_shahCommented:
try this,

 Public Function lid_report_weekly_get(ByVal valueDate As Date) As SqlDataReader

        Dim CN As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SiteSqlServer").ToString)

        Dim CMD As New SqlCommand("lid_report_weekly_get", CN)

        CMD.CommandType = CommandType.StoredProcedure

        Dim P As New SqlParameter

        P = New SqlParameter("@today", SqlDbType.DateTime)
        P.Value = valueDate
        CMD.Parameters.Add(P)

        CN.Open()
        Dim DR As SqlDataReader = CMD.ExecuteReader()

        Return DR

    End Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now