For global connections(means connections which are not closed), for implicit transactions(with auto commit on),whether insert query is not committed immediately? (sometimes last inserted row is not available to read in other connections). But works for local connections(means connections which are closed after every query execution)

i have attached the vb code below

Imports System.Data.SQLite
Public Class Form1
    Public cnn As SQLiteConnection
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        cnn = New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")
        cnn.ParseViaFramework = True
    End Sub
Public Function inserttoTable(ByVal sql As String) As DataTable

            sql = "SELECT max(ID) FROM joblog;"
            Dim mycommand As SQLiteCommand = New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            sql = "INSERT INTO joblog (jobid) VALUES (123);"

            mycommand = New SQLiteCommand(cnn)
            mycommand.CommandText = sql
        Catch ex As Exception
        End Try
  • i do not want to use WAL or shared cache mode or ,close and open connections after every insert. – IT researcher Oct 3 at 9:52

I think CL. hit the nail in the coffin...

As to my knowledge - I don't know if it's the same for SQLite - some database systems offer a "cached mode" - where a select result is held in the database server's memory as a temporary table - and your application only gets a pointer to these temporary table and a so called "cursor" that initially points to the first entry of said table. To free up the used temporary table you have to assign an additional command (db_free_result or similarly named) to the database server. Usually you do that after fetching the temporary table into some kind of data structure in your application - be it a list of objects, a collection or an array of data sets or similar. As long as you don't free the result, some databases deny you the next select request.

  • sometimes(when another application also inserts row) we get "UNIQUE constraint failed" error to "ID" column (which is AUTOINCREMENT) i.e AUTOINCREMENT is not working for insert. Also if we try to read maximum ID, it doesnt return last inserted ID. – IT researcher Oct 9 at 5:25

Your symptoms indicate that the transaction is still active.

This happens when some SELECT query has not yet finished. (The C API has functions to search for active commands, but this is not available in C#.)

Another cause could be that your network file system is buggy.

  • No other query is running - it is a test db - you can create error yourself, by opening 2 exes(instances) of given code – IT researcher Sep 30 at 9:35
  • "Active" is not the same as "running". Some SELECT command has not been closed correctly. – CL. Sep 30 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.