Ms Access - 系统资源超出插入行 [英] Ms Access - System resource exceeded inserting rows

问题描述

我想从vb.net datagridview向Ms访问数据库插入1500行。

I want to insert 1500 rows to Ms access database from vb.net datagridview.

插入最多400行没有问题，但是超过400行显示错误 - 超出系统资源

Inserting up to 400 rows no issue, but above 400 rows its showing error - System resource exceeded.

我使用下面的代码。错误是突出显示：

Im using below code. The error is highlighting to:

readinputs = dbup.ExecuteReader() and sometimes
.ExecuteNonQuery()

---

---

Dim Dbcon As New OleDbConnection(connStr)

Dbcon.Open()

Dim query As String
Dim dbup As New OleDbCommand
Dim readinputs As OleDbDataReader

For x As Integer = 0 To IncomingMailDGV.Rows.Count - 1
    Dim received As String = IncomingMailDGV.Rows(x).Cells(0).Value
    Dim subject As String = IncomingMailDGV.Rows(x).Cells(1).Value
    Dim contents As String = IncomingMailDGV.Rows(x).Cells(2).Value

    query = "SELECT ReceivedDateTime, Subject, MessageContents FROM IncomingAlerts WHERE ReceivedDateTime = @ReceivedDateTime AND MessageContents =@MessageContents"
    dbup = New OleDbCommand(query, Dbcon)
    dbup.Parameters.AddWithValue("ReceivedDateTime", received)
    dbup.Parameters.AddWithValue("MessageContents", contents)
    readinputs = dbup.ExecuteReader()

    If readinputs.HasRows = False Then

        Dim InsertData As String
        InsertData = "INSERT INTO IncomingAlerts(ReceivedDateTime, Subject, MessageContents) Values (@ReceivedDateTime, @Subject, @MessageContents)"
        dbup = New OleDbCommand(InsertData)
        dbup.Parameters.AddWithValue("ReceivedDateTime", received)
        dbup.Parameters.AddWithValue("Subject", subject)
        dbup.Parameters.AddWithValue("MessageContents", contents)

        With dbup
            .CommandText = InsertData
            .Connection = Dbcon
            .ExecuteNonQuery()
        End With

    End If

Next

推荐答案

由于循环，您最多创建2个 OleDbCommand 每行（一个用于 SELECT ，也许一个用于 UPDATE ），但从不处理他们。您可以使用 cmd.Parameters.Clear 来重用它们，但是我将把这个东西放在一个控制过程中，使其更简单。 如下所示：

Because of the loop, you are creating up to 2 OleDbCommand objects per row (one for the SELECT and maybe one for the UPDATE), but never disposing of them. You could use cmd.Parameters.Clear to reuse them, but I would chop that thing up into a control procedure to make it simpler. Something like this:

' if AllowUsersToAddRows is true, this will loop one too many:
For x As Integer = 0 To IncomingMailDGV.Rows.Count - 1
    Dim received = IncomingMailDGV.Rows(x).Cells(0).Value.ToString
    Dim contents  = IncomingMailDGV.Rows(x).Cells(2).Value.ToString
    Dim subject  = IncomingMailDGV.Rows(x).Cells(1).Value.ToString

    If ItemExists(received, contents) = False Then
        InsertItem(received, contents, subject)
    End If
Next

然后自己包含的自助的帮助者：

Then helpers which are self contained and clean up after themselves:

Private Function ItemExists(received As String, 
       contents As String) As Boolean
    Dim query As String = "SELECT ReceivedDateTime, Subject, MessageContents FROM IncomingAlerts WHERE ReceivedDateTime = @ReceivedDateTime AND MessageContents =@MessageContents"
    Using dbcon As New OleDbConnection(connstr)
        dbcon.Open
        Using cmd As New OleDbCommand(query, dbcon)
            cmd.Parameters.AddWithValue(("ReceivedDateTime", received)
            cmd.Parameters.AddWithValue("MessageContents", contents)

            ' Better to convert the query to a SELECT COUNT
            ' cmd.ExecuteScalar would not require a Reader
            Using rdr = cmd.ExecuteReader
                Return rdr.HasRows
            End Using
        End Using    
    End Using

End Function

Private Function InsertItem(received As String, 
                 contents As String, subj As String) As Boolean
    Dim sql = "INSERT INTO IncomingAlerts(ReceivedDateTime, Subject, MessageContents) Values (@ReceivedDateTime, @Subject, @MessageContents)"

    Dim rows As Integer
    Using dbcon As New OleDbConnection(connstr)
        Using cmd As New OleDbCommand(sql, dbcon)
            dbcon.Open
            cmd.Parameters.AddWithValue("@ReceivedDateTime", received)
            cmd.Parameters.AddWithValue("@Subject", subj)
            cmd.Parameters.AddWithValue("@MessageContents", contents)
            rows = cmd.ExecuteNonQuery
            Return rows <> 0
        End Using
    End Using
End Function

我也通过使用构造函数重载使它们稍微缩短一点。例如，使用OleDbCommand，我创建SQL并将连接传递给它，而不是单独设置这些属性。

I also made them a little shorter by using the constructor overloads. For instance, with OleDbCommand, I pass the SQL and the connection to it when it is created rather than set those properties individually.

就这样，它只能完成一次。还有其他一些事情可以做，比如只使用SQL Count 来确定是否有任何匹配的行等。使用DataTable和FindRow也可以防止将数据库热看看是否有东西。

As is, it gets done only once. There are other things you could do such as just use SQL Count to determine if the are any matching rows etc. Using a DataTable and FindRow would also prevent having to hot the DB to see if something exists.

主要的点是处理 Connection ，命令和 DataReader 对象，当你完成它们。

The main point is disposing of Connection, Command and DataReader objects when you are done with them.

这篇关于Ms Access - 系统资源超出插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！