asked on

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

krunal_shah

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

RameshS

Why are you using DR.Read()?

Remove that line and try again. Also post the error that you are getting.

feesu

ASKER

krunal,

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

Invalid attempt to call NextResult when reader is closed.

krunal_shah

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

krunal_shah

your code should be in between,
objConn.Open()
...
objConn.Close()

where objConn is the object of your SqlConnection

feesu

ASKER

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

ASKER CERTIFIED SOLUTION

krunal_shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial