Sqlite Update command using WHERE and Variables

[Quinton B.]

Hello, I'm trying to use an SQLite "UPDATE" command with a "WHERE" statement using variables. See code bellow the arrow:


-- button_Forgot_Password_ShowPassword
global gDatabaseID
on mouseUp
-- Hides the new passord fields if the check fields are not correct
set the visible of group "group_Fields" to true
set the invisible of button "button_Submit_New_Password" to true
set the invisible of field "text_Forgot_Password_Password" to true
set the invisible of field "field_Forgot_Password_Password" to true
-- put the text of each field into a variable
put the text of field "field_Forgot_Password_UserName" into tForgotPasswordUserName
put the label of button "button_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestion
put the text of field "field_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestionAnswer
-- statement for searching a database
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion =" && "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tGetLogIn
-- executes the search command
put revDataFromQuery(tab,return,gDatabaseID,tGetLogIn) into tFinalPasswordCheck
if tPasswordCheck is empty then
answer "Fields Are Not Correct. Please Make Sure They Are Correct, To Retrieve Your Password"
else
answer "Enter Your New Password"
set the invisible of group "group_Fields" to true
set the visible of button "button_Submit_New_Password" to true
set the visible of field "text_Forgot_Password_Password" to true
set the visible of field "field_Forgot_Password_Password" to true
-- The new password
put the text of field "field_Forgot_Password_Password" into tFPP


-- l l
-- l l
--\ /
-- \ /

-- Updates the password (resets it)
put "UPDATE MentorAccount SET Password='" & tFPP & "' ,ConfirmPassword='" & tFPP & "'Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion =" && "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tNewPassword
-- executes the search command
--REPLACE INTO tableName [(column-list)] VALUES(value-list)
put empty into field "field_Forgot_Password_UserName"
set the label of button "button_Forgot_Password_SecurityQuestion" to "Chose A Security Question"
put empty into field "field_Forgot_Password_SecurityQuestion"
--checks to make sure the password is different
if tFinalPasswordCheck = tUpdatePassword then
answer "Choose a Different Password"
end if
end if
-- clears all fields



end mouseUp

[Klaus]

Hi Quinton,

and what exactly is the problem?
Except a missing space before "Where UserName=", the SQL looks fine!?
...
put "UPDATE MentorAccount SET Password='" & tFPP & "' ,ConfirmPassword='" & tFPP & "' Where UserName ="...
...

Best

Klaus

[Quinton B.]

I get this error: button "button_Forgot_Password_ShowPassword": compilation error at line 37 (Expression: double binary operator), char 74

--Perfect Code

--Global Variable
Global gDatabaseID

on button_Forgot_Password_ShowPassword
// Hides the New password field if the user cannot answer a few verification questions to their account
set the visible of group "group_Fields" to true -- sets this group to visible so the user can enter the correct verification information so they can reset their password
set the invisible of button "button_Submit_New_Password" to true -- sets this button to invisible so the user can enter the verification information needed to reset their password
set the invisible of field "text_Forgot_Password_Password" to true -- sets the text to invisible so the user can enter the verification information needed to reset their password
set the invisible of field "field_Forgot_Password_Password" to true -- sets the field to invisible so the user can enter the verification information needed to reset their password

// Puts each field, text, or button label into a variable to enter the SQLite statement to be processed by the database and selected to verify information
put the text of field "field_Forgot_Password_UserName" into tForgotPasswordUserName -- puts the text of the field into a variable to be processed by the database
put the label of button "button_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestion -- puts the label of the button into a variable to be processed by the database
put the text of field "field_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestionAnswer -- puts the text of the field into a variable to be processed by the database

// Recalls data from the database and matches it to the users input, if its not correct it will display an error to enter the correct information in order to resert their password
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion =" && "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tGetLogIn -- slqite select command being used to verify the information inputed from the user to reset their password
put revDataFromQuery(tab,return,gDatabaseID,tGetLogIn) into tInputedPassword -- the command used to process the data through the database in the form of an sqlite statement

// This checks if the user inputted the correct information n order to reset their password
if tInputedPassword is empty then -- if statement being used to determine if the user put the correct information in the field in order to reset their password
answer "Please Enter the Correct Information" -- answers this statement if the user's information doesn't match that of the database
else -- used to be a fail safe if the first if fails

// If statement within an if statement that checks is the user input the correct information to change the password
if tInputedPassoword is not empty then -- if statement being used for the user that put the correct information in the field in order to reset their password
set the invisible of group "group_Fields" to true -- sets the group to invisible after the user entered the correct information t reset their password
set the visible of button "button_Submit_New_Password" to true -- sets the button to visible if the user put the correct information in the fields above (lines 64 to 67)
set the visible of field "text_Forgot_Password_Password" to true -- sets the text to visible if the user put the correct information in the fields above (lines 64 to 67)
set the visible of field "field_Forgot_Password_Password" to true -- sets the field to visible if the user put the correct information in the fields above (lines 64 to 67)
put the text of field "field_Forgot_Password_Password" into tUpdatedPassword -- places the user's new password into the variable tUpdatedPassword so that the password will be checked against the old one then processed by the database and a new password

// If statement within an if statement within an if statement to determine if the passwords are different then executes the password change within the database using an UPDATE command
if tInputedPassword <> tUpdatedPassword then -- checks the passwords against each other to see if they are different and if they are then moving n to process the new password to the database
put "UPDATE MentorAccount SET Password=" & tUpdatedPassword & , "ConfirmPassword=" & tUpdatedPassword & "WHERE UserName=" & tForgotPasswordUserName & "SecurityQuestion=" & tForgotPasswordSecurityQuestion & "SecurityQuestionAnswer=" & tForgotPasswordSecurityQuestionAnswer into tUpdateUsersDatabasePasswords -- Update command with where statement using variables to update a users password where the user inputs the correct verification information
revExecuteSQL gDatabaseID, tUpdateUsersDatabasePasswords -- executes the update command in the database
answer "Password Reset" -- displays to the user that their password has been reset
ResetGroupForgotPassword -- resets all fields with the if statement tResetForgotPassword
else -- used to be a fail safe if the first if fails
answer "Please Chose a Different Password" -- displays this error to the user if they don't enter a new password
end if
end if
end if
end button_Forgot_Password_ShowPassword

// Functions to reset group Forgot Password after entering a new password or hitting the back arrow
function ResetGroupForgotPassword -- clears all the fields above
put empty into field "field_Forgot_Password_UserName" -- clears anything in this field
set the label of button "button_Forgot_Password_SecurityQuestion" to "Chose A Security Question" -- sets this button to choose a security question
put empty into field "field_Forgot_Password_SecurityQuestion" -- clears anything in this field
put empty into field "field_Forgot_Password_Password" -- clears anything in this field
put empty into tInputedPassword -- clears anything in this variable
put empty into tUpdatePassword -- clears anything in this variable
put empty into tResetForgotPassword -- clears anything in this variable
set the visible of group "group_Fields" to true -- sets this group to visible after the password has been successfully changed
set the invisible of button "button_Submit_New_Password" to true -- sets this button to visible after the password has been successfully changed
set the invisible of field "text_Forgot_Password_Password" to true -- sets this text to visible after the password has been successfully changed
set the invisible of field "field_Forgot_Password_Password" to true -- sets this field to visible after the password has been successfully changed
set the layer of group "group_LogIn" to the top -- sets the default group to the top for logging in
end function

[Klaus]

Hi Quinton,

--Perfect Code

so this does compile? Please provide a tad more info!

Just tested, this does not compile, due to a COMMA outside of QUOTES:

Code: Select all

....
## put "UPDATE MentorAccount SET Password=" & tUpdatedPassword & , "ConfirmPassword=" & tUpdatedPassword & ...
put "UPDATE MentorAccount SET Password=" & tUpdatedPassword & ", ConfirmPassword=" & tUpdatedPassword & ...
...

But you should really start to use single quotes, at some point they will be required!


Best

Klaus

[Quinton B.]

No it doesn't. Same error. And still no new data in the database.

[Klaus]

Compiles fine here, see screenshot!
macOS 10.12.5, LC 9 dp6

[Quinton B.]

Yes, it does, but It does not change the passwords in the database.

[Klaus]

Hi Quinton,

No it doesn't. Same error.

OK, it compiles, right? What error do you see?
The script compiled without error here!

Please check "the result" right after executing the SQL:
...
revExecuteSQL gDatabaseID, tUpdateUsersDatabasePasswords
answer the result
...

Best

Klaus

[Quinton B.]

I've checked the result and nothing comes out, no errors either????

[Klaus]

Hi Quinton,

hm, did you check if your WHERE clauses really result in any record?
Know what I mean?


Best

Klaus