Reply

Member

54 Points

Error with DataReader associated with Command

Jul 26, 2018 01:42 PM|vstorpedo|LINK

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.

Reply

mudassarkhan

All-Star

54478 Points

14108 Posts

Re: Error with DataReader associated with Command

Jul 26, 2018 02:01 PM|mudassarkhan|LINK

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()

.Net Consultant | MVP[2009-2013] | MCC[2012]
Blog: ASPSnippets | Forum: ASPForums | Company: Excelasoft

Reply

mgebhard

All-Star

40881 Points

17121 Posts

Re: Error with DataReader associated with Command

Jul 26, 2018 02:02 PM|mgebhard|LINK

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.