Batch INSERTS into MDB

I have a string array containing sql INSERT statements that I want to run. Here is the code:

For i = 0 To UBound(sqlArray)
   sSQL = sqlArray(i)
   command.CommandText = sSQL
   command.ExecuteNonQuery()
Next

This works fine IN DEBUG MODE but not in runtime. In Runtime, only the last insert works. The solution - I think - to ensure running the next query ONLY when the first is completed. I don't think I can use the PROCESS Class would work here - can threads be implemented can someone help me with ideas/codes

Thanks.

Answer this question

Batch INSERTS into MDB

Tiago Colombo

The call to ExecuteNonQuery is done only when the query has completed.

I created a very small sample to try to reproduce the problem but could not. Your best bet might be to isolate the problem in a similar snippet of code. If the problem still occurs, post the code here and someone may be able to offer an explanation and/or a solution.

David Sceppa
Microsoft

aQueries(0) = "INSERT INTO BunchOfJetQueries (SomeColumn) VALUES ('First Row')"

aQueries(1) = "INSERT INTO BunchOfJetQueries (SomeColumn) VALUES ('Second Row')"

aQueries(2) = "INSERT INTO BunchOfJetQueries (SomeColumn) VALUES ('Third Row')"

aQueries(3) = "INSERT INTO BunchOfJetQueries (SomeColumn) VALUES ('Fourth Row')"

aQueries(4) = "INSERT INTO BunchOfJetQueries (SomeColumn) VALUES ('Fifth Row')"

Dim cn As New OleDbConnection(strConn)

cn.Open()

Dim cmd As OleDbCommand = cn.CreateCommand()

cmd.CommandText = "DROP TABLE BunchOfJetQueries"

Try : cmd.ExecuteNonQuery() : Catch : End Try

cmd.CommandText = "CREATE TABLE BunchOfJetQueries (SomeColumn varchar(255))"

cmd.ExecuteNonQuery()

For Each strSQL As String In aQueries

cmd.CommandText = strSQL

cmd.ExecuteNonQuery()

cmd.CommandText = "SELECT COUNT(*) FROM BunchOfJetQueries"

Console.WriteLine(cmd.ExecuteScalar())

cn.Close()

Console.Read()

Batch INSERTS into MDB

Answer this question