none
An SqlParameter with ParameterName '@blabla' is not contained by this SqlParameterCollection.

    Question

  • Hi,

    You can find my code below. IT's directly inspired from this article:
    http://jaliyaudagedara.blogspot.fr/2013/02/synchronize-with-parameter-based.html

    The row with PopulateFromTemplate returns that error:
    An SqlParameter with ParameterName '@agentCo' is not contained by this SqlParameterCollection.

    I don't really understand why I get taht error.

    Thanks for your help.

    François

    static void Main(string[] args)
            {
                SqlConnection serverConn = null;
                SqlConnection clientConn = null;
                try
                {
                    // create a connection to the SyncDB server database
                    serverConn = new SqlConnection("Data Source=MY-PC\\SQLEXPRESS; Initial Catalog=Immo; Integrated Security=True");
                    // create a connection to the SyncCompactDB database
                    clientConn = new SqlConnection("Data Source=MY-PC\\SQLEXPRESS;Database=locImmo;uid=blabla;Pwd=blablabla;");
                    #region template
                    string filterTemplate = "filter_template";
                    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(filterTemplate);
                    scopeDesc.UserComment = "Template for filtering based on agentCo.";
                    List<DbSyncTableDescription> tablesDesc = new List<DbSyncTableDescription>();
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Biens", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("BonsVisite", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Lots", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Mandats_Gestion", serverConn));
                    //tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Mandats_Location", serverConn));
                    //tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Mandats_Recherche", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Mandats_Transaction", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Photos", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Proprietaires", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Recherches", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TypeBiens", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TypeChauffage", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TypeCivilite", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TypeExclusivite", serverConn));
                    tablesDesc.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TypeLots", serverConn));
                    foreach (DbSyncTableDescription tableDesc in tablesDesc)
                        scopeDesc.Tables.Add(tableDesc);
                    //creating a provisioning template
                    SqlSyncScopeProvisioning serverProvisionTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
                    SqlParameter param = new SqlParameter("@agentCo", SqlDbType.VarChar, 80);
                    //biens
                    serverProvisionTemplate.Tables["Biens"].AddFilterColumn("proprietaire");
                    serverProvisionTemplate.Tables["Biens"].FilterClause = "[side].[proprietaire] in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo))";
                    serverProvisionTemplate.Tables["Biens"].FilterParameters.Add(param);
                    //bons de visiste
                    serverProvisionTemplate.Tables["BonsVisite"].AddFilterColumn("bien");
                    serverProvisionTemplate.Tables["BonsVisite"].FilterClause = "[side].[bien] in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo)))";
                    serverProvisionTemplate.Tables["BonsVisite"].FilterParameters.Add(param);
                    //lots
                    serverProvisionTemplate.Tables["Lots"].AddFilterColumn("bien");
                    serverProvisionTemplate.Tables["Lots"].FilterClause = "[side].[bien] in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo)))";
                    serverProvisionTemplate.Tables["Lots"].FilterParameters.Add(param);
                    //Mandats gestions
                    serverProvisionTemplate.Tables["Mandats_Gestion"].AddFilterColumn("bien");
                    serverProvisionTemplate.Tables["Mandats_Gestion"].FilterClause = "[side].[bien] in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo)))";
                    serverProvisionTemplate.Tables["Mandats_Gestion"].FilterParameters.Add(param);
                    //Mandats locations
                    /*serverProvisionTemplate.Tables["Mandats_Location"].AddFilterColumn("bien");
                    serverProvisionTemplate.Tables["Mandats_Location"].FilterClause = "[side].[bien] in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo)))";
                    serverProvisionTemplate.Tables["Mandats_Location"].FilterParameters.Add(param);*/
                    //Mandats recherches
                    /*serverProvisionTemplate.Tables["Mandats_Recherche"].AddFilterColumn("proprietaire");
                    serverProvisionTemplate.Tables["Mandats_Recherche"].FilterClause = "[side].[proprietaire] in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo))";
                    serverProvisionTemplate.Tables["Mandats_Recherche"].FilterParameters.Add(param);*/
                    //Mandats transactions
                    serverProvisionTemplate.Tables["Mandats_Transaction"].AddFilterColumn("bien");
                    serverProvisionTemplate.Tables["Mandats_Transaction"].FilterClause = "[side].[bien] in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo)))";
                    serverProvisionTemplate.Tables["Mandats_Transaction"].FilterParameters.Add(param);
                    //photos
                    serverProvisionTemplate.Tables["Photos"].AddFilterColumn("lot");
                    serverProvisionTemplate.Tables["Photos"].FilterClause = "[side].[lot] in (select id from Lots where bien in (select id from biens where proprietaire in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo))))";
                    serverProvisionTemplate.Tables["Photos"].FilterParameters.Add(param);
                    //proprietaires
                    serverProvisionTemplate.Tables["Proprietaires"].AddFilterColumn("agentCo");
                    serverProvisionTemplate.Tables["Proprietaires"].FilterClause = "[side].[agentCo] in (select id from AgentCos where username = @agentCo)";
                    serverProvisionTemplate.Tables["Proprietaires"].FilterParameters.Add(param);
                    //recherches
                    serverProvisionTemplate.Tables["Recherches"].AddFilterColumn("client");
                    serverProvisionTemplate.Tables["Recherches"].FilterClause = "[side].[client] in (select id from Proprietaires where agentCo in (select id from AgentCos where username = @agentCo))";
                    serverProvisionTemplate.Tables["Recherches"].FilterParameters.Add(param);
                    if (!serverProvisionTemplate.TemplateExists(filterTemplate))
                    {
                        serverProvisionTemplate.Apply();
                    }
                    #endregion
                    #region provisionning
                    string agentCo = "francois.mouchati";
                    string branchFilteredScope = string.Format("{0}_Scope", agentCo);
                    SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
                    serverProvision.PopulateFromTemplate(branchFilteredScope, filterTemplate);
                    serverProvision.Tables["Biens"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["BonsVisite"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Lots"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Mandats_Gestion"].FilterParameters["@agentCo"].Value = agentCo;
                    //serverProvision.Tables["Mandats_Location"].FilterParameters["@agentCo"].Value = agentCo;
                    //serverProvision.Tables["Mandats_Recherche"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Mandats_Transaction"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Photos"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Proprietaires"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.Tables["Recherches"].FilterParameters["@agentCo"].Value = agentCo;
                    serverProvision.UserComment = string.Format("Only for agent {0}.", agentCo);
                    if (!serverProvision.ScopeExists(branchFilteredScope))
                    {
                        serverProvision.Apply();
                    }
                    #endregion
                    #region provisionning client
                    DbSyncScopeDescription serverScopeDescForBranch = SqlSyncDescriptionBuilder.GetDescriptionForScope(branchFilteredScope, null, serverConn);
                    SqlSyncScopeProvisioning branchProvision = new SqlSyncScopeProvisioning(clientConn, serverScopeDescForBranch);
                    if (!branchProvision.ScopeExists(branchFilteredScope))
                    {
                        branchProvision.Apply();
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    serverConn.Close();
                    serverConn.Dispose();
                    clientConn.Close();
                    clientConn.Dispose();
                }
            }

    Wednesday, August 07, 2013 1:23 PM