p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 4 General Discussion (http://p2p.wrox.com/forumdisplay.php?f=561)
-   -   Error: Invalid attempt to call Read when reader is closed (http://p2p.wrox.com/showthread.php?t=83870)

Vayne June 3rd, 2011 04:59 AM

Error: Invalid attempt to call Read when reader is closed
 
Hi,

I have this sub which performs updates in successive function call. The reader object is closed only when the operation has been completed (after the while loop has ended), yet I get this error. Any suggestions?

Code:

        sub reset_slno()
               
                dim objCmd2 as new OleDBCommand()
                dim objReader2 as OleDBDataReader
                objCmd2.Connection = Conn
                objCmd2.CommandText = "SELECT * FROM tbl_civil_works"
                       
                try
                        objCmd2.Connection.Open()
                        objReader2 = objCmd2.ExecuteReader
                catch ex as exception
                        lblMessage.Text = ex.Message
                end try
               
                dim strSQL3 as String
                dim record_counter as Integer = 1
                               
                while objReader2.Read()
                        strSQL3 = "UPDATE tbl_civil_works SET codeno = " & record_counter
                        ExecuteStatement(strSQL3)       
                        record_counter = record_counter + 1
                end while

                objReader2.Close()
                objCmd2.Connection.Close()               
               
        end sub

Error details:

Quote:

Source Error:


Line 227: dim record_counter as Integer = 1
Line 228:
Line 229: while objReader2.Read()
Line 230: dim strSQL3 as String = "UPDATE tbl_civil_works SET codeno = " & record_counter
Line 231: ExecuteStatement(strSQL3)

Source File: i:\inetpub\wwwroot\ecsite\civilworks1.aspx Line: 229

om_prakash June 3rd, 2011 05:43 AM

Try this:
Code:

If objReader2.HasRows Then
    Do While objReader2.Read()
 strSQL3 = "UPDATE tbl_civil_works SET codeno = " & record_counter
 ExecuteStatement(strSQL3)
 record_counter = record_counter + 1
    Loop
Else
    Console.WriteLine("No rows found.")
End If


Imar June 3rd, 2011 05:55 AM

And what's the error you're getting?

Imar

Vayne June 3rd, 2011 07:43 AM

Quote:

Originally Posted by om_prakash (Post 272776)
Try this:
Code:

If objReader2.HasRows Then
    Do While objReader2.Read()
 strSQL3 = "UPDATE tbl_civil_works SET codeno = " & record_counter
 ExecuteStatement(strSQL3)
 record_counter = record_counter + 1
    Loop
Else
    Console.WriteLine("No rows found.")
End If


Still the same error message.

Vayne June 3rd, 2011 07:44 AM

Quote:

Originally Posted by Imar (Post 272780)
And what's the error you're getting?

Imar

Error: Invalid attempt to call Read when reader is closed

Its in the thread title. :)

Imar June 3rd, 2011 07:48 AM

Is ExecuteStatement using the same connection? if so, this is not support out of the box. By default, a SqlDataReader requires its own, dedicated open connection to the database. So, when ExecuteStatement excutes, it messes up the connection used by the SqlDatareader.

Two solutions:

1. use a dedicated Connection objct for the SqlDataReader

2. Enable MARS (Multiple Active Result Sets) so multiple readers / data access code can exist on the same connection. Google has examples of enabling MARS.

Cheers,

Imar

Vayne June 3rd, 2011 08:22 AM

Quote:

Originally Posted by Imar (Post 272784)
Is ExecuteStatement using the same connection? if so, this is not support out of the box. By default, a SqlDataReader requires its own, dedicated open connection to the database. So, when ExecuteStatement excutes, it messes up the connection used by the SqlDatareader.

Two solutions:

1. use a dedicated Connection objct for the SqlDataReader

2. Enable MARS (Multiple Active Result Sets) so multiple readers / data access code can exist on the same connection. Google has examples of enabling MARS.

Cheers,

Imar

Thanks for the reply Imar!

The ExecuteStatement function is using a different connection and is only meant to execute nonquery. I used to it to insert data and also update data (however update is not working and for which very reason I am using this function).

And right now I am using Access database.

Vayne June 3rd, 2011 08:48 AM

This is the ExecuteStatement function:

Code:

        function ExecuteStatement(strSQL as String)
               
                dim objCmd as new OleDBCommand(strSQL, Conn)

                Response.Write(strSQL)               
               
                try
                        objCmd.Connection.Open()
                        objCmd.ExecuteNonQuery
                catch ex as exception
                        lblMessage.Text &= ex.Message
                end try

                objCmd.Connection.Close()                       
       
        end function


Vayne June 3rd, 2011 09:27 AM

Woops! My bad, I am actually using a single connection for both, it was global. I have rectified it now. Thanks!

Vayne June 3rd, 2011 09:32 AM

*I am just not being insightful*


All times are GMT -4. The time now is 03:12 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.