none
Filtering Project Server Lookup Table RRS feed

  • Question

  • Hello!

    I am trying to filter a specific Lookup Table value from a LookupTableDataSet via the following code.

    LookupTableDataSet lookupDs = new LookupTableDataSet();

    //Get all the lookup table
    Filter cfFilter = new Microsoft.Office.Project.Server.Library.Filter();
    // Restrict the filter to one table.
    cfFilter.FilterTableName = lookupDs.LookupTableTrees.TableName;
    // Add fields to the filter to limit the columns you want returned.
    // Set the sort order on the name column (optional for the Field constructor).
    cfFilter.Fields.Add(new Filter.Field(lookupDs.LookupTableTrees.TableName,
    lookupDs.LookupTableTrees.LT_VALUE_FULLColumn.ColumnName));

    Filter.FieldOperator criteria =
    new Microsoft.Office.Project.Server.Library.Filter.FieldOperator
    (Filter.FieldOperationType.Equal, "LT_VALUE_FULL",
    new string[] { 'Consulting.Analyst' });

    cfFilter.Criteria = criteria;
    lookupDs = lookupTable.ReadLookupTables(cfFilter.GetXml(), false, 0);



    While executing, I am getting the following error message:


    declare @P0 NVarChar; set @P0 = Consulting.Analyst;

    SET NOCOUNT ON

    SELECT
       LT_STRUCT_UID ,
       LT_UID ,
       LCID
    INTO #T0
    FROM dbo.LookupTableTrees AS keys
    WHERE (ISNULL(keys.LT_VALUE_FULL, '') = @P0)
    CREATE CLUSTERED INDEX PK_#T0 ON #T0 (LT_STRUCT_UID)

    SET NOCOUNT OFF

    SELECT
       keys.LT_STRUCT_UID ,
       keys.LT_UID ,
       LT_PARENT_STRUCT_UID ,
       LT_VALUE_FULL
    FROM dbo.LookupTableTrees
       INNER JOIN #T0 AS keys ON keys.LT_STRUCT_UID = LookupTableTrees.LT_STRUCT_UID


    DROP TABLE #T0;

     ---> System.Data.ConstraintException: Failed to enable constraints. One or more
     rows contain values violating non-null, unique, or foreign-key constraints.
       at System.Data.DataSet.EnableConstraints()
       at System.Data.DataSet.set_EnforceConstraints(Boolean value)
       at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSe
    t(Boolean allowCache, DataSet typedDataSet, String[] tables, SqlCommand sqlComma
    nd, Boolean enforceConstraints)
       at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSe
    t(DataSet typedDataSet, String[] tables, SqlCommand sqlCommand, Boolean enforceC
    onstraints)
       at Microsoft.Office.Project.Server.DataAccessLayer.FilterDal.FillDataSet(Quer
    yState queryState)
       --- End of inner exception stack trace ---
       at Microsoft.Office.Project.Server.DataAccessLayer.FilterDal.FillDataSet(Quer
    yState queryState)
       at Microsoft.Office.Project.Server.BusinessLayer.LookupTable.ReadLookupTables
    (String xmlFilter, Boolean autoCheckOut, Int32 language)
       at Microsoft.Office.Project.Server.Wcf.Implementation.LookupTableImpl.ReadLoo
    kupTables(String xmlFilter, Boolean autoCheckOut, Int32 language)
    ============================

    When I run the SQL query, I don't get any results. By changing the @P0 variable to NVARCHAR(20), the query is running fine and shows the correct result?

    What could be wrong with the PSI code? How could I filter to a specific LT_Value_Full value?



    Many thanks



    Joerg


    Wednesday, February 27, 2013 10:26 AM