Link to home
Start Free TrialLog in
Avatar of smithmrk
smithmrkFlag for United States of America

asked on

VB.net - Update Access Database (System Resources Exceeded) Error

I'm looping through 4000 rows to update the Access Database, but I keep getting "System Resources Exceeded" Error:

                Dim sSQL_UpdateStatement As OleDb.OleDbCommand
                Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sConnectionString)

                sSQL_UpdateStatement = New OleDb.OleDbCommand
                sSQL_UpdateStatement.Connection = conn
                sSQL_UpdateStatement.CommandTimeout = 0
                sSQL_UpdateStatement.CommandType = CommandType.Text
                conn.Open()

                For Each drRow As DataRow In dtDepotData.Rows
                    sSQL_UpdateStatement.CommandText = "UPDATE tbl_Receivables SET exported = " & CType(sBatchNumber, Long) & _
                        " WHERE ClientID = " & ClientID & " AND ReceiveDate = #" & ReceiveDate.ToShortDateString & "# AND Acct1 = @acctnum1" & _
                        " AND Acct2 = @acctnum2 AND Acct3 = @acctnum3 AND Acct4 = @acctnum4 AND Acct5 = @acctnum5 " & _
                        " AND AmountPaid = " & drRow.Item("AmountPaid") & " AND Exported = 0"

                    sSQL_UpdateStatement.Parameters.AddWithValue("@acctnum1", drRow.Item("Acct1"))
                    sSQL_UpdateStatement.Parameters.AddWithValue("@acctnum2", drRow.Item("Acct2"))
                    sSQL_UpdateStatement.Parameters.AddWithValue("@acctnum3", drRow.Item("Acct3"))
                    sSQL_UpdateStatement.Parameters.AddWithValue("@acctnum4", drRow.Item("Acct4"))
                    sSQL_UpdateStatement.Parameters.AddWithValue("@acctnum5", drRow.Item("Acct5"))

                    sSQL_UpdateStatement.ExecuteNonQuery()
                    sSQL_UpdateStatement.Parameters.Clear()
                Next
                conn.Close()
                conn.Dispose()
                sSQL_UpdateStatement.Dispose()
ASKER CERTIFIED SOLUTION
Avatar of A.E. Veltstra
A.E. Veltstra
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of smithmrk

ASKER

Thanks