"not an error" error SqliteDatabase

Payam_Arzani · February 16, 2017, 2:55pm

Hi,

When I try to update a row, I get a “not an error” error. This does not happen regularly in that case, only sometimes. Why does the sqlite return this error? What can I do to prevent it?

Thank you,
Payam

Tim_Parnell · February 16, 2017, 3:05pm

What is the SQL?
Is your SQLite Database instance’s value for the Error boolean true or false?

Bob_Keeney · February 16, 2017, 3:32pm

Show us your code for the update.

Payam_Arzani · February 16, 2017, 3:42pm

Yes, the Error boolean is True. This is my code:

    Dim Local As New SQLiteDatabase
    Local.DatabaseFile=Globals.DB_FILE
    
    Dim edb As New SQLiteDatabase
    edb.DatabaseFile=Globals.EB_FILE
    
    Dim success As Boolean=False
    
    If Local.Connect And edb.Connect Then
      
      Local.MultiUser=True
      Local.ThreadYieldInterval=0
      Local.SQLExecute("BEGIN TRANSACTION")
      
      edb.MultiUser=True
      edb.ThreadYieldInterval=0
      edb.SQLExecute("BEGIN TRANSACTION")
      
      Dim rs As RecordSet
      
      rs=Local.SQLSelect("SELECT * FROM factures WHERE suppression='0' AND UPPER(n_facture)='"+APOS(Uppercase(INVMainList.Cell(INVOICE_INDEX,3)))+"' AND UPPER(alias)='"+APOS(Uppercase(INVDoctors.Text))+"'")
      If rs<>Nil Then
        rs.Edit
        Dim p As String
        If INVFACNonPaye.Value=True Then
          rs.IdxField(8).Value="n/a"
          p="n/a"
        ElseIf INVFACPrete.Value=True Then
          INVVIRVire.Value=False
          INVVIRNonPaye.Value=False
          rs.IdxField(8).Value="Pr"
          p="Pr"
        ElseIf INVFACLiquide.Value=True Then
          INVVIRVire.Value=False
          INVVIRNonPaye.Value=False
          rs.IdxField(8).Value="Es"
          p="Es"
        ElseIf INVFACVPay.Value=True Then
          INVVIRVire.Value=False
          INVVIRNonPaye.Value=False
          rs.IdxField(8).Value="vP"
          p="vP"
        ElseIf INVFACCarteCredit.Value=True Then
          INVVIRVire.Value=False
          INVVIRNonPaye.Value=False
          rs.IdxField(8).Value="cC"
          p="cC"
        ElseIf INVFACVire.Value=True Then
          'If INVVIRVire.Value=True Then
          rs.IdxField(8).Value="Vi"
          p="Vi"
          'End If
        End If
        If p<>"n/a" Then
          If rs.IdxField(17).StringValue.InStr("")=0 Then
            Dim oldValue As String=rs.IdxField(17).StringValue
            If p="Vi" Then
              VirementWindow.ShowModalWithin(self) // HERE OPENS A WINDOW IN MODAL MODE
            End If
            If Globals.DATE_VIREMENT<>"" Then
              rs.IdxField(17).Value=Globals.DATE_VIREMENT+""+oldValue
            Else
              Dim d As New Date
              rs.IdxField(17).Value=Format(d.Day,"00")+"/"+Format(d.Month,"00")+"/"+Str(d.Year)+""+oldValue
            End If
            Globals.DATE_VIREMENT=""
          Else
            Dim oldValue As String=rs.IdxField(17).StringValue.NthField("",2)
            If p="Vi" Then
              VirementWindow.ShowModalWithin(self)
            End If
            If Globals.DATE_VIREMENT<>"" Then
              rs.IdxField(17).Value=Globals.DATE_VIREMENT+""+oldValue
            Else
              Dim d As New Date
              rs.IdxField(17).Value=Format(d.Day,"00")+"/"+Format(d.Month,"00")+"/"+Str(d.Year)+""+oldValue
            End If
            Globals.DATE_VIREMENT=""
          End If
        Else
          If rs.IdxField(17).StringValue.InStr("")>0 Then
            Dim subString As String=rs.IdxField(17).StringValue.NthField("",2)
            rs.IdxField(17).Value=subString
          End If
        End If
        rs.Update
        
        Dim rec As New DatabaseRecord
        For i As Integer=2 To rs.FieldCount
          If rs.IdxField(i).Name="montant" Then
            rec.DoubleColumn(rs.IdxField(i).Name)=rs.IdxField(i).DoubleValue
          Else
            rec.Column(rs.IdxField(i).Name)=APOS(rs.IdxField(i).StringValue)
          End If
        Next
        rec.Column("telechargement")="0"
        edb.InsertRecord("factures",rec)
        
        If Local.Error=False And edb.Error=False Then
          Local.Commit
          edb.Commit
          INVMainList.Cell(INVOICE_INDEX,7)=p
          success=True
        Else
          Local.Rollback
          edb.Rollback
          MsgBox "Erreur : "+Local.ErrorMessage+", "+edb.ErrorMessage
        End If
      End If
    End If
    
    If success=False Then
      Update_Facture_Generale_Payement // This is the method, if Commit fails, it repeats the method
    End If
    
    INVMainList.Invalidate

Bob_Keeney · February 16, 2017, 3:44pm

Could you go back and format it with the code button? Buttons on top of the edit box and when you hover over it it will say “Code” This will make it easier for us to read.

DaveS · February 16, 2017, 3:51pm

just a comment…

s.IdxField(17)

I think the use of indexed fields should be avoided… should the underlying schema be altered for what ever reason… what WAS field #17 may no longer be so. The used of NAMED fields reduces this risk of relying of positional fields remaining constant.

Bob_Keeney · February 16, 2017, 3:57pm

This should never be done:

If rs<>Nil Then

The only time rs will EVER be nil is when there is an error in the SQL statement. So if you have an error here you’ll never catch it. I wrote about this a while back: http://www.bkeeneybriefs.com/2009/05/why-is-my-recordset-nil/

And I wrote about Field vs idxField too: http://www.bkeeneybriefs.com/2012/02/recordset-idxfield-vs-recordset-field/

DaveS · February 16, 2017, 4:08pm

use of IDXFIELD violates the first two of my programming commandments

[quote]My 3 commandments (as stated previously)
Thou shall write code that is Readable by you and your peers now and into the future
Thou shall write code that is easily Maintainable by you and your peers now and into the future
Thou shall write code that is of course Executable as efficiently as possible without violating commandments 1 and 2
[/quote]

Jean-Yves_Pochez · February 16, 2017, 4:13pm

also using sqlite database with multiuser=true is not a good idea at all, you risk database corruption any day soon…

Jean-Yves_Pochez · February 16, 2017, 4:16pm

change the code like :

dim ch as string = "SELECT * FROM factures WHERE suppression='0' AND UPPER(n_facture)='"+APOS(Uppercase(INVMainList.Cell(INVOICE_INDEX,3)))+"' AND UPPER(alias)='"+APOS(Uppercase(INVDoctors.Text))+"'" rs=Local.SQLSelect(ch)
break at the last line and gives us the value of ch, there must be a syntax error or something.
also do you have foreign keys ? if they are activated, and the types of the data at both sides of the key are different, I already had this error.

DaveS · February 16, 2017, 4:17pm

left out a “commandment”

Bob_Keeney · February 16, 2017, 4:17pm

Only if on a network drive. It’s not inherently bad.

Payam_Arzani · February 16, 2017, 4:20pm

I removed the part where the modal sheet window opens to ask for a value. I am testing the code in a row to see if that error is reproduced. Until now no error occurred.

Tobias_Bussmann · February 16, 2017, 4:23pm

[quote=316145:@Payam Arzani]

If Local.Error=False And edb.Error=False Then .... Else Local.Rollback edb.Rollback MsgBox "Erreur : "+Local.ErrorMessage+", "+edb.ErrorMessage End If[/quote]
I would bet “not an error” here means the Rollback was successful. If you want to see the ErrorMessage that was belonging to the Error property, try to ask for it before doing further DB operations.

Payam_Arzani · February 16, 2017, 4:30pm

Thank you, Tobias. You made a good point.

Jean-Yves_Pochez · February 16, 2017, 6:30pm

I’m pretty sure that if the OP wrote this line on purpose, then he wants to use it networked …