ExecuteNonQuery inside of Reader

问题:

How can I solve this problem? I do not want to use two connections. ExecuteNonQuery only works if reader is closed.

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()

Thank you

回答1:

Use a list to store your IDs while reading the DataReader, then for each element of the list you should be able to execute your query with then same connection:

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()

P.S. I don't understand the For i = 1 To NumExecutions for loop, but I added it as you wrote it