在读取器中执行非查询

Question

如何解决这个问题？我不想使用两个连接。ExecuteNonQuery 仅在读取器关闭时才有效。

oleCnn = New System.Data.OleDb.OleDbConnection
oleCnn.ConnectionString = ConnectionString
oleCmm = New OleDb.OleDbCommand()
oleCnn.Open()
oleStr = "SELECT ID_Process FROM MyProcessTable"
Dim reader As System.Data.OleDb.OleDbDataReader = oleCmm.ExecuteReader()
        While reader.Read()
            For i = 1 To NumExecutions
                    oleStr = "INSERT INTO MyOtherProcessTable(reader.getInt32(0))"           
                    oleCmm.ExecuteNonQuery()
            Next
        End While
reader.Close()
oleCnn.Close()

谢谢

Answer 1

使用列表在读取 DataReader 时存储您的 ID，然后对于列表的每个元素，您应该能够使用相同的连接执行查询：

oleCnn = New System.Data.OleDb.OleDbConnection
oleCnn.ConnectionString = ConnectionString
oleCmm = New OleDb.OleDbCommand()
oleCnn.Open()
oleStr = "SELECT ID_Process FROM MyProcessTable"
Dim ids As New List(Of Integer)
Dim reader As System.Data.OleDb.OleDbDataReader = oleCmm.ExecuteReader()
    While reader.Read()
    ids.Add(reader.GetInt32(0))
    End While
reader.Close()
For Each curr_id As Integer In ids
    For i = 1 To NumExecutions
        oleStr = "INSERT INTO MyOtherProcessTable(" & curr_id.ToString & ")"
        oleCmm.ExecuteNonQuery()
    Next
Next
oleCnn.Close()

附言我不明白循环For i = 1 To NumExecutions，但我在你写的时候添加了它