Updating a DateTimePicker

General
1

What is the correct syntax to Update a calendar change to the sqlite database? The INSERT code works as expected, but when I try to UPDATE a changed date, I get an error. This is the code I am using:

db.ExecuteSQL ("UPDATE Characters SET Character_Date = '" + DateofBirth.SelectedDate + "' WHERE ID = " + CharacterID_Selected)

the error message is:
Undefined operator. Type TextLiteral does not define "Operator_Add" with type DateTime db.ExecuteSQL ("UPDATE Characters SET Character_Date = '" + DateofBirth.SelectedDate + "' WHERE ID = " + CharacterID_Selected)

2

https://documentation.xojo.com/api/data_types/index.html

3

Are you saving in SQLDate format as a String?

4

no, not sure how I would formalise this. I will read up on this, thanks for the tip.

5

What do you see on the database?
Can you pull the information from the database? What code do you use to pull the information from the database?

Do you understand what the error message is telling you?
Basically you are using db.ExecuteSQL(string) but you are trying to concatenate string + DateTime + string (assuming CharacterID_Selected is string too) and you can’t do that.

6

DateTime is an object. You need to use DateofBirth.SelectedDate.SQLDate to get a suitable string.

7

I am using this code to pull the data from the database into the Calendar object, it works as expected:

DateofBirth.SelectedDate = rs.Column("Character_Date").DateTimeValue

8

Thanks, it works correctly now.

9

I hope you did catch my hint about SQLDate and that you understand why you get the error without using .SQLDate

10

Yes, If I understand correctly it says I cannot add two different values together?

12

I always use a property to build the SQLite Command String.

Read the code extract below:


If gDB.Connect Then
  // Add a TABLE And RowsÂ…
  Var SQL_Cmd As String
  
  SQL_Cmd = "CREATE TABLE IF NOT EXISTS Customers(CustomerID INTEGER, " +_
  "CustomerName TEXT, ContactName TEXT, Address TEXT, City TEXT, PostalCode TEXT, " +_
  "Country TEXT, ID INTEGER NOT NULL, PRIMARY KEY(ID));"
  gDB.ExecuteSQL(sql_Cmd)

The code compiles even if the SQLite Command is wrong. To be sure about the SQL_Cmd contents, you can use:



System.DebugLog SQL_Cmd

and you will get the contents of the property as string if you click (while the program runs in the ide) in
image

Concatenating objects in a SQLite Command String… always results in troubles. As its name imply, the SQLite Command String holds Strings.

Read String to know how to convert whatever kind of variable into String.

13

I would recommend reading this Blog

14

Would be surprise me if Aliens would be using sqlite in the 24th century… I understand the risks about the SQL Injections, but I don’t think this is very important in the app I am building which is for story development for a single user. No money laundry dangers here. Also, because I am still at the start of understand how to work with databases I want to understand first the basics of it before adding a layer of something else. Nice blog tough, thanks :slight_smile:

15

Using a prepared statement will provide the necessary data type conversions for you as well as protecting you from injection issues.

2 Likes