RE: Problem Updating mySQL Record
The ANDs were jacking my query up. Commas used instead makes it work just as it should. UPDATE tleasepayments SET LeasePaymentDollars=21337. , CropSharePercent='0' , Notes='PUT SOME NOTES HERE' WHERE LeaseID='1214' grr, COMMAS! You win this time... -- View this message in context: http://n4.nabble.com/Problem-Updating-mySQL-Record-tp1475274p1475880.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Problem Updating mySQL Record
Ok, I have spent some time trying to smooth that query out. I got the query to work if I took out LeasePaymentDollars=1337. I also got the query to work if I take everything BUT LeasePaymentDollars out. I didn't re-write the query and the spellings/syntax are the exact same. So it works when broken up but not when put together? Wtf? Bizzare. -- View this message in context: http://n4.nabble.com/Problem-Updating-mySQL-Record-tp1475274p1475811.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Problem Updating mySQL Record
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. 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.
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
RE: Problem Updating mySQL Record
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. 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@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Problem Updating mySQL Record
Originally I had a database error saying that the field cannot be null. I changed it in the table to allow null and see what it was placing. No errors after I made it nullable. The fields Datatype is decimal and the length is 19,4. I am fairly baffled. Is this a rev problem or a mySQL problem? -- View this message in context: http://n4.nabble.com/Problem-Updating-mySQL-Record-tp1475274p1475325.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: Problem Updating mySQL Record
On Wed, Feb 10, 2010 at 10:46 AM, 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. AND > CropSharePercent='N/A' AND Notes='None' WHERE LeaseID='1075' There doesn't appear to be any problem with this SQL. What is the data type for LeasePaymentDollars in the database table? When you execute the query, do you get an error report? Cheers, Sarah ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Problem Updating mySQL Record
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. 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@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
