Problem inserting multiple rows quickly to Oracle database from VB
I'm trying to insert multiple rows, one after another, to a database. Here is the relevant code.
Private ConnectionString As String = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=xxx)))(CONNECT_DATA=(SID=xxx)(SERVER=DEDICATED)));User Id=xxx;Password=xxx;" Private SQL As String = "INSERT INTO XXX.BU_LOG (PROGRAM, LOCATION, MESSAGE, TIMESTAMP, ""LEVEL"", COMPUTER, ""USER"") " Dim Connection As New OleDbConnection(ConnectionString) Dim Command As OleDbCommand 'The SQL variable is the first part of an insert statement SQLValues = "VALUES ('" & Program & "','" & Location & "','" & Message & "','" & Timestamp & "','" & LevelName & "','" & Computer & "','" & User & "')" Dim Command As New OleDbCommand(SQL & SQLValues, Connection) Connection.Open() Command.ExecuteNonQuery() Connection.Close()
Now if I call it once it works great. If I call it twice from say different buttons (inserting different values for each button) it works great. However, when I call the the code twice in the same button, one right after another, it inserts two rows but the second row is the same as the first row. I've checked the command text and it is correct when it executes the query but it duplicates the row.
If I sleep the thread for 500ms before I call the second insert, it works fine. But if I only sleep it 100ms it will duplicate. Any ideas?
EDIT: Sorry if I was unclear. The problem isn't specifically the Timestamp column. In fact, it's ok for the Timestamp column to have dupes. Here is how I'm calling it.
log.Write(My.Application.Info.AssemblyName, System.Reflection.MethodBase.GetCurrentMethod.Name, "Hello World!") log.Write(My.Application.Info.AssemblyName, System.Reflection.MethodBase.GetCurrentMethod.Name, "Testing", , VB2008_Log_Dll.Log.mLevel.Fatal)
As you can see, the first one just writes "Hello World!" and the second writes "Testing". When I run the program I get two rows with "Hello World!". I hope this makes it clearer.
What is the data type of column TIMESTAMP? If it is DATE, Oracle DATE values are only accurate to 1 second, so two inserts within half a second or so are likely to end up with the same value for the column.
There is a newer data type called TIMESTAMP that is accurate to fractions of a second that you could use instead if appropriate.