Problem Updating mySQL Record

Andrew Kluthe andrew at
Tue Feb 9 18:46:28 CST 2010

While not very pretty yet, I am trying to cobble together an update handler
for updating a "Payment" record.

It keeps trying to put NULL into "LeasePaymentDollars" field in my DB. This
is weird because I check the SQL query its trying to execute and everything
is as it should be. 

I have a sneaking suspicion that it has something to do with the
tLeasePaymentDollars variables Data type.

Here is the query that the handler is producing. Note: I first tried putting
single quotes around the value and it did not work, so I tried it without
and get the same result.

UPDATE tleasepayments SET LeasePaymentDollars=1337.0000 AND
CropSharePercent='N/A' AND Notes='None' WHERE LeaseID='1075'

Here is the handler itself.

on UpdatePayment pTable, pSetQuery, pConditional
    -- check the global connection ID to make sure we have a database
    global sDatabaseID
    if sDatabaseID 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 the text of field "fLeasePaymentDollars" into tLeasePaymentDollars
    put the text of field "fCropSharePercent" into tCropSharePercent
    if tCropSharePercent is empty then put "N/A" into tCropSharePercent
    put the text of field "fNotes" into tNotes
    if tNotes is empty then put "None" into tNotes
    put "tleasepayments" into pTable
    put "LeasePaymentDollars=" & tLeasePaymentDollars && "AND" &&
"CropSharePercent='" & tCropSharePercent & "'" && "AND" && "Notes='" &
tNotes & "'" into pSetQuery
    put "WHERE LeaseID='" & thePaymentID & "'" into pConditions
    #Crop Year
    put "UPDATE" && pTable && "SET" && pSetQuery && pConditions into tSQL
    -- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be
filled by variables in the revExecuteSQL line
    #UPDATE Table1 SET birthDate='12/06/1970' WHERE firstName='Mary' AND
    #put "UPDATE " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3)"
into tSQL
    put tSQL into field "test"
    -- send the SQL to the database, filling in the placeholders with data
from variables
    #revExecuteSQL sDatabaseID, tSQL
    -- check the result and display the data or an error message
    if the result is a number then
        answer info "Record Updated."
        answer error "There was a problem adding the record to the
database:" & cr & the result
    end if
end UpdatePayment


View this message in context:
Sent from the Revolution - User mailing list archive at

More information about the use-livecode mailing list