Error: Invalid attempt to call Read when reader is closed

[Vayne]

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?

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:

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

[jfarrugia]

Somewhere you are closing the connection..a datareader shuts down once the connection terminates.. whats in :

ExecuteStatement

[crishlay]

first open Reader then execute sql query

[NETProgrammer]

Try this

If (con.State = ConnectionState.Open) Then
            con.Close()
        End If
        con.Open()

[VIPER5646]

I seam to be having the same problem.
I added the the code that NetProgramer suggested but I wasn't successful.
My error is given at the While m_Dr.read

This is My code.

query = "Select * From " & Tabela & " where Model= @Model" & " Order by model asc"

        Dim txtSource As String
        Dim ObjFile As New System.IO.StreamReader("C:\CWDEsign\CWDEsign.txt")
        txtSource = ObjFile.ReadToEnd()
        ObjFile.Close()
        ObjFile.Dispose()

        Dim sourse As String = "Data Source=" & txtSource & ";"
        Dim providr As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Dim MYcon = providr & sourse
        Dim con = New OleDb.OleDbConnection(MYcon)
        Dim cmd As New OleDb.OleDbCommand(query, con)
        With cmd.Parameters
            .AddWithValue("@Model", BModel)
        End With
        cmd.CommandType = CommandType.Text
        cmd.CommandText = query
        con.Open()
        Dim m_dr As OleDbDataReader

        m_dr = cmd.ExecuteReader
        m_dt.Load(m_dr)
        If (con.State = ConnectionState.Open) Then
            con.Close()
        End If
        con.Open()
             While m_dr.Read
            ModelBuilder_Relation.Add(m_dr.GetString(0))
                End While

[NETProgrammer]

VIPER5646 I suggest you to check all of your data readers and close them right after you have finished using them.
It's not a good programming practice to keep your data readers open even after you have
finished your task( Server load because more connections will be active)

Close any open reader and it will work fine.

Let us know how it goes, I am sure it will help you out.

Thanks

[khadakbist]

You need to close your datareader.

[VIPER5646]

Thank you all for your replies.

Based on the code I posted I closed it by using m_dr.close.
If that is correct than they are all closed and I still get the same error.

[VIPER5646]

Thanks all
I had forgotten to instantiate ModelBuilder_Relation.
At least I asumed I had it wright instead of Dim ModelBuilder_Relation as new Arraylist I had Dim ModelBuilder_Relation as Arraylist. Now it seams to be working

I cant seam to mark this thread as solved may be because I did not start this thread .