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