Sqlite query UPDATE problem

Databases
1

I have code that should update the SQLite DB by putting a date in UK format (DD/MM/YY) in my TargetDate field

The code I have is below.

NewD is a String variable, containing the required date in the format YYYY-MM-DD and this is changed to the required format in the 1st line of code, into the String Variable dtDD

CheckID is the ID field of the DB record. TargetDate field is type Text in the DB

CODE:

var dtDD as String = mid(NewD,9,2) + “/” + mid(NewD,6,2) + “/” + mid(NewD,3,2) 'Change format to DD/MM/YY
Messagebox(str(len(dtDD)) + " " + dtDD) 'correctly shows dtDD length as 8 plus the required date as DD/MM/YY
updateSQL = "UPDATE ToDo SET TargetDate = " + dtDD + " WHERE id = " + checkID
Try
app.db.ExecuteSQL(updateSQL)
Catch error As DatabaseException
MessageBox(app.db.ErrorMessage)
End Try
END OF CODE

What happens is that when this is run, the TargetDate field is updated to the numeral 1, not to the dtDD string.

When I access the DB outside Xojo, using DB Browser, and run exactly the same query (putting values instead of variables of course), it works and the date is stored in TargetDate. For this I used this query:
UPDATE ToDo SET TargetDate = “21/01/21” WHERE id = 17

I have other places in the project where dates are stored in the DB without any problem, for example

CODE
Var updateSQL As String = "UPDATE ToDo SET Completed = " + t + " WHERE id = " + checkID
END CODE

to insert a date in the Completed field. In this case t is a string variable in the same format as dtDD, and this works as it should.

I cannot find the reason for dtDD not working error - can someone help please!

Many thanks

2

Without addressing your specific issue, it helps readers if instead of posting code as:

CODE
Var updateSQL As String = "UPDATE ToDo SET Completed = " + t + " WHERE id = " + checkID
END CODE

you instead hightlight the code and then click on </> button making it look like this:

Var updateSQL As String = "UPDATE ToDo SET Completed = " + t + " WHERE id = " + checkID

For a single line of code you need to include a blank line before and after in the highlighted bit in order to get the syntax colouring.

Personally I convert all dates/times to SecondsSince1970 for database storage and only convert them to strings for display to the end-user (using their desired presentation format). That way I keep storage/processing separate from presentation and makes date comparison trivial.

Perhaps you need to include quotes for your string, so the SQL becomes:

updateSQL = "UPDATE ToDo SET TargetDate = '" + dtDD + "' WHERE id = " + checkID

Or you could do this:

updateSQL = "UPDATE ToDo SET TargetDate = ? WHERE id = ?"
app.db.ExecuteSQL(updateSQL, dtDD, checkID)

which is safer anyway.

2 Likes
3

Thank you so much for this very quick reply - much appreciated!

The quotes suggestion solved the problem, & I note the alternative query usage which I have seen mentioned in the documentation - that works as well.

And thank you too for the explanation of how to include code in a post to the forum. I’ve been wondering how to do that for quite some time!

Thanks once again.

4

:smile:

You might also want to look at the SQLite website:

and possibly download the sqlite3 CLI program for your platform if you don’t already have it (it comes with macOS). It is written by the SQLite people and is the most reliable way of checking that a given SQLite SQL string will work. Sometimes people have had a badly formed SQL string, which doesn’t work in Xojo, but when tested in some other program appears to work OK. What happens is that some other programs willl kindly do some fixing up of the string before running it, which is all very well but hides some problems.

5

Dates are fun. Without the quotes, SQL is doing the ‘math’ for you:
21/01/21 = 1