locked
Insert-Sync doesn't work on bidirectional Table (update and delete does...) RRS feed

  • Question

  • Hello There.


    I'm developing a simple WM6 Application (VS2008, C#, SQLCE 3.5) which uses the ADO.Net Sync Services to sync a few Tables in Download mode (which works fine) and just one Table in bidirectional mode. That one Table stores shopping-Tasks (things to buy) and should therefore be able to be synced in both directions with Insert, update and delete ability. Deletion and Updates of records that initially came from the server (SQL 2008, Change Tracking on DB / all Tables) works fine. however Inserts on the client just won't synchronize.

    Here's some detail on the issue (case of insert on the client):

    - after the process of sync finished the SyncStatistics object returned by the ClientSyncAdapter.Synchronize() tells me that 1 record was successfully uploaded. There is no insert in the server-db though. (Insert on Client side happened manually, using SQL-CE-Commands)

    - i was initially using (generated) typed sqlCeResultSets to do the inserts on the client-side. initially the primary-keys on the tables were int. i randomly experienced duplicated key exceptions and therefore changed the primary keys to guids. the dataset respectively the sqlCeResultSets that were generated from the datacache (which was generated by wizard from the server-db) now contained a "duplicate" id-field (original ID column was ID_ACTIVITY, now there is an additional one ID_ACTIVITY1, also GUID and also with a NOT NULL constraint). Inserts using the sqlCeResultSet's generated Add-Method do not work anymore because the parameterlist lacks that ID_ACTIVITY1 field.

    -The above described ID_ACTIVITY1 field can only be seen on the WM-Clients database, using the Query Analyzer Tool

    -Manual Inserts do work locally but are not being synchronized (manually inserting data on system-columns like SYS_CHANGE_VERSION...)


    know i have no clue why firstly, the sqlCeResultSets are being generated the way they are and secondly why the application is not able to synchronize with the server...

    i'd greatly appreciate any help, tipps tricks or links to related issues and such.

    Greetings

    Sam
    • Moved by Tina_Tian Friday, April 22, 2011 7:58 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, October 23, 2008 8:17 AM

All replies

  • Can you try recreating the offline cache? I dont think you can easily "update" your cache schema and queries on making  a schema change. So one workaround is to drop the cache and recreate it

     

    Thursday, October 23, 2008 3:22 PM
    Moderator
  • Hello mahjayar,
    First of all, thank you for your reply... i've recreated the offline cache quite a few times already.. the sdf file looks allright as well as the generated datasets or sqlCeResultsets (i tried using both of them..) after the initial sync happened on the device, i'm still getting the awkward ID_ACTIVITY1 (note here: ID_ACTIVITY is part of the original db, but not ID_ACTIVITY1) which is no part of the dataset (or resultset).
    creating a row using either of them result in a "column cannot contain null"-exception.

    kind regards

    sam
    Thursday, October 23, 2008 3:40 PM
  • Issue is partly resolved now. Sync works now, there was a problem with the code on my side. sorry for the trouble.
    still there are those "magic"-columns beeing inserted when the database is being created by the initial sync-process. to give you a better view on it, i give you the columns for the same table on the server and the client side..

    server:

    ID_ACTIVITY (GUID, IDENTITY, NOT NULL)
    AMOUNT
    DESCRIPTION
    FK_ACTIVITYTYPEID (GUID, Foreign key)
    TIMESTAMP (TIMESTAMP, used for historisation)

    client:
    the columns on the client are of the same type as the columns on the server..
    ID_ACTIVITY
    ID_ACTIVITY1
    AMOUNT
    DESCRIPTION
    TIMESTAMP
    SYS_CHANGE_VERSION
    SYS_CHANGE_OPERATION
    SYS_CHANGE_CREATION_VERSION
    SYS_CHANGE_COLUMNS
    SYS_CHANGE_CONTEXT
    __sysChangeTxBsn
    __sysInsertTXBsn

    due to this situation, the DataSets / SqlCeResultsets generated from the DataCache (which was being generated via VS2008 Tools referencing the server-database) wont work for inserts (they do work for updates and deletes..).
    Insertion has to be done manually, using SqlCeCommands.
    As a workaround i insert a copy of the ID_ACTIVITY value into the ID_ACTIVITY1 field, also i set the following Columns:
    SYS_CHANGE_VERSION = 1
    SYS_CHANGE_OPERATION = 'I'
    SYS_CHANGE_CREATION_VERSION =1

    After the manual insert the sync does well (SyncStatistic says 1 upload, 1 download which refers to the loopback phenomenon).


    Maybe there's a better approach than the manual insert. If so, i'm very keen to implementing it.
    kind regards

    sam


    Friday, October 24, 2008 7:51 AM