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

feesuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.