Problem Updating mySQL Record

[Andrew Kluthe]

DATABASE TABLE STRUCTURE of tleasepayments

LeaseIDint(11) NOT NULL
LeaseNamevarchar(50) NOT NULL
LeaseCropYearsmallint(5) NOT NULL
LandOwnerID varchar(40) NOT NULL
LeasePaymentDate timestamp NOT NULL
LeasePaymentDollars decimal(19,4) NULL <------- The problem field.
CropSharePercent decimal(18,4) NULL
AssetIDvarchar(25) NULL
Notesvarchar(255) NULL



It must be my query because even when given directly to the database (not
through rev) it still does not update correctly and puts null in all the
nullable values. I realized I was trying to put a string into
CropSharePercent and fixed it to put zero instead. But it still does the
same thing it was.

The below code outputs this query.

UPDATE tleasepayments SET LeasePaymentDollars=21337.0000 AND
CropSharePercent='0' AND Notes='None' WHERE LeaseID='1213'

Should I ditch the single quotes on variables that are decimals or integers?
In my DB if the datatype is decimal can it have a "0" value or must it be
"0.0"?


I cleaned up the code a bit. And explained the questionable variables, but
am hesitant to spend time pulling this out of my program and into a stack
for the sake of figuring out my query problems. At this point I am very sure
I am mismatching datatypes in my DB or setting my query up wrong.

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 
     
  
    put the text of field "fLeasePaymentDollars" into tLeasePaymentDollars
    put the text of field "fCropSharePercent" into tCropSharePercent
    if tCropSharePercent is empty then put "0" into tCropSharePercent
    
    put the text of field "fNotes" into tNotes
    if tNotes is empty then put "None" into tNotes
    
    #below are just place holders for passing my parameters eventually.
    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
    
    #dump the query So I can See what it is putting out
    put tSQL into field "test" 

    -- send the SQL to the database
    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




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