Have to loop through one data reader and check another data reader at the same time but get error!

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Please note image attachment below of error highlighting line: Dim reader2 As SqlDataReader = command2.ExecuteReader().

I get error message of there is already an open DataReader associated with this command which must be closed first.

I really only have command not command2 opened - so I don't know why the message.  Probably saying I can't have two datareaders opened at the same time.  I don't want to know why this is happening but rather how may I rewrite the code so that this can work?

Basically I have a base table (AllPrograms) full of programs' full path names to check to see if they exist on a person's machine in a log table (LogProgramID).  If it exists already then merely check the datemodified field and make sure its the same - if not then get it updated.


 Dim command As SqlCommand
        Dim command2 As SqlCommand
        Dim command3 As SqlCommand

        Using connection As New SqlConnection("Server=EC-SQL;uid=FKPTracker;pwd=goaway;database=FKPTracker")

            connection.Open()

sSQL = "SELECT [ProgramID], [ProgramFullName]"
            sSQL = sSQL & " FROM [AllPrograms]"
            sSQL = sSQL & " WHERE [ProgramMonitor] = 1"
            sSQL = sSQL & " ORDER BY [ProgramFullName]"
            command = New SqlCommand(sSQL, connection)
            reader = command.ExecuteReader()
            While reader.Read()

                lProgramID = reader(0).ToString
                sProgram = reader(1).ToString
                sDateModifiedNet = sProgram

                If File.Exists(sProgram) = True Then

                    sSQL = "SELECT [AuditID], [ModifiedDate] FROM [LogProgramID] WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                    command2 = New SqlCommand(sSQL, connection)
                    Dim reader2 As SqlDataReader = command2.ExecuteReader()
                    If reader2.HasRows Then
                        reader2.Read()
                        sDateModified = reader2.GetString(reader.GetOrdinal("ModifiedDate"))

                        If sDateModified <> sDateModifiedNet Then

                            sSQL = "UPDATE [LogProgramID] SET [DateModified] = '" & sDateModifiedNet & "'"
                            sSQL = sSQL & " WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                            command3 = New SqlCommand(sSQL, connection)
                            command3.ExecuteNonQuery()
                            command3 = Nothing

                        End If


                    Else

                        sSQL = "INSERT INTO [LogProgramID] ([ProgramID], [CPUID], [DateModified]) VALUES (" & lProgramID & ", " & lCPUID & ", " & sDateModifiedNet & ")"
                        command3 = New SqlCommand(sSQL, connection)
                        command3.ExecuteNonQuery()
                        command3 = Nothing


                    End If
                    reader2.Close()
                    command2 = Nothing
                    reader2 = Nothing

                Else

                    ''remove the program from LogProgram table...

                    'sSQL = "UPDATE LogProgram SET [ItExists] = 0 WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                    'command = New SqlCommand(sSQL, connection)
                    'Try
                    '    command.ExecuteNonQuery()
                    'Catch ex As Exception
                    '    ErrorMessageString = ex.ToString & " - " & ex.Message
                    '    Call ExceptionClassObject.WriteExceptionErrorToFile(ExceptionErrorFileString, Me.Name, sMsg, ErrorMessageString, WriteErrorMessageString)
                    'End Try

                    'command = Nothing
                End If

            End While
            reader.Close()
            connection.Close()

Open in new window

error-ee.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The trouble is that the datareader class maintains an open connection to the database.  This connection cannot be shared.  So you would have to declare and use another connection object or use a datatable or other disconnected object for your queries.


See:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Author

Commented:
I need someone's help with rearranging the syntax of the code above to make it work please!
just enable mars in connection string and rest will be fine...

Commented:
I would still tend to advise instead that you use a disconnected model like a datatable to loop through, as consuming a connection the entire time you are processing updates is expensive.  If you start to get connection timeouts on your server, you might consider revisiting this.  But if that's working for you, good.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial