Page 1 of 1

using UPDATE function in SQLite

Posted: Sun Jan 18, 2015 3:38 pm
by Ultravibe
Hi there!
This is my script, wich contained in Button controller and works when clicking the button:

on mouseUp
put "UPDATE Persons SET (Amplua='Rider',Name='Samuel',Lastname='Jackson',Origin='USA') WHERE Number='2'" into tSQL
revExecuteSQL DatabaseID, tSQL
--DatabaseID at this moment contains an actual database id, it's defined before!

revCloseDatabase DatabaseID
end mouseUp

I want to modify the cells in table of database. Table named "Persons". Field "Number" in every row contains number of person. Then i've got several fields like "Amplua" (person is rider, official or simple. it's like a status of person), "Name", "LastName" etc. Using WHERE clause, i set the destination (the row) where i want to update cells

but this structure doesn't work :(((( what am i supposed to do?

Re: using UPDATE function in SQLite

Posted: Sun Jan 18, 2015 8:44 pm
by phaworth
The parentheses around the SET shouldn't be there, that's probably why i's not working. Also, you don't need the single quotes around the 2 although that doesn't cause an error. You only need single quotes around non-numeric values.

After revExecuteSQL, always check the result. It will contain an integer if things worked OK. In this case it would have the number of rows updated by your UPDATE command. If an error occurred it will contain a non integer value that is the text of the error that occurred.

Finally, and this is a matter of personal preference, I open the database when my application starts up and close it when it quits. Continuously opening and closing a database adds extra overhead that I don't think is necessary. You'll find others that don't agree with that but for SQLite databases, I think that's the way to go.

Re: using UPDATE function in SQLite

Posted: Mon Jan 19, 2015 2:06 pm
by Klaus
With every new database app I create, Iususally go here to refresh my meager knowledge about SQL: http://www.w3schools.com/sql/default.asp
:D

Re: using UPDATE function in SQLite

Posted: Tue Jan 20, 2015 12:22 pm
by zaxos

Code: Select all

on mouseUp
put "UPDATE Persons SET Amplua='Rider',Name='Samuel',Lastname='Jackson',Origin='USA' WHERE Number='2'" into tSQL
revExecuteSQL DatabaseID, tSQL
if the result is not an integer then
answer the result
end if
--DatabaseID at this moment contains an actual database id, it's defined before!

revCloseDatabase DatabaseID
end mouseUp
You should get used to do some error checking, it helps :)