sql in livecode

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Samuele
Posts: 282
Joined: Mon Oct 11, 2021 7:05 pm
Location: Italy

sql in livecode

Post by Samuele » Wed Nov 17, 2021 11:24 pm

hi, i got this error while trying to edit a record on a database can someone help me please?

Code: Select all

global gConnectionID
on mouseUp
   -- check the global connection ID to make sure we have a database connection
   
   if gConnectionID is not a number then
      answer error "Please connect to the database first."
      exit to top
   end if
   	
   -- edit these variables to match your database & table
   -- this assumes a table called Table1 with 3 fields
   put "Persons" into tTableName
   put "UserName, HighScore" into tFields
   #put "Elanor" into tFirstName
   put field "Try" into tFirstName
   put field "Score" into tLastName
   #put the short date into tBirthDate	-- this is nonsensical but gives some variation to the data
   	
   -- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
   #put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2)2 into tSQL
   #put "UPDATE " & tTableName &  " SET HighScore= " " VALUE (:1)" " WHERE UserName= " "VALUE (:2)" into tSQL
  put "UPDATE " & tTableName & " SET HighScore= " & " VALUE (:1) " & "WHERE UserName=" & " VALUE (:2)" into tSQL
   -- send the SQL to the database, filling in the placeholders with data from variables
   revExecuteSQL gConnectionID, tSQL, "tLastName", "tFirstName"
   	
   -- check the result and display the data or an error message
   if the result is a number then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if
end mouseUp

(the error is given on a popup message saying to check the sql syntax, it seems okay to me...)
thanks!
Samuele.

cpuandnet
Posts: 32
Joined: Thu Jan 17, 2019 6:43 am

Re: sql in livecode

Post by cpuandnet » Thu Nov 18, 2021 9:03 pm

Remove the VALUE keyword and parenthesis from the parameters in your UPDATE sql statement. Try this instead.

Code: Select all

put "UPDATE " & tTableName & " SET HighScore= " & " :1 " & "WHERE UserName=" & " :2" into tSQL
 
I would also recommend simplying the statement to something with less concatenations like this

Code: Select all

put "UPDATE " & tTableName & " SET HighScore= :1 WHERE (UserName = :2)" into tSQL

Post Reply

Return to “Databases”