Using Global Variables within SQL Query

[Jamie37]

Hello,

I followed the Livecode tutorial for basic connection and record adding and deleting in a SQL database. The one question I have is I have two input field for testing (Username and Password). The add record button puts these field names into a global variable and add the records to the database. The part I cannot do is the delete button. I want the delete button to take these variable and if there is matching records in the database, I want the record to be deleted. Hopefully I am explaining this well but hard to explain. Below is the code for the Delete button. I think I have made the global variables correct but cannot work out how to add them to the query.

Code: Select all

global tUsername
global tPassword

on mouseUp
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first"
exit to top
end if

-- since you don't need a where clause, its really straight forward. No Where means all rows removed.
put "DELETE FROM Logininfo WHERE Username= 'tUsername' AND Password= 'tPassword'" into tSQL 

revExecuteSQL gConnectionID, tSQL

 if the result is not number then
    answer "All Records Deleted"
 else answer error "There was a problem clearing your table" & cr & the result
end mouseUp

Thanks
Jamie

[Klaus]

Hi Jamie,

you are supplying the STRINGS tUsername and tPassword and not the content of these variables!
...
## put "DELETE FROM Logininfo WHERE Username= 'tUsername' AND Password= 'tPassword'" into tSQL
put "DELETE FROM Logininfo WHERE Username='" & tUsername & "' AND Password= '" & tPassword & "'" into tSQL
...

Best

Klaus

[Jamie37]

HI Klaus,

That seems to delete all the records whatever I have in the fields?

[SparkOut]

While debugging, "answer tSQL" after the statement constructing the query and before executing, to check it matches your expectations.
Your code comments seem to imply you want to delete records with no where clause, but if you do, are the columns correctly stated, along with the correct variable contents? Klaus showed the correct way to build your query. Now you need to make sure that your query does what you expect.