Problem Updating mySQL Record
andrew at rjdfarm.com
Wed Feb 10 08:35:45 EST 2010
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
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
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
if sDatabaseID is not a number then
answer error "Please connect to the database first."
exit to top
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
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."
answer error "There was a problem adding the record to the
database:" & cr & the result
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.
More information about the Use-livecode