SQLite Exception: Error Code 20 when Saving Record

[BPak]

Trying to save a record in a database and get an error.

Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.
   Dim SQL1 As SQL
End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   Dim NameEd As EditText
   Dim BankEd As EditText
   Dim LoseCntEd As EditText
End Sub

Sub Activity_Create(FirstTime As Boolean)
   Activity.LoadLayout("1")
   If FirstTime = True Then
      CreateConnection
   End If
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
   If UserClosed Then SQL1.Close
End Sub

Sub CreateConnection
   If SQL1.IsInitialized = False Then
      SQL1.Initialize(File.DirDefaultExternal, "Metz.db", True)
      ' Create Account Table
      SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS account (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Bank REAL, LoseCnt INTEGER)")
   End If
End Sub

Sub SaveBtn_Click
   Dim IDs As String
   Dim Name As String
   Dim Bank As String
   Dim Lose As String

   IDs = "Null"
   Name = NameEd.Text
   Bank = BankEd.Text
   Lose = LoseCntEd.Text
   ' save new or edit account to account dbase
   SQL1.ExecNonQuery2("INSERT INTO account VALUES(?,?,?,?)", _
      Array As String(IDs, Name, Bank, Lose))   
End Sub

I think it is something to do with the PRIMARY KEY.

Is there a special way to do an Entry that uses PRIMARY KEY?

 

[Erel]

Try:

SQL1.ExecNonQuery2("INSERT INTO account VALUES(?,?,?)", _
      Array As String(Name, Bank, Lose))

 

[klaus]

Unfortunately this doesn't work.

SQL1.ExecNonQuery2("INSERT INTO account VALUES(?,?,?)", _
        Array As String(Name, Bank, Lose))

But this one does.

SQL1.ExecNonQuery2("INSERT INTO account VALUES(NULL,?,?,?)", _
        Array As String(Name, Bank, Lose))

Best regards.

 

[BPak]

Great!

I came up with a work around but the way Klaus has it would be safer to use.

   ' work around, does work
   str1 = "INSERT INTO account VALUES (Null, '" & NameEd.Text & "', " & BankEd.Text & ", " & LoseCntEd.Text & ")"
   SQL1.ExecNonQuery(str1)