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
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
Catch error As DatabaseException
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
Var updateSQL As String = "UPDATE ToDo SET Completed = " + t + " WHERE id = " + checkID
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!