Foxpro SQL Update - Command contains unrecognized phrase/keyword.

[Mastermunk]

I have to update a foxpro table named RMA which has the annoying feature of using keywords for column names.

Basically I have to update a column named 'desc' in the table this RMA. This is simple enough on SQL Server to handle (just using [desc]) but with Foxpro I get an error.

Sql Statement
----------------------------------------------------------

UPDATE RMA
   SET RMA.desc='(some lengthy text)'
 WHERE RMA.rmanumber=2626

Used in Code: (VBscript)
----------------------------------------------------------

sql="UPDATE RMA                                   "& _
    "   SET RMA.desc='"&replace(expl,"'","''")&"' "& _
    " WHERE RMA.rmanumber="&session("RMAnumber")
conntemp.execute(sql)

'Where expl is a concatenated string,
'RMAnumber is a long int,
'and conntemp is an open connection to foxpro

Error:
-----------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

/intranet/customerservice/rmamanager.asp, line 89


If I wasn't forced to use these column names this would be a no-brainer. If anyone can see the error of my ways or a another possible solution please reply.

 

[cslawtom]

Sql Statement
----------------------------------------------------------
UPDATE RMA
SET RMA.desc='(some lengthy text)'
WHERE RMA.rmanumber='2626'

use '2626' instead of 2626 - - same in VB code

 

[Mastermunk]

Thank you cslawtom but unfortunately I know that wont help (I did try it though ). I know the problem lies in the fact that a column has the same name as the keyword desc because I have tested updating with every other field in the same table and they all work. I have been looking all over the net for a solution to this and if anyone can tell me how to override keywords in Foxpro I would be very grateful.

 

[1oldfoxman]

I tried using the & macro substitution for the field name to get the SQL update expression to work and get a syntax error as well, in FoxPro6. (Using a memvar to hold the fieldname). Seems like there ought to be some way around this, inelegant as it may have to be. The field() function won't work in this type of referene either.

I'll try to think of some other ways and experiment.

 

[1oldfoxman]

The only other thing I can think of is creating a view on the FoxPro side, but I can't figure out how to create the view and assign an alternate field name as part of the Create View statement (column heading, yes, but I don't think that will give a referenceable value to identify the field). If there's a way to do that, you might be able to do the update through the view, as opposed to the table directly.

I don't have a whole lot of knowledge/experience with integrating VB/VFP. Can you apply native FoxPro command syntax as a VB script? I'm guessing not, that ODBC is strictly limited to SQL interaction. Too bad, because the REPLACE command doesn't return the syntax error like the SQL UPDATE.

That's all I got. Sorry.