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

问题描述

我想从 vb.net datagridview 向 Ms access 数据库插入 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 时将 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、Command 和DataReader 对象后处理它们.

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

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