Auto increment isn't correct when running two instances

When i insert a row into the database using any one instance ,the auto incremented value(ID) is not proper when read from other instance

I suspect it's because your connections and commands aren't closed/disposed so they are left open.

Best advice for connection is to use it and then close/dispose of it. Same goes for commands, make sure they are disposed.

Here's one way to do this:

 Dim intReturn As Integer sql = "SELECT max(ID) FROM joblog;" ' First query to get your scalar return value Using cnn As New New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")    Using mycommand As New SQLiteCommand(sql, cnn)       cnn.ParseViaFramework = True       cnn.Open()       intReturn = mycommand.ExecuteScalar       MessageBox.Show(intReturn)    End Using End Using ' Second query to actually do the insert sql = "INSERT INTO joblog (jobid) VALUES (123);" Using cnn As New New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")    Using mycommand As New SQLiteCommand(sql, cnn)       cnn.ParseViaFramework = True       cnn.Open()       intReturn = mycommand.ExecuteNonQuery()       MessageBox.Show("Records affected " & intReturn)    End Using End Using

On another note, look into using SQL Parameters, otherwise you may be prone to SQL Injection attacks.

The function I used is somewhat equivalent to Scope_Identity in Sql Server. I do not have an SqLite database hanging around so I couldn't test. I am not sure if you can chain commands in SqLite but if not just make 2 separate queries. The ExecuteScalar returns the first column of the first row in the result set which is the number you want.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click    Dim NewID As Long    Using cn As New SQLiteConnection("Your connection string")        Using cmd As New SQLiteCommand("INSERT INTO joblog (jobid) VALUES (123); Select last_insert_rowid()")            NewID = CLng(cmd.ExecuteScalar())        End Using    End Using    MessageBox.Show($"The new ID is {NewID}")End Sub

Simple answer is that you should not use shared sqlite database file, it is not stable, and the database file can easily be corrupted.Using mariadb/postgresql is a better idea.

I think your problem is about transaction, you should commit your changes manually to update the database file immediately after updating.

1. SQLiteTransaction trans = conn.BeginTransaction();2. select max(id), insert...3. trans.commit();