Problem Updating mySQL Record

[Andrew Kluthe]

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
connection
    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
    #LandOwner
    #PaymentDate
    
    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
lastName='Smith'
    #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."
    else
        answer error "There was a problem adding the record to the
database:" & cr & the result
    end if
end UpdatePayment



wtf?

-- 
View this message in context: http://n4.nabble.com/Problem-Updating-mySQL-Record-tp1475274p1475274.html
Sent from the Revolution - User mailing list archive at Nabble.com.