locked
PK-FK conflict with sync services RRS feed

  • Question

  • Hello,

    I am using N-tier synchronization for synching tables between mobile device and sql server. Basically there are 3 tables:

    1) timesheets (PK ts_is)
    2) timesheet-entries (PK te_id FK ts_id)
    3) timesheet cell entries (PK tec_id, FK te_id)

    Here is the sync service code where I am adding parent table before child table:

      syncAgent.Configuration.SyncTables.Add(tblTimesheets);
      syncAgent.Configuration.SyncTables.Add(tblTimesheetEntries);
      syncAgent.Configuration.SyncTables.Add(tblTimesheetCellEntries);

    However I get following error on ApplyChangeFailed on DbServerSyncProvider

    {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_tbl_timeentry_tbl_timesheet\". The conflict occurred in database \"VDOF_IFRIS_V2\", table \"gisadmin.tbl_timesheets\", column 'ts_id'.\r\nThe statement has been terminated."}

    I would appreciate if someone can please help me on this.

    Thanks

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

All replies

  • Also this is the error that I get when the server is trying to insert record in Timesheet-entries.

    Here is the code :

      SqlCommand insTimesheetCmd = new SqlCommand();
                    insTimesheetCmd.CommandType = CommandType.StoredProcedure;
                    insTimesheetCmd.CommandText = TableOwner + SPTimesheetInsert;
                 
                    insTimesheetCmd.Parameters.Add("@employee_id", SqlDbType.Int);
                    insTimesheetCmd.Parameters.Add("@time_sheet_status_id", SqlDbType.Int);
                    insTimesheetCmd.Parameters.Add("@ts_end", SqlDbType.DateTime);
                    insTimesheetCmd.Parameters.Add("@ts_remarks", SqlDbType.VarChar, 500);
                    insTimesheetCmd.Parameters.Add("@ts_hours_worked", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_hours_leave_taken", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_hours_ot_leave", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_hours_ot_pay", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_hours_comp_leave", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_cipps_hours", SqlDbType.Decimal, 10);
                    insTimesheetCmd.Parameters.Add("@ts_p14_hours", SqlDbType.Decimal, 18);
                    insTimesheetCmd.Parameters.Add("@ts_approved", SqlDbType.DateTime);
                    insTimesheetCmd.Parameters.Add("@ts_leave_status_id", SqlDbType.Int);

                    insTimesheetCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
                    insTimesheetCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
                    insTimesheetCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                    insTimesheetCmd.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Int);

                    adaptorTimesheets.InsertCommand = insTimesheetCmd;


    Please note that I am not adding parameter for timesheet PK (ts_id) because that is created on the server using identity.

    SqlCommand insTimesheetEntryCmd = new SqlCommand();
                    insTimesheetEntryCmd.CommandType = CommandType.StoredProcedure;
                    insTimesheetEntryCmd.CommandText = TableOwner + SPTimesheetEntriesInsert;
                
                    insTimesheetEntryCmd.Parameters.Add("@tr_tractnum", SqlDbType.VarChar, 8);
                    insTimesheetEntryCmd.Parameters.Add("@ts_id", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@cs_id", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@gr_id", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@sg_id", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@timecode_id", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@comp_code", SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@FeatureID", SqlDbType.UniqueIdentifier);

                    insTimesheetEntryCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
                    insTimesheetEntryCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
                    insTimesheetEntryCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                    insTimesheetEntryCmd.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Int);

                    insTimesheetEntryCmd.Connection = serverConnection;
                    adaptorTimesheetEntries.InsertCommand = insTimesheetEntryCmd;

    Please note that I am not adding parameter for timesheet entry PK (te_id) because that is created on the server using identity.

    Is Identity the reason for this error?

    Thanks

    Apurv
    Thursday, October 23, 2008 9:50 PM