SQLite Syntax

I always seem to vave problems with the syntax for SQLite transactions. Thanks to you guys I’ve got the hang of SELECT operations. Now I’m looking for for the UPDATE and DELETE syntax.
For UPDATE I want to be able to change a single record identified by the ID, but the ID would be cited by reference to a TextField which contains it -
UPDATE [tablename] [field names] WHERE [ID = TF.text]
For DELETE I want to delete the entire row referencing the record in the same way as above.

What I find confusing is where to put the apostrophes and inverted commas.

Also, how important is the db.Commit statement ??

easy good way to learn sql online : https://www.w3schools.com/sql/default.asp

easy way to test live your sql queries online : http://sqlfiddle.com/

Read there too:
https://www.sqlite.org/index.html

Nota: Jean-Yves links are very good (especially https://www.sqlite.org/index.html)

[quote=413601:@Mike Wyatt]For UPDATE I want to be able to change a single record identified by the ID, but the ID would be cited by reference to a TextField which contains it -
UPDATE [tablename] [field names] WHERE [ID = TF.text]
For DELETE I want to delete the entire row referencing the record in the same way as above.

What I find confusing is where to put the apostrophes and inverted commas.

Also, how important is the db.Commit statement ??[/quote]
What apostrophes and inverted commas?

A simple UPDATE example might be:

update mytable set myfield1=33, myfield2=44 where id=27

If the id is in a textfield, you might build an sql string as in:

Dim sql as string, dbh as sqlitedatabase

sql = "update mytable set myfield1=33, myfield2=44 where id=" + clong (mytxtfield.text)

dbh.SQLExecute (sql)

(I’ve omitted code to open the db and check for errors.)

This assumes that myfield is guaranteed to only contain a number, you may wish to read the value from the field and check it before generating the sql. If the values I’ve used (33 and 44) come from elsewhere then modify the sql generating statement as appropriate.

You don’t need the commit unless you have started a transaction. In SQLite, all single sql calls like the above are their own transaction.

[code] Dim sql as string, dbh as sqlitedatabase

sql = “update mytable set myfield1=33, myfield2=44 where id=” + clong (mytxtfield.text)[/code]

You cannot pass a clong [Klong ;-)] to a String variable. Use instead:

[code] Dim sql as string, dbh as sqlitedatabase

sql = “update mytable set myfield1=33, myfield2=44 where id=” + mytxtfield.text[/code]

Ah, sorry, I meant to write:

sql = "update mytable set myfield1=33, myfield2=44 where id=" + clong(mytxtfield.text).ToText

But (at first), I really liked Klong (phonetically) until I realisez it is CLong ;).

Thank you for the smile.

BTW: do you really need to change the text contents to a long, then change it back to text ?

yes to filter only the integer, and disgard the eventual letters.

There is a Mask Property available to avoid entering non digits characters in TextEdit.

Edit:
I asked because I am curious.

Thanks chaps - got both working now. I really find this Forum most useful and some of the contributors, who are far more proficient than I can ever hope to be (!), really quite extraordinarily helpful. Mike W

Good to hear. Emile’s suggestion of the SQLite web site is a good one - I find that the docs there are most helpful. There’s also an SQLite Users’ Mailing List that I lurk on - most of what’s there is too arcane for me but there’s the occasional nugget - and - they’re very helpful there, too.