B4J Question Inserting Records in Access database takes too long.

[Johnson Samuel]

   dim fc As FileChooser
   dim sql as SQL
    fc.Initialize
    fc.Title="Select File" 'title of the dialog box
    fc.setExtensionFilter("MDB file", Array As String("*.mdb"))
    myfname=fc.ShowOpen(RemoteResultform) 'myfname contains the full path
    sql.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & myfname.Trim & ";memory=false")

For k=0 To paylist.Size -1
       
        Dim row(5) As Object
        res_map.Initialize
        res_map = paylist.Get(k)
           
        row(0)=res_map.Get("exam")
        row(1)=res_map.Get("schno")
        row(2)=res_map.Get("subject")
        row(3)=res_map.Get("max")
        row(4)=res_map.Get("mark")
   
        'inserting the data in Access database
           
        sql.ExecnonQuery2("INSERT INTO RemoteResult(exam,schno,subject,max,mark) Values(?,?,?,?,?)",Array As Object(row(0),row(1),row(2),row(3),row(4)))
        sql.ExecnonQuery("COMMIT;")
       
    Next
    lblmessage.Text="Done..."

This takes around 2 to 3 minutes to insert 1000 records. Kindly Help.

 

[jahswant]

sql.ExecnonQuery2

sql.AddnonQueryToBatch

sql.ExecnonQuery("COMMIT;")

After the next block add this.

Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")

Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)

Log("NonQuery: " & Success)

sql.ExecnonQuery("COMMIT;")

 

[Johnson Samuel]

Thanks jahswani for your prompt solution, only problem was one should add the 2nd block of senderfilter after the Next statement

Next
Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
Log("NonQuery: " & Success)

sql.ExecnonQuery("COMMIT;")

Thanks again , now it's taking only 3 sec. for 1000 records.