2

I would like to know how to get the time needed by the

INSERT

query in a msaccess database.

I use an OleDbCommand and of course an OleDbConnection object

I am running a loop like :

 For Each item In ListBox.SelectedItems

                Try

                    cmd.CommandText = "INSERT INTO table(x) VALUES(" + item.ToString + ")"
                    cmd.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            Next

and just afterwards I fill another listbox by getting these items recently inserted in the database.

code for 2nd listbox:

SELECT items FROM table

for each item returned by query: 
listbox2.addItem(item) 
end loop

I have a slow connection so my 2nd listbox doesn't get filled right away.

By putting my thread to sleep for 5s and then running the SELECT query solves my problem because only after 5s I can see the newly inserted data, but it's not always like this, I may need to wait for 4s or even 7s.I hope the Try does not slow down the process.

Rob
  • 26,989
  • 16
  • 82
  • 98
Oleg
  • 1,479
  • 3
  • 21
  • 42

2 Answers2

1

Why re-query the database. Just populate the second list box with the same value you INSERTed. Just be sure to do that after the Try Catch completes. Or return a scalar to check to be certain the insert worked then add your item to the second list box. That way you need not re-query, which is by far the slowest bottle neck.

Also, if you must re query, are you closing and reopening your connection? If so don't, just leave it open until you are all done with the db.

htm11h
  • 1,739
  • 8
  • 47
  • 104
1

It is a caching issue.

The answers and linked articles in this question explain it:

Why is my Access database not up-to-date when I read it from another process?

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80