System.Data.SQLite

Good afternoon,

i'm using SQLite for .NET in last version, but my problem is (maybe) version invariant.

I connect database file using:

Dim csb As New SQLite.SQLiteConnectionStringBuilder()
    csb.JournalMode = SQLiteJournalModeEnum.Persist
    csb.CacheSize = 1024
    csb.DateTimeFormat = SQLiteDateFormats.ISO8601
    csb.Pooling = True
    csb.DataSource = Settings.dataDir + "eldp" & CStr(wa_id) & ".sqlite"
    dbData.ConnectionString = csb.ConnectionString
    dbData.Open()
    dmELDP.ELDPQuery = New SQLite.SQLiteCommand(dbData)
    If dbData.State = ConnectionState.Open Then
      Return True
    Else
      Return False
    End If

Windows Forms interface is controled via BindingNavigator, connected throught SQLiteDataAdapter:

ELDPDataSet.Tables("Listy").Clear()
      query = "SELECT DISTINCT * FROM eldp WHERE eldp_prubeh_id NOT NULL ORDER BY " & _
        "prijmPosl, jmeno, RodneCislo"
      If (Me.ELDPDA IsNot Nothing) Then
        Me.ELDPDA.Dispose()
      End If
      Me.ELDPDA = New SQLite.SQLiteDataAdapter(query, DB.dbData)
      If (ELDPDataSet.Tables("Listy") Is Nothing) Then
        ELDPDataSet.Tables.Add("Listy")
      End If
      ELDPDA.Fill(ELDPDataSet.Tables("Listy"))
      Me.ELDPBindSource.DataSource = ELDPDataSet.Tables("Listy")

Eldp is defined as view between 4 tables. I don't use standard navigator's, and dataadapter's, methods for move, update, delete and insert, becouse i need verifying all dates. All works O.K., except Insert.

While i debug code, all queries looks good (after copy them into SQLiteAdmin all of them works), ExecuteScalar() gives corrent new id (autoincemental column), but database file stays untouched. I'm correctly connected throught class which is defined as singleton.

In program is second part, for import from CSV, which works fine. All steps are same (difference is only between getting data from splitted rows of file instead of Windows GUI).

I think that database file is locked, but only for insert, not for update/delete. Can anybody tell me, how to find lock?

Environment: VisualStudio.NET 2005

I don't see anything in your code that locks the database in exclusive mode. The code you have posted just read the database. So, I guess the problem is somewhere else. Please, could you post the complete code?

Anyway, here you have some basic guidelines:

• Be sure you dispose all disposable objects (connections, transactions, commands) after using them.
• Keep transactions short in time. Don't give transaction duration control to users. 
• Don't use a single connection object shared for all the application. Instead, every time you need a connection, create one, use it, and dispose it. If creating a connection takes long time, use connection pooling.

Thanx for Your answer.

I can't post complete codes, because they are about 1.5MB and my contract make it impossible. Save part is:

  Private Sub saveEldpData()
    Dim query As String
    Dim pomReader As SQLite.SQLiteDataReader
    If ((Me.EldpId <> 0) And (Me.PersonId <> 0)) Then
      'Update ELDP and Person
      Try
        query = "UPDATE eldp_prac SET RodneCislo='" & ELDPNet.clsCommon.rc2db(CStr(edtRC.Text.Trim())) & _
        "', DatumNarozeni='" & ELDPNet.clsCommon.LongDate(CStr(edtDatumNarozeni.Text.Trim().Replace(",", "."))) & _
        "', prijmPosl='" & _
        CStr(edtPrijmeni.Text.Trim()) & "', jmeno='" & _
        CStr(edtJmeno.Text.Trim()) & "', titul='" & CStr(edtTitul.Text.Trim()) & "', ulice='" & _
        CStr(edtUlice.Text.Trim()) & "', CisloDomu='" & CStr(edtCisloDomu.Text.Trim()) & "', Obec='" & _
        CStr(edtObec.Text.Trim()) & "', PSC='" & CStr(edtPSC.Text.Trim()) & "', stat='" & _
        CStr(Me.cmbStat.SelectedItem.ToString().Substring(0, 2)) & "', " & _
        "MistoNarozeni='" & CStr(edtMistoNaroz.Text.Trim()) & "', Posta='" & _
        Me.edtPosta.Text.Trim() & "', VydCinOd='" & _
        ELDPNet.clsCommon.LongDate(CStr(edtVydCinOd.Text.Trim().Replace(",", "."))) & _
        "', RodPrijm='" & CStr(edtRodPrijm.Text.Trim()) & "', org_id='" & _
        CStr(Me.OrgId) & _
        "' WHERE eldp_prac_id=" & CStr(PersonId)
        DB.execNonQuery(query)
        query = "UPDATE eldp_prubeh SET mKod='" & CStr(edtMKod.Text.Trim()) & "', mOd='" & _
        ELDPNet.clsCommon.ShortDate(CStr(edtMOd.Text.Trim().Replace(",", "."))) & "', MDo='" & _
        ELDPNet.clsCommon.ShortDate(CStr(edtMDo.Text.Trim().Replace(",", "."))) & _
        "', m01='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl1.Checked) & _
        "', m02='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl2.Checked) & _
        "', m03='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl3.Checked) & _
        "', m04='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl4.Checked) & _
        "', m05='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl5.Checked) & _
        "', m06='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl6.Checked) & _
        "', m07='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl7.Checked) & _
        "', m08='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl8.Checked) & _
        "', m09='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl9.Checked) & _
        "', m10='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl10.Checked) & _
        "', m11='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl11.Checked) & _
        "', m12='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl12.Checked) & _
        "', m13='" & ELDPNet.clsCommon.chkBoxToDb(mChkVyl13.Checked) & _
        "', mVylDoby=" & IIf(CStr(edtMVylDob.Text.Trim()) <> "", CStr(edtMVylDob.Text.Trim()), "0") & ", mVymZ=" & _
        IIf(CStr(edtMVymZ.Text.Trim()) <> "", CStr(edtMVymZ.Text.Trim()), "NULL") & ", mDobyOdectu=" & _
        IIf(CStr(edtMDobOdectu.Text.Trim()) <> "", CStr(edtMDobOdectu.Text.Trim()), "NULL") & _
        ", mMalyRozsah='" & ELDPNet.clsCommon.chkBoxToDb(chkMMalyRozsah.Checked) & _
        "', mDny=" & IIf(CStr(edtMDny.Text.Trim()) <> "", CStr(edtMDny.Text.Trim()), "NULL") & _
        ", nKod='" & CStr(edtNKod.Text.Trim()) & "', nOd='" & _
        ELDPNet.clsCommon.ShortDate(CStr(edtNOd.Text.Trim().Replace(",", "."))) & _
        "', nDo='" & ELDPNet.clsCommon.ShortDate(CStr(edtNDo.Text.Trim().Replace(",", "."))) & _
        "', n01='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl1.Checked) & _
        "', n02='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl2.Checked) & _
        "', n03='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl3.Checked) & _
        "', n04='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl4.Checked) & _
        "', n05='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl5.Checked) & _
        "', n06='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl6.Checked) & _
        "', n07='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl7.Checked) & _
        "', n08='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl8.Checked) & _
        "', n09='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl9.Checked) & _
        "', n10='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl10.Checked) & _
        "', n11='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl11.Checked) & _
        "', n12='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl12.Checked) & _
        "', n13='" & ELDPNet.clsCommon.chkBoxToDb(nChkVyl13.Checked) & _
        "', nVylDoby=" & IIf(CStr(edtNVylDob.Text.Trim) <> "", CStr(edtNVylDob.Text.Trim), "NULL") & ", nVymZ=" & _
        IIf(CStr(edtNVymZ.Text.Trim()) <> "", CStr(edtNVymZ.Text.Trim), "NULL") & ", nDobyOdectu=" & _
        IIf(CStr(edtNDobOdectu.Text.Trim) <> "", CStr(edtNDobOdectu.Text.Trim), "NULL") & _
        ", nMalyRozsah='" & ELDPNet.clsCommon.chkBoxToDb(chkNMalyRozsah.Checked) & _
        "', nDny=" & IIf(CStr(edtNDny.Text.Trim) <> "", CStr(edtNDny.Text.Trim), "NULL") & _
        ", oKod='" & CStr(edtOKod.Text.Trim) & "', oOd='" & _
        ELDPNet.clsCommon.ShortDate(CStr(edtOOd.Text.Trim.Replace(",", "."))) & "', oDo='" & _
        ELDPNet.clsCommon.ShortDate(CStr(edtODo.Text.Trim.Replace(",", "."))) & _
        "', o01='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl1.Checked) & _
        "', o02='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl2.Checked) & _
        "', o03='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl3.Checked) & _
        "', o04='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl4.Checked) & _
        "', o05='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl5.Checked) & _
        "', o06='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl6.Checked) & _
        "', o07='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl7.Checked) & _
        "', o08='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl8.Checked) & _
        "', o09='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl9.Checked) & _
        "', o10='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl10.Checked) & _
        "', o11='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl11.Checked) & _
        "', o12='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl12.Checked) & _
        "', o13='" & ELDPNet.clsCommon.chkBoxToDb(oChkVyl13.Checked) & _
        "', oVylDoby=" & IIf(CStr(edtOVylDob.Text.Trim) <> "", CStr(edtOVylDob.Text.Trim), "NULL") & ", oVymZ=" & _
        IIf(CStr(edtOVymZ.Text.Trim) <> "", CStr(edtOVymZ.Text.Trim), "NULL") & ", oDobyOdectu=" & _
        IIf(CStr(edtODobOdectu.Text.Trim) <> "", CStr(edtODobOdectu.Text.Trim), "NULL") & _
        ", oMalyRozsah='" & ELDPNet.clsCommon.chkBoxToDb(chkOMalyRozsah.Checked) & _
        "', oDny=" & IIf(CStr(edtODny.Text.Trim) <> "", CStr(edtODny.Text.Trim), "NULL") & _
        ", sVylD=" & IIf(CStr(edtSVylDob.Text.Trim) <> "", CStr(edtSVylDob.Text.Trim), "0") & ", sVymZakl=" & _
        IIf(CStr(edtSVymZ.Text.Trim) <> "", CStr(edtSVymZ.Text.Trim), "0") & ", sDobyOdectu=" & _
        IIf(CStr(edtSDobOdectu.Text.Trim) <> "", CStr(edtSDobOdectu.Text.Trim), "0") & _
        ", eldpRok=" & CStr(edtEldpRok.Text.Trim) & ", eldpTyp='" & _
        CStr(Me.eldpTyp.SelectedItem.ToString().Substring(0, 2)) & _
        "', eldpDatum='" & ELDPNet.clsCommon.LongDate(Me.edtDatumVyhot.Text.Trim()) & "'" & _
        " WHERE eldp_prubeh_id=" & CStr(EldpId)
        DB.execNonQuery(query)
        Dim posit As Integer = Me.ELDPBindSource.Position
        Me.getEldpData()
        Me.ELDPBindSource.Position = posit
      Catch ex As Exception
        MsgBox(ex.Message)
        ELDPNet.clsError.EMzdyError(ex.Message & vbCrLf & query)
      End Try
    Else
      Try
        query = "INSERT INTO eldp_prac(oscis,RodneCislo, DatumNarozeni, prijmPosl, jmeno, titul, ulice, " & _
          "CisloDomu, Obec, PSC, stat, MistoNarozeni, Posta, VydCinOd, RodPrijm, org_id) VALUES (" & _
          "'01','" & ELDPNet.clsCommon.rc2db(Me.edtRC.Text.Trim()) & "', '" & _
          ELDPNet.clsCommon.LongDate(CStr(edtDatumNarozeni.Text.Trim())) & "', '" & _
          CStr(edtPrijmeni.Text.Trim()) & "', '" & CStr(edtJmeno.Text.Trim()) & _
            "', '" & CStr(edtTitul.Text.Trim()) & "', '" & CStr(edtUlice.Text.Trim()) & _
            "', '" & CStr(edtCisloDomu.Text().Trim) & "', '" & CStr(edtObec.Text.Trim()) & _
            "', '" & CStr(edtPSC.Text.Trim) & "', '" & _
            CStr(Me.cmbStat.SelectedItem.ToString().Substring(0, 2)) & "', '" & _
            CStr(edtMistoNaroz.Text.Trim()) & "', '" & CStr(edtPosta.Text.Trim()) & "', '" & _
            ELDPNet.clsCommon.LongDate(CStr(edtVydCinOd.Text.Trim)) & "', '" & _
            CStr(edtRodPrijm.Text.Trim) & "', '" & CStr(Me.OrgId) & "');"
        DB.execNonQuery(query)
        query = "SELECT last_insert_rowid() FROM eldp_prac"
        Me.PersonId = DB.ExecScalar(query)
        query = "INSERT INTO eldp_prubeh(eldp_prac_id, mKod, mOd, mDo, m01, m02, m03, m04, m05, " & _
          "m06, m07, m08, m09, m10, m11, m12, m13, mVylDoby, mVymZ, mDobyOdectu, mMalyRozsah, " & _
          "nKod, nOd, nDo, n01, n02, n03, n04, n05, " & _
          "n06, n07, n08, n09, n10, n11, n12, n13, nVylDoby, nVymZ, nDobyOdectu, nMalyRozsah, " & _
          "oKod, oOd, oDo, o01, o02, o03, o04, o05, " & _
          "o06, o07, o08, o09, o10, o11, o12, o13, oVylDoby, oVymZ, oDobyOdectu, oMalyRozsah, " & _
          "sVylD, sVymZakl, sDobyOdectu, eldpRok, eldpTyp, mDny, nDny, oDny, eldpMesic, eldpDatum) VALUES (" & _
          CStr(Me.PersonId) & ", '" & Me.edtMKod.Text.Trim() & "', '" & _
          ELDPNet.clsCommon.ShortDate(Me.edtMOd.Text.Trim().Replace(",", ".")) & _
          "', '" & ELDPNet.clsCommon.ShortDate(Me.edtMDo.Text.Trim().Replace(",", ".")) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl1.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl2.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl3.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl4.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl5.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl6.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl7.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl8.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl9.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl10.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl11.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl12.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl13.Checked) & "', '" & _
          CStr(Me.edtMVylDob.Text.Trim) & "', '" & CStr(Me.edtMVymZ.Text.Trim) & "', '" & _
          CStr(Me.edtMDobOdectu.Text.Trim) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(Me.chkMMalyRozsah.Checked) & "', '" & _
          Me.edtNKod.Text.Trim & "', '" & _
          ELDPNet.clsCommon.ShortDate(Me.edtNOd.Text.Trim().Replace(",", ".")) & _
          "', '" & ELDPNet.clsCommon.ShortDate(Me.edtNDo.Text.Trim().Replace(",", ".")) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl1.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl2.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl3.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(mChkVyl4.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl5.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl6.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl7.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl8.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl9.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl10.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl11.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl12.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(nChkVyl13.Checked) & "', '" & _
          CStr(Me.edtNVylDob.Text.Trim) & "', '" & CStr(Me.edtNVymZ.Text.Trim) & "', '" & _
          CStr(Me.edtNDobOdectu.Text.Trim) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(Me.chkNMalyRozsah.Checked) & "', '" & _
          Me.edtOKod.Text.Trim & "', '" & _
          ELDPNet.clsCommon.ShortDate(Me.edtOOd.Text.Trim().Replace(",", ".")) & _
          "', '" & ELDPNet.clsCommon.ShortDate(Me.edtODo.Text.Trim().Replace(",", ".")) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl1.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl2.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl3.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl4.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl5.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl6.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl7.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl8.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl9.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl10.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl11.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl12.Checked) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(oChkVyl13.Checked) & "', '" & _
          CStr(Me.edtOVylDob.Text.Trim) & "', '" & CStr(Me.edtOVymZ.Text.Trim) & "', '" & _
          CStr(Me.edtODobOdectu.Text.Trim) & "', '" & _
          ELDPNet.clsCommon.chkBoxToDb(Me.chkOMalyRozsah.Checked) & "', '" & _
          CStr(Me.edtSVylDob.Text.Trim) & "', '" & CStr(Me.edtSVymZ.Text.Trim) & "', '" & _
          CStr(Me.edtSDobOdectu.Text.Trim) & "', '" & CStr(cmbEldpRok.SelectedText.Trim) & _
          "', '" & CStr(eldpTyp.SelectedText) & "', '" & CStr(Me.edtMDny.Text.Trim) & "', '" & _
          CStr(Me.edtNDny.Text.Trim) & "', '" & CStr(Me.edtODny.Text.Trim) & "', '" & _
          CStr(Me.cmbMesic.SelectedText) & "', '" & _
          ELDPNet.clsCommon.LongDate(Me.edtDatumVyhot.Text.Trim()) & "'); " & _
          " SELECT last_insert_rowid() FROM eldp_prac;"
        Me.EldpId = DB.ExecScalar(query)
        Me.ELDPDataSet.Tables("Listy").Clear()
        Me.getEldpData()
        Me.ELDPBindSource.ResumeBinding()
      Catch ex As Exception
        MsgBox(ex.Message)
        ELDPNet.clsError.EMzdyError(ex.Message & vbCrLf & query)
      End Try
    End If
  End Sub

I don't understand why update and delete works fine, but insert no (it's more unbelievable, insert doesn't work in one part only).

I've though, that's better use single shared instance, becouse program is single user, throught program run.

Connecting and disconnecting works fine, but i'm not sure that it's good practice.