John,

>Thanks for your prompt response. When the UPDATE seemed to not be working for this particular table (TABLEUPDATE(.T.,.T.) returned .F.), I swithced it over to Manual Update so I could isolate the command.

Going back to CA automatic updates, I tried a test with my CA classes against a SQL Server table with an order field. Here is what I found:

1) The problem is completely with SQL Server and not with VFP. VFP can properly interpret the ?table.order construction as it creates the parameterized statement to send to SQL Server, but SQL will choke on the fieldname (order=) part of it unless the fieldname is surrounded by [] square brackets.

2) The CA SelectCmd property must have the field "order" enclosed in square brackets if you specify fieldnames and don't use select * from... You can handle this in code or you can trap the BeforeCursorFill() event and STRTRAN the cSelectCmd parameter there (do a DODEFAULT first with the full list of params before you change the param).

For example, setting the SelectCmd in code like this works:

SelectCmd = ;
  "select CD_ID, CD_VAL, [order], " + ;
  "CD_DESC, MOD_DTTM, MOD_BY_ID, CONV_BAT_ID " + ;
  "from MyTable"

3) Once you have the data in a VFP cursor with a field named "order" in the cursor, VFP can handle it OK, including constructing the update statement and handling the ?table.order substitution, but cannot automatically change the update command to put brackets around the order field before sending to SQL Server without a little fancy footwork in defining properties or trapping events.

I tried putting quotes around that table.field in the UpdateNameList property, but CA completely ignored the field while constructing the update statement (UpdateNameList is used as a cross-reference to figure out what SQL server names to use for each field in the VFP cursor). This did not work:

UpdateNameList = ;
  "code_id MyTable.CD_ID, code MyTable.CD_VAL, " + ;
  "code_desc MyTable.CD_DESC, mod_dttm MyTable.MOD_DTTM, " + ;
  "mod_by_id MyTable.MOD_BY_ID, " + ;
  "conv_bat_id MyTable.CONV_BAT_ID, order [MyTable.order]"

However, using this syntax in UpdateNameList DOES work:

order MyTable.[order]

With the fieldname in brackets AFTER the "table.", CA cross-references the VFP order field with MyTable.[order] and strips off the MyTable." part of it, leaving [order], which it then puts into the Update statement. Here is the resulting VFP SQL statement, trapped in the BeforeUpdate method in the cUpdateInsertCmd parameter:

UPDATE MyTable SET [order]=?codes.order,
MOD_DTTM=?codes.mod_dttm,MOD_BY_ID=?codes.mod_by_id 
WHERE CD_ID=?OLDVAL('code_id','codes') AND CD_VAL=?OLDVAL('code','codes')
AND MOD_DTTM=?OLDVAL('mod_dttm','codes')

BTW, to see what is about to be sent to SQL Server, you can do this in the BeforeUpdate method of CA:

FUNCTION BeforeUpdate(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)
DODEFAULT(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)
_cliptext = cUpdateInsertCmd
ENDFUNC

Then you can paste from the clipboard to see what the cUpdateInsertCmd looked like.Alternatively to setting up the brackets in the UpdateNameList, you can put code in the BeforeUpdate method to STRTRAN the field:

FUNCTION BeforeUpdate(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)
DODEFAULT(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)
cUpdateInsertCmd = STRTRAN(cUpdateInsertCmd, "order=?", "[order]=?")
_cliptext = cUpdateInsertCmd
ENDFUNC

Either of these approaches will result in success, but I like the UpdateNameList much better because it will be used both for Update and Insert commands. Otherwise, you would have to trap both the BeforeUpdate and BeforeInsert events.

Because VFP takes the SQL shown in cUpdateInsertCmd in BeforeUpdate and further works it over before sending to SQL Server, if you look in SQL Profiler (just open it up and start a new Trace before issuing the tableupdate) you see this for the original error version:

exec sp_executesql N'UPDATE MyTable SET order=@P1 ,
MOD_DTTM=@P2 ,MOD_BY_ID=@P3  WHERE CD_ID=@P4  AND 
CD_VAL=@P5  AND MOD_DTTM=@P6 ', N'@P1 float,@P2 varchar(23),
@P3 varchar(8),@P4 varchar(10),@P5 varchar(3),@P6 varchar(23)',
 2.000000000000000e+000, '2006-07-21 15:49:07.380', 
'david   ', 'ADV_INSTR ', 'A  ', '2006-07-21 15:40:02.387'

and this for the corrected version:

exec sp_executesql N'UPDATE MyTable SET [order]=@P1 ,
MOD_DTTM=@P2 ,MOD_BY_ID=@P3  WHERE CD_ID=@P4  AND 
CD_VAL=@P5  AND MOD_DTTM=@P6 ', N'@P1 float,@P2 varchar(23),
@P3 varchar(8),@P4 varchar(10),@P5 varchar(3),@P6 varchar(23)',
 2.000000000000000e+000, '2006-07-21 15:49:07.380', 
'david   ', 'ADV_INSTR ', 'A  ', '2006-07-21 15:40:02.387'

The only difference is that the field "order" is now surrounded by brackets. However, both of them show that VFP correctly interpreted the ?codes.order before turning it into a parameter to send to SQL Server. So, the problem is with SSvr needing the order= to be [order]=.

Hopefully, this will help point you in the right direction toward configuring CA to automatically handle the updates for you.

BTW, I haven't tested it, but you probably could do the same with a Remote View automatically handling the brackets by using CursorSetProp on the cursor to set the UpdateNameList cursor property the same way. CA with DataSourceType="ODBC" is really just a wrapper around SQL Passthrough, but with auto-creation of insert/update/delete statements like Remote View -- it is like a subclassable Remote View with event-trapping. Many of CA's properties directly map to cursorgetprop/setprop properties.