David, Without the with/endwith it DOESN'T work but only sometimes!!! ...however this only as per the help on SQL Update where it says it should never work. The fact that I had a program where it DID work and whatever I did made it not work is what threw me for a while.
As I say the fact it is inconsistent is the annoying thing. If it broke every time I would have found the error easier. As a matter of interest I also found out by chance that you don't need a With Thisform......endwith if you are referring to an object that is a property of "Thisform". You can simply wade straight in with .<Object_Name>. Now that IS weird! Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Stevenson Sent: 30 March 2007 16:49 To: [email protected] Subject: RE: SQL Update and Objects - DOH! Dave, I assume that this version DOES work? (lose the WITH/ENDWITH and put the entire object reference): *With oObj Update curTemp ; Set Id=oObj.Id ; Where Primary_Key=2 *Endwith Looks like the inconsistency you mention is only if you're using WITH/ENDWITH. Perhaps the HELP file is erring on the side of caution when saying you should store to a memvar first. David Stevenson http://talkingfox.blogspot.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Crozier Sent: Friday, March 30, 2007 6:10 AM To: [EMAIL PROTECTED] Subject: SQL Update and Objects - DOH! To All, I have just come up against a really strange inconsistency error in VFP9 (may be other versions as well but I can't check). I usually scatter fields from a data record to an object using "scatter name..." and then use either: Gather name <<object>> Or replace <<field>> with <<object.Field>> command(s) to put them back into the table. Just for once I decided top use an SQL Update command to do the equivalent and it raised an error which took a while to sort out and some scratching of the head: With <<object>> Update <<table>> ; Set <<Field1>>=<<.Field1>>, ; ...etc Where... * Endwith Results in an error when using ".Field1" object reference - what was going on??? The error I found out when looking at the VFP help file is caused by... " If you want to use an object property in an expression, you need to specify a memory variable and then use that variable in the expression." i.e you can't use object properties in the SQL update statement. HOWEVER, if you try the small program below you will find the inconsistency - despite the fact that it should throw an error in the first "Update" it doesn't. The second "Update" does in fact fail correctly as per the help warning. It is this inconsistency that has caused me a small headache this morning and a bit of head scratching as (1) I wasn't aware of the potential error and (2) the rule doesn't seem to be consistent. I wonder if any of you can try it in other flavours of VFP? The Moral: Any of you out there who use SQL update beware of Object references. Dave Crozier (Example Below) ************ * Start of Code * Create Cursor curTemp(Primary_Key I, Id C(10), Date D) Insert Into curTemp Values(1,"A01", Date()) Insert Into curTemp Values(2,"A02", Date()+1) Insert Into curTemp Values(3,"A03", Date()+2) Select curTemp Goto 2 Scatter Name oObj Memo oObj.Id="B99" oObj.Date=Date()-1 * This update works but shouldn't * as it has an "." Object reference Update curTemp ; Set Id=oObj.Id, ; Date=oObj.Date ; Where Primary_Key=2 With oObj * This Update Fails as it should Update curTemp ; Set Id=.Id ; Where Primary_Key=2 * Endwith Select curTemp Browse * End of Code ***************** -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.20/736 - Release Date: 27/03/2007 16:38 [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
