locked
Error with DataReader associated with Command RRS feed

  • Question

  • User426001450 posted

    I do have the following in code behind:

    cmd = New SqlCommand("SELECT UserName FROM ............... sql go here ....", con)
                    cmd.Parameters.AddWithValue("@IDAutoNumber", AutoNumber)
                    con.Open()
                    reader = cmd.ExecuteReader()
                    cmd.Parameters.Clear()
    
                    If reader.HasRows Then
                        While reader.Read()
    
                            'Here we get UserName from each record read
                            NewReadUserName = CType(reader("UserName"), Object).ToString
    
                            'Here we update table for particular UserName
                            cmd = New SqlCommand("UPDATE RegistrationList .............sql go here .........", con)
                            cmd.Parameters.AddWithValue("@AutoNumber", AutoNumber)
                            cmd.Parameters.AddWithValue("@NewReadUserName", NewReadUserName)
                            If NumberOfRecordsFound = 1 Then
                                cmd.Parameters.AddWithValue("@WordToEnter", "Found one")
                            ElseIf NumberOfRecordsFound > 1 Then
                                cmd.Parameters.AddWithValue("@WordToEnter", "Found more than one")
                            End If
                            cmd.ExecuteNonQuery()
                            cmd.Parameters.Clear()
    
                        End While
    
                    End If
    
                    reader.Close()
                    con.Close()

    I'm receiving this message when I try to access the page: 

     

    Server Error in '/' Application.


    There is already an open DataReader associated with this Command which must be closed first.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

    Source Error: 

    Line 164:                            cmd.Parameters.AddWithValue("@WordToEnterForFirstPlace", "TieFirstPlaceWinner")
    Line 165:                        End If
    Line 166:                        cmd.ExecuteNonQuery()
    Line 167:                        cmd.Parameters.Clear()
    Line 168:


    Source File: C:\bGlocal\TalentShow\AdminShowsVotingResultPage.aspx    Line: 166 

    Stack Trace: 

    [InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.]
       System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) +1543253
       System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +101
       System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +268
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +96
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +192
       ASP.talentshow_adminshowsvotingresultpage_aspx.Page_Load(Object sender, EventArgs e) in C:\bGlocal\TalentShow\AdminShowsVotingResultPage.aspx:166
       System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
       System.Web.UI.Control.OnLoad(EventArgs e) +95
       System.Web.UI.Control.LoadRecursive() +59
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2952
    

    I can not close the reader before it start reading. What should I do here?

    Thanks to all.

    Thursday, July 26, 2018 1:42 PM

Answers

  • User475983607 posted

    As the error indicates you cannot invoke a query on the same connection the reader is using to fetch records.  Open a new connection to handle the second query or let the reader finish then iterate over the result.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 2:02 PM

All replies

  • User-1171043462 posted

    Since cmd is already used by SQLDataReader, hence you cannot use it without closing DataReader.

    Hence solution is to create a different object cmd2

     cmd = New SqlCommand("SELECT UserName FROM ............... sql go here ....", con)
            cmd.Parameters.AddWithValue("@IDAutoNumber", AutoNumber)
            con.Open()
            reader = cmd.ExecuteReader()
            cmd.Parameters.Clear()
    
            If reader.HasRows Then
                While reader.Read()
    
                    'Here we get UserName from each record read
                    NewReadUserName = CType(reader("UserName"), Object).ToString
    
                    'Here we update table for particular UserName
                    Using cmd2 As New SqlCommand("UPDATE RegistrationList .............sql go here .........", con)
                        cmd2.Parameters.AddWithValue("@AutoNumber", AutoNumber)
                        cmd2.Parameters.AddWithValue("@NewReadUserName", NewReadUserName)
                        If NumberOfRecordsFound = 1 Then
                            cmd2.Parameters.AddWithValue("@WordToEnter", "Found one")
                        ElseIf NumberOfRecordsFound > 1 Then
                            cmd2.Parameters.AddWithValue("@WordToEnter", "Found more than one")
                        End If
                        cmd2.ExecuteNonQuery()
                        cmd2.Parameters.Clear()
                    End Using
                End While
    
            End If
    
            reader.Close()
            con.Close()

    Thursday, July 26, 2018 2:01 PM
  • User475983607 posted

    As the error indicates you cannot invoke a query on the same connection the reader is using to fetch records.  Open a new connection to handle the second query or let the reader finish then iterate over the result.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 2:02 PM
  • User-1171043462 posted

    Yes. I did not open a new Connection. That is a mistake. NP

    Thursday, July 26, 2018 4:26 PM