Delete a line in SQLite

[KennyR]

Ok...I have spent countless hours trying to figure this out, and I hate to have to ask the community for help but I have no choice... I am trying to allow a user to delete a database entry when they select the line in a list field. In other words, I populate the list field with the contents of my database, and the user can select a line and choose to delete it from the records. I have been using the example from the lessons section regarding connecting and adding database entries, and I have modified it to create the type of table and column headers I want. The table "goals" simply consists of two columns "name" and "goal". The user types some text into a field called "trialField" and a name of the trial into a field "goalName", this then makes my database entry. I am having issues querying and deleting a single record.... I have tried the "DELETE FROM goals WHERE goal = blah blah blah....in every conceivable form and fashion but I am too dense to get it.... can anyone help an idiot out here? thanks...

Code: Select all

## Use a script local variable to store the database id
local sDatabaseID

## Handlers to get and set the variable storing the database id
command setDatabaseID pDatabaseID
   put pDatabaseID into sDatabaseID
end setDatabaseID

function getDatabaseID
   return sDatabaseID
end getDatabaseID

command databaseConnect
   local tDatabasePath, tDatabaseID
   
   ## The database must be in a writeable location
   put specialFolderPath("documents") & "/runrevemails.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id as a custom property so other handlers can access it
   setDatabaseID tDatabaseID
end databaseConnect

command databaseClose
   revCloseDatbase sDatabaseID
end databaseClose

on databaseCreateTables
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE goals (name char(50), goal string)" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseCreateTables

on databaseDeleteTables
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "delete from goals" into tSQL 
   revExecuteSQL tDatabaseID, tSQL
end databaseDeleteTables


on databaseInsertContactDetails   
   ## Insert names and email addresses into the database
   put getDatabaseID() into tDatabaseID
   global vGoalName
   global vGoalText
   put fld "goalName" into vGoalName
   put fld "trialField" into vGoalText
   
   put "INSERT into goals VALUES ( '"& vGoalName & "', '"& vGoalText & "'  );" into tSQL
   
   revExecuteSQL tDatabaseID, tSQL
end databaseInsertContactDetails


function databaseGetContactDetails
   ## Query the database for contact details to be displayed in the field
   put getDatabaseID() into tDatabaseID
   put "SELECT * from goals"  into tSQL
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
   return tRecords
end databaseGetContactDetails

[FireWorx]

I am not qualified to provide you an answer off the cuff. However I do regularly access this web site for all kinds of easy answers to HTML, JavaScript and SQl questions. Here is the link. With specific directions regarding the delete command. Good luck.

http://www.w3schools.com/sql/sql_delete.asp

Dave

[sturgis]

Used your code to set up a db.

Then set up this quicky handler just to show how to get a successful row delete.

Code: Select all

command deleteRow pVar
   put merge("delete from goals where goal = '[[pVar]]';") into tSql
   revexecutesql sDatabaseId,tSql
   put the result
end deleteRow

This is only deleting against column goal, so of course the correct data must be passed. Also make sure (since this is a string value) to include the single tick quotes around the value itself in the comparison. goal = 'value here'; The merge will shove the passed value into the string at [[pvar]].
Assuming the value exists in that column, after the revexecute the result should contain 1. (or more if there are duplicate rows) if it contains 0 the delete failed meaning there was no match. If something else is wrong it will contain the error. In this case if you leave out the single tick quotes the confusing message in the result will be something like "no such column: [[the value from pvar]]"

I am not qualified to provide you an answer off the cuff. However I do regularly access this web site for all kinds of easy answers to HTML, JavaScript and SQl questions. Here is the link. With specific directions regarding the delete command. Good luck.

http://www.w3schools.com/sql/sql_delete.asp

Dave

[KennyR]

Thank you for creating this handler and I appreciate you taking time out of your day to help me, but I am not advanced enough to figure out how to implement it. I have copied it and pasted it to my card script, but that is about as far as I can get with it....I am not sure what 'Pvar" is nor how to use it...I feel lost...

[sturgis]

Ah k.

pvar is a parameter variable. (it could be named anything but I name it that way to remember that is is an argument passed to the handler"

So, to test out the handler

The handler name is deleteRow
It accepts 1 parameter that I called pVar

If you want to try it in the message box you would type

deleteRow "the text I want to match from the goal column of my database"
When you hit enter it will call deleteRow, put the text between the "" into pVar and then go on from there

Code: Select all

command deleteRow pVar
/*
Merge is a really cool command.  You hand it a string in this case "delete from goals where goal ='[[pVar]]';" and evaluates it
In this case since pVar is holding the string that you used when you started the command up, it evaluates things and replaces [[pVar]] with the actual value in pVar so you end up with:
delete from goas where goal = 'the text I want to match from the goal column of my database'; 
and then it puts that into tSql*/

   put merge("delete from goals where goal = '[[pVar]]';") into tSql

-- tSql now contains delete from goals where goal = 'the text I want to match from the goal column of my database'; 
   revexecutesql sDatabaseId,tSql -- sends the delete to the database

-- if there is no match to the string (text) that was passed to the command in the database
-- the result will = 0 
-- if there IS a match, (1 or more records) the result will contain the number of rows affected. So if 3 rows match, the result will contain3
-- if there is an error of some type the result will contain the error message
   put the result
end deleteRow

So, say you look up a row. The text of the row is placed into a field "name" and field "goal" and you want to delete all rows that match the text of field "goal" to accomplish this you could have a button with the following

Code: Select all

on mouseUp
     deleteRow (field "goal")
end mouseUp

After you mess with it a bit you'll of course want to NOT put the result into the message box but will want to use it possibly to log actions or update a status bar or whatever. The handler can be adjusted for that pretty easy.

after the revexecutesql statement something like

Code: Select all

switch
case the result = 0
-- code to let the use know that no matching records were found
break
case the result is not an integer
-- code to let the user know there was an error of some type, and display the error
break
default
-- code to let the user know how many records were affected
end switch