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.
[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.
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.