Error: Invalid attempt to call Read when reader is closed

Vayne · June 3rd, 2011, 04:59 AM

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

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

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

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*