Problem Updating mySQL Record

[Jim Bufalini]

Hi Andrew,

It's difficult to look at long code in an email especially when lines are
wrapped. So you may want to make available a sample stack that contains
enough code that demonstrates what isn't working for download (don't try to
attach it to an email to this list). In this case it should also have the
schema of the DB. Also, for me to really look at code, I like to see
explicit variable declaration (my preference). But, just looking through the
below (and I assume you copied this right out of your editor) there are a
couple of anomalies that I see:

1. *if tCropSharePercent is empty then put "N/A" into tCropSharePercent* Is
this column typed as numeric in your DB schema?

2. *put "tleasepayments" into pTable* Shouldn't this be the opposite? 

3. Your construction of pSetQuery and pConditions together with the comment
lines are difficult to read in this email. Try using the *format* command
and "wrap" your lines using the "\" char, even in a stack script. This will
allow more people to easily read, jump in, and pick up on what may be the
problem and you will get the answer you need from this list a lot quicker.

Aloha from Hawaii,

Jim Bufalini



Andrew Kluthe wrote:

> 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.
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution