[SQLite] Multiple UPDATE SETAs

Databases
1

The code below works fine:

SQL_Cmd = "UPDATE Contacts SET EntityName='" + TF_1_Entity.Text + "' WHERE ContactID=" + UniqueID.ToString +";"

Try
  db.ExecuteSQL(SQL_Cmd)

But if I add anothe Column either separated with a comma or AnD does not.

DO I fall into a bug ?

Latest Sequoia, MacBookPro M1, Xojo 2024r4.2.

Searches leads to nowhere.

Ideas ?

2

As stupid as it seems, issuing multiple UPDATE

SET works fine.

I mean:

db.ExecuteSQL("UPDATE Contacts SET EntityName='"

Change the Column Name / Value and all works fine.

3

Have you tried doing a prepared statement instead?

Or maybe just something like this:

Var SQL_Cmd As String = "UPDATE Contacts SET EntityName = ? WHERE ContactID = ?"
db.ExecuteSQL(SQL_Cmd, TF_1_Entity.Text, UniqueID)
4

For more than one column, it looks like this:

Var SQL_Cmd As String = "UPDATE Contacts SET EntityName = ?1, othercolumn=?2 WHERE ContactID = ?3"
db.ExecuteSQL(SQL_Cmd, TF_1_Entity.Text, otherval, UniqueID)
5

Thank you.

$But this is where the trouble lies. I have 9 (or is it 10) Rows to update; try to plas-ce more than one SET in a line… (hint: that does not works? I tried with ‘AND’ and ‘,’…)
My original (and not working) code:


SQL_Cmd = "UPDATE Contacts " + _
"SET EntityName = '"  + TF_1_Entity.Text    + "' "+_
"SET Address1 = '"    + TF_2_Address1.Text  + "' "+_
"SET Address2 = '"    + TF_3_Address2.Text  + "' " +_
"SET PO_Box = '"      + TF_3_PO_Box.Text    + "' " +_
"SET ZIP = '"         + TF_4_Zip.Text       + "' " +_
"SET City = '"        + TF_5_Ville.Text     + "' " +_
"SET Phone = '"       + TF_6_Telephone.Text + "' " +_
"SET eMail = '"       + TF_7_eMail.Text     + "' " +_
"SET Web_URL = '"     + TA_8_Web_Home.Text  + "' " +_
"SET Notes = '"       + TA_9_Notes.Text     + "' " +_
"WHERE ContactID = "  + UniqueID.ToString   + ";"

THat is at the end of each line ('xcept the last) that I add ‘AND’, then ‘,’ and still get the trouble.

Using one Column at a time (Documenation, your suggestion) works fine.

I wasted the same amount of time for UPDATE than I use to add the code for the other usual commands… (my whole morning vs part of the night).

6

I thought the numbering was only used for the SQL Server driver? What issues could arise without using the numbers? I have a few Xojo/SQLite apps that don’t number it.

7

Hey Emile, you can only use 1 SET in a sqlite command. You would have to write individual update statements if you want to use SET for every column.

8

That is what I see in Tim reply.

The reported error was:
`Error 1: near “SET”: syntax error.

Thank You All.

9

The syntax is to only use the term SET once. You need to be using a prepared statement.

UPDATE t_Contact SET Blank = ?, Blank2 = ?, Blank3 = ? WHERE ContactID = ?
10

with ExecuteSQL ? (API2)

11

You can use API 2 or you can construct a SQLitePreparedStatement in API 1, but since you’re inserting user data directly you need to do so to avoid (even unintentional) SQL injections.

A single-quote in any of the Text Fields above would destroy the SQL statement which could cause problems of all kinds.

Update: Something like this

const kSQL = "UPDATE Contacts SET EntityName = ?, Address1 = ?, Address2 = ?, PO_Box = ?, " + _
"ZIP = ?, City = ?, Phone = ?, eMail = ?, Web_URL = ?, Notes = ? WHERE ContactID = ?"

db.ExecuteSQL(kSQL, TF_1_Entity.Text, TF_2_Address1.Text, _
TF_3_Address2.Text, TF_3_PO_Box.Text, TF_4_Zip.Text, TF_5_Ville.Text, _
TF_6_Telephone.Text, TF_7_eMail.Text, TA_8_Web_Home.Text, TA_9_Notes.Text, _
UniqueID)
2 Likes
12

You can leave off the suffix but then have to be super careful to have the params in the same order they are asked for in the SQL statement. I’m using SQLite and leave the suffix off if there’s only one param, but always add it if there’s more than one.

1 Like