Save edited list back to the DB - SQLite
Posted: Sun Jun 14, 2015 1:08 am
I wanted to get some of the opinions of the SQLite experts on the forum. I have a native iOS input control (call it inputA) which is initially populated on an openCard script from the same table in the script below. The table only has one column and is only used to store the list of items which is then used in other places in the app. So, what I want to accomplish with the script below, is allow the user to edit the lines of control inputA and save the list back to the DB. They could add lines, remove lines or edit what's there. What I have works but it just seems like it could be more concise.
Code: Select all
on addEditElec
local tCurrentProps, nDBNames, tDBNames, tStuff
## Gather the list from the table
put "Select equipName FROM equipment" into tSQLStatement
put revDataFromQuery(tab,cr, the uDatabaseID of this stack ,tSQLStatement) into tDBNames
put the num of lines of tDBNames into nDBNames
## Gather the list from the input control
put mobileControlGet("AddElec","text") into tListPropNames
filter tListPropNames without empty
sort tListPropNames
put the num of lines of tListPropNames into nListPropNames
if nListPropNames > nDBNames then
-- add some rows to the column
put (nListPropNames - nDBNames) into addCount
put "NewCell" into tStuff
repeat with x= 1 to addCount
put "INSERT into equipment(equipName) VALUES ('"&tStuff&"')" into tSQLStatement
revExecuteSQL the uDatabaseID of this stack ,tSQLStatement
end repeat
else -- nCurrentProps < nDBNames
-- delete some rows from the column
put (nDBNames - nListPropNames) into delCount -- the number of rows to delete
delete line 1 to nListPropNames of nDBNames -- the PropIDs to delete
repeat with x= 1 to delCount
put line x of nDBNames into tDelete
put "DELETE FROM equipment WHERE equipID ='"&tDelete&"' " into tSQLStatement
revExecuteSQL the uDatabaseID of this stack ,tSQLStatement
end repeat
end if
repeat with x= 1 to nListPropNames
put line x of tListPropNames onto pPropName
put "UPDATE equipment SET equipName = '"&pPropName&"' WHERE equipID = '"& x &"' " into tSQLStatement2
revExecuteSQL the uDatabaseID of this stack ,tSQLStatement2
end repeat
if the result is "1" then
mobileControlSet "AddElec","text" , tListPropNames
answer information "List updated"
else
answer error "There was an error updating the list"
end if
end addEditElec