Problem with DataReader in loop

[Optimaximal]

I've wrote a program designed to loop through a text file containing email addresses and unsubscribe said email addresses in our CRM system.

I'm having a problem with the following code...

Do While obj_filereader2.Peek() <> -1
            strcuremail = obj_filereader2.ReadLine()
            Dim dbqCustNo As String = "SQL CODE HERE - SELECT CUSTOMER NUMBER"
            Dim exeCustNo As New SqlCommand()
            Dim SQLdr As SqlDataReader

            exeCustNo.Connection = ConnStr
            exeCustNo.CommandText = dbqCustNo
            SQLdr = exeCustNo.ExecuteReader

            If SQLdr.HasRows Then
                While SQLdr.Read()
                    strCustNo = Trim(SQLdr("customer"))
                    If strCustNo.Length > 0 Then
                        'If ConnStr.State = ConnectionState.Closed Then ConnStr.Open()
                        Dim updCustDefl As String = "SQL CODE HERE - UPDATE DEFAULTS"
                        Dim exeCustDefl As New SqlCommand(updCustDefl, ConnStr)
                        exeCustDefl.ExecuteNonQuery()

                        Dim updCustNote As String = "SQL CODE HERE - UPDATE NOTES"
                        Dim exeCustNote As New SqlCommand(updCustNote, ConnStr)
                        exeCustNote.ExecuteNonQuery()
                        strCustNo = ""
                        
                    End If
                    strUpdateCount += 1
                    Call Delay(2)
                End While

                SQLdr.Close()

                txtline = txtline & strcuremail & " - " & strUpdateCount & " records processed." & vbNewLine
                txtResults.Text = txtline
                txtResults.Select(txtResults.Text.Length - 1, 0)
                txtResults.ScrollToCaret()
                txtproccount += 1
                strUpdateCount = 0

            Else

                txtline = txtline & strcuremail & " - Skipped (Email not found)" & vbNewLine
                txtResults.Text = txtline
                txtResults.Select(txtResults.Text.Length - 1, 0)
                txtResults.ScrollToCaret()
                txtproccount += 1

                SQLdr.Close()

            End If


            tipRecordCount.Text = "Records Processed: " & txtproccount

        Loop

The program is falling over when trying to execute the update commands during the datareader loop, with the fabled 'There is already an open DataReader associated with this Command...' error.

This program has evolved as I've found problems/better ways of working, so it might be a case of myself just not using the DataAdapter right - I'm guessing I should be using a function of the open DataReader rather than the ExecuteNonQuery command. Heck, I could be doing the whole process backwards.

I'm new to .Net and I'm adjusting to the various commands and how they're used.

All help appreciated...

Before anyone offers it as a suggestion, it's SQL 2000, so the MultipleActiveResultSets tag in the connection string a lot of people suggest for the problem won't work.

[techgnome]

Using a reader on a connection causes that reader to obtain an exclusive lock on that connection... that means NOTHING ELSE can use that same connection. If you need a connection while inside your reader, you'll need to open a SECOND connection and use that for the remainder of the operations.

The reason for that lock is because the reader is a server-side activity, it gets data from the database on-demand, it's what makes it very fast (that and it's forward, read-only too).

-tg

[wild_bill]

I suggest refactoring a little bit. It is good practice to break your data access out of your UI, usually into a different class library.

    Private Sub ParseInputFile(ByVal filePath As String)

        'get all email addresses from file
        For Each emailAddress In IO.File.ReadAllLines(filePath)
            'get customer number for email address
            Dim customerNumber As String = SelectCustomerId(emailAddress)

            If customerNumber Is Nothing Then
                'TODO: stat tracking
            Else
                'Update Customer's Defaults 
                UpdateCustomerDefault(customerNumber, "somevalue")
                'Update Customer's Notes
                InsertCustomerNote(customerNumber, "Customer does not wish to recieve email")
                'TODO: stat tracking
            End If

        Next

    End Sub

    Public Function SelectCustomerId(ByVal emailAddress As String) As String

        Dim customerNumber As Object = Nothing
        Using Con As New SqlClient.SqlConnection("connection string")
            Con.Open()
            Using cmd As New SqlClient.SqlCommand("Select customerNumber from customerTable where email = @email", Con)
                cmd.Parameters.AddWithValue("@email", emailAddress)
                customerNumber = cmd.ExecuteScalar
            End Using
        End Using

        If customerNumber IsNot Nothing Then
            Return CStr(customerNumber)
        End If

        Return Nothing

    End Function

    Public Function UpdateCustomerDefault(ByVal customerNumber As String, ByVal defaultValue As String) As Integer

        Using Con As New SqlClient.SqlConnection("connection string")
            Con.Open()
            Using cmd As New SqlClient.SqlCommand("Update customerTable set default = @default where customerNumber = @customerNumber", Con)
                cmd.Parameters.AddWithValue("@customerNumber", customerNumber)
                cmd.Parameters.AddWithValue("@default", defaultValue)
                Return cmd.ExecuteNonQuery
            End Using
        End Using

    End Function

    Public Function InsertCustomerNote(ByVal customerNumber As String, ByVal message As String) As Integer

        Using Con As New SqlClient.SqlConnection("connection string")
            Con.Open()
            Using cmd As New SqlClient.SqlCommand("INSERT INTO customerNote (customerNumber,message) Values(@customerNumber,@message)", Con)
                cmd.Parameters.AddWithValue("@customerNumber", customerNumber)
                cmd.Parameters.AddWithValue("@message", message)
                Return cmd.ExecuteNonQuery
            End Using
        End Using

    End Function