I want to handle a constraint defined in a SQLite database with this piece of code:
Var Sql As String = "DELETE FROM Customers WHERE CustomerId = ?"
Try
db.ExecuteSQL(Sql,TxtCode.Value)
Catch err As DatabaseException
MessageDialog.Show("DB Error: " + err.Message)
Return
End Try
The table Customers is connected with Invoices. It seems the error is not detected in XOJO. In DB Browser for SQLite I get a constraint error when I try to delete the same record with the same SQL statement. Is there something wrong with this piece of code?
What is TxtCode? I’d say that TxtCode.Value will be a string, while it looks like CustomerId is an integer.
TxtCode.Value is a String. CustomerId is also a String in both tables.
You mean defined as a Text field? If you’ve defined the columns a String in the SQLite database they will not be Text and will have numeric affinity. Strings supplied will be converted to numbers where possible, which may possibly mess up your SQL comparison.
To see what is happening you should check using the sqlite3 CLI tool, as other tools may mask problems. You can download that from sqlite.org.
What sort of constraint error are you expecting?
TxtCode is a XOJO Textfield. The Value of TxtCode is a String. In the SQLite database I defined CustomerId to be Text fields.
XOJO always deletes the Customer record in the table Customers (even when the same CustomerId is used in the Invoices table). The reference to CustomerId in the Invoices table is not ‘catched’ by my piece of code.
The constraint error I want to display could be 'Delete not allowed because of related records in other tables '. In the Invoices table I defined the foreign key as follows:
FOREIGN KEY("CustomerId") REFERENCES "Customer"("CustomerId") ON DELETE RESTRICT
if CustomerID is a string, then you should surround the “?” at the end of the query with some quotes ?
Thank you, Paul. This solved my problem!
1 Like