smithmrk
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(sCon nectionStr ing)
sSQL_UpdateStatement = New OleDb.OleDbCommand
sSQL_UpdateStatement.Conne ction = conn
sSQL_UpdateStatement.Comma ndTimeout = 0
sSQL_UpdateStatement.Comma ndType = CommandType.Text
conn.Open()
For Each drRow As DataRow In dtDepotData.Rows
sSQL_UpdateStatement.Comma ndText = "UPDATE tbl_Receivables SET exported = " & CType(sBatchNumber, Long) & _
" WHERE ClientID = " & ClientID & " AND ReceiveDate = #" & ReceiveDate.ToShortDateStr ing & "# 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.Param eters.AddW ithValue(" @acctnum1" , drRow.Item("Acct1"))
sSQL_UpdateStatement.Param eters.AddW ithValue(" @acctnum2" , drRow.Item("Acct2"))
sSQL_UpdateStatement.Param eters.AddW ithValue(" @acctnum3" , drRow.Item("Acct3"))
sSQL_UpdateStatement.Param eters.AddW ithValue(" @acctnum4" , drRow.Item("Acct4"))
sSQL_UpdateStatement.Param eters.AddW ithValue(" @acctnum5" , drRow.Item("Acct5"))
sSQL_UpdateStatement.Execu teNonQuery ()
sSQL_UpdateStatement.Param eters.Clea r()
Next
conn.Close()
conn.Dispose()
sSQL_UpdateStatement.Dispo se()
Dim sSQL_UpdateStatement As OleDb.OleDbCommand
Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon
sSQL_UpdateStatement = New OleDb.OleDbCommand
sSQL_UpdateStatement.Conne
sSQL_UpdateStatement.Comma
sSQL_UpdateStatement.Comma
conn.Open()
For Each drRow As DataRow In dtDepotData.Rows
sSQL_UpdateStatement.Comma
" WHERE ClientID = " & ClientID & " AND ReceiveDate = #" & ReceiveDate.ToShortDateStr
" AND Acct2 = @acctnum2 AND Acct3 = @acctnum3 AND Acct4 = @acctnum4 AND Acct5 = @acctnum5 " & _
" AND AmountPaid = " & drRow.Item("AmountPaid") & " AND Exported = 0"
sSQL_UpdateStatement.Param
sSQL_UpdateStatement.Param
sSQL_UpdateStatement.Param
sSQL_UpdateStatement.Param
sSQL_UpdateStatement.Param
sSQL_UpdateStatement.Execu
sSQL_UpdateStatement.Param
Next
conn.Close()
conn.Dispose()
sSQL_UpdateStatement.Dispo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER