SQL issue when syncing to remote user database [ Edited ]

[wilsonat]

I have a custom merge application that is written in C# and exposed in the network client (7.2.1) as a .NET extension. The merge application allows users to merge together accounts, contact, and opportunities. As part of the account merge, I update all the one-to-many relationships with the following SQL statement (exectued through the OleDb provider): update <table> set <idfield> = ? where <idfield> = ?

For instance, when merging two accounts together, the opportunity table will be updated to point all opportunities owned by the merging account to the maintained account. The problem is that when the client sync actually parses the TEF file, it changes the SQL. For example:

Executed on the network database: update Opportunity set AccountId = 'A61230000001' where AccountId = 'A61230000002'

Executed on the remote database: update Opportunity set AccountId = 'A61230000001' where OpportunityId = 'A61230000002'

The 'where' clause was modified to be invalid. The transaction viewer indicates this is a transaction of type "Update2", with the following transaction data:

    Table Name = OPPORTUNITY
    Field Name = ACCOUNTID
    Value = A61230000001
    Old Value = A61230000002

Any ideas or suggestions about what may be going wrong? Can anyone tell me why the transaction type isn't "ParamSQL" like most of my other transactions?

Message Edited by wilsonat on 03-16-2010 11:02 AM

[RJSamp]

Include the OpportunityID in your where clause as well......SLX syncs by RowID......each row is an individual transaction.....so loop through the ADO recordset of Opportunity's and change EACH opportunity's AccountID where OpportunityID = 'whatever'......

SLX doesn't do PARAMSQL TEF's across main entity fields....they're each their own standalone transaction.....

syncing is by ACCOUNTID.......transactions are by AccountID...... one row at a time.