Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SharePoint Products and Technologies >> SharePoint - Business Data Catalog

Associations

Hi, I'm using the AdventureWorks BDC example to try to assiociate two entities. The example works, but when I do my modifications, I get the error "Could not find fields to insert all the Identifier Values to correctly execute an Association method." It's possible I've stared myself blind on this xml, but I am not able to find where the missing tags should go.

This is my bdc app def:

<?xmlversion="1.0"encoding="utf-8"standalone="yes" ?>
<LobSystemxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd"
Type="Database"
Version="1.0.0.8"Name="MyLob"
xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Properties>
<PropertyName="WildcardCharacter"Type="System.String">%</Property>
</Properties>
<LobSystemInstances>
<LobSystemInstanceName="MyLobInstance">
<Properties>
<PropertyName="AuthenticationMode"Type="System.String">PassThrough</Property>
<PropertyName="DatabaseAccessProvider"Type="System.String">SqlServer</Property>
<PropertyName="RdbConnection Data Source"Type="System.String">MyServer</Property>
<PropertyName="RdbConnection Initial Catalog"Type="System.String">MyDatabase</Property>
<PropertyName="RdbConnection Integrated Security"Type="System.String">SSPI</Property>
<PropertyName="RdbConnection Pooling"Type="System.String">false</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<EntityEstimatedInstanceCount="10000"Name="CreditLimit">
<Identifiers>
<IdentifierName="PolicyID"TypeName="System.Int32" />
<IdentifierName="ActorID"TypeName="System.Int32" />
</Identifiers>
<Methods>
<MethodName="GetCreditLimits">
<Properties>
<PropertyName="RdbCommandText"Type="System.String">
select PolicyID, ActorID, BuyerID, BuyerName from dbo.v_CreditLimits where (PolicyID = @PolicyID)
</Property>
<PropertyName="RdbCommandType"Type="System.String">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptorType="Comparison"Name="ID" >
<Properties>
<PropertyName="Comparator"Type="System.String">Equals</Property>
</Properties>
</FilterDescriptor>
<FilterDescriptorType="Wildcard"Name="BuyerName" />
</FilterDescriptors>
<Parameters>
<ParameterDirection="In"Name="@PolicyID">
<TypeDescriptorTypeName="System.Int32"IdentifierName="PolicyID"AssociatedFilter="ID"Name="PolicyID">
<DefaultValues>
<DefaultValueMethodInstanceName="CreditLimitFinderInstance"Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<ParameterDirection="Return"Name="CreditLimits">
<TypeDescriptorTypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"IsCollection="true"Name="CreditLimitsDataReader">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, ublicKeyToken=b77a5c561934e089"Name="CreditLimitsDataRecord">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Int32"IdentifierName="PolicyID"Name="PolicyID"/>
<TypeDescriptorTypeName="System.Int32"Name="ActorID"/>
<TypeDescriptorTypeName="System.Int32"Name="BuyerID"/>
<TypeDescriptorTypeName="System.String"Name="BuyerName"/>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstanceName="CreditLimitFinderInstance"Type="Finder"ReturnParameterName="CreditLimits" />
<MethodInstanceName="CreditLimitSpecificFinderInstance"Type="SpecificFinder"ReturnParameterName="CreditLimits" />
</MethodInstances>
</Method>
</Methods>
</Entity>
<EntityEstimatedInstanceCount="10000"Name="Policy">
<Properties>
<PropertyName="Title"Type="System.String">PolicyID</Property>
</Properties>
<Identifiers>
<IdentifierName="PolicyID"TypeName="System.Int32" />
<IdentifierName="ActorID"TypeName="System.Int32" />
</Identifiers>
<Methods>
<MethodName="GetPolicies">
<Properties>
<PropertyName="RdbCommandText"Type="System.String">Select ActorID, PolicyID from v_Policies where ActorID = @ActorID</Property>
<PropertyName="RdbCommandType"Type="System.String">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptorType="Comparison"Name="ID" >
<Properties>
<PropertyName="Comparator"Type="System.String">Equals</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<ParameterDirection="In"Name="@ActorID">
<TypeDescriptorTypeName="System.Int32"IdentifierName="ActorID"AssociatedFilter="ID"Name="ActorID">
<DefaultValues>
<DefaultValueMethodInstanceName="PolicyFinderInstance"Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<ParameterDirection="Return"Name="Policies">
<TypeDescriptorTypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"IsCollection="true"Name="PolicyDataReader">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="PolicyDataRecord">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Int32"IdentifierName="ActorID"Name="ActorID" />
<TypeDescriptorTypeName="System.Int32"IdentifierName="PolicyID"Name="PolicyID" />
<TypeDescriptorName="Test"TypeName="System.Int32"/>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstanceName="PolicyFinderInstance"Type="Finder"ReturnParameterName="Policies" />
<MethodInstanceName="policySpecificFinderInstance"Type="SpecificFinder"ReturnParameterName="Policies" />
</MethodInstances>
</Method>
<MethodName="GetCreditLimitsForPolicy">
<Properties>
<PropertyName="RdbCommandText"Type="System.String">
select PolicyID, ActorID, BuyerID, BuyerName from dbo.v_CreditLimits where PolicyID = @PolicyID
</Property>
<PropertyName="RdbCommandType"Type="System.String">Text</Property>
</Properties>
<Parameters>
<ParameterDirection="In"Name="@PolicyID">
<TypeDescriptorTypeName="System.Int32"IdentifierEntityName="CreditLimit"IdentifierName="PolicyID"Name="PolicyID" />
</Parameter>
<ParameterDirection="Return"Name="CreditLimits">
<TypeDescriptorTypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"IsCollection="true"Name="CreditLimitDataReader">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="CreditLimitDataRecord">
<TypeDescriptors>
<TypeDescriptorTypeName="System.Int32"IdentifierEntityName="CreditLimit"IdentifierName="PolicyID"Name="PolicyID"/>
<TypeDescriptorTypeName="System.Int32"IdentifierEntityName="CreditLimit"IdentifierName="ActorID"Name="ActorID"/>
<TypeDescriptorTypeName="System.Int32"Name="BuyerID" />
<TypeDescriptorTypeName="System.String"Name="BuyerName"/>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
</Method>
</Methods>
</Entity>
</Entities>
<Associations>
<AssociationAssociationMethodEntityName="Policy"AssociationMethodName="GetCreditLimitsForPolicy"AssociationMethodReturnParameterName="CreditLimits"Name="PolicyToCreditLimit"IsCached="true">
<SourceEntityName="Policy" />
<DestinationEntityName="CreditLimit" />
</Association>
</Associations>
</LobSystem>

What am I missing?

[52656 byte] By [HansErikLange] at [2008-1-27]
«« build skipped because Configuration isn't evaluated right
»» How do I display a filtered view of a list in a sub-site...desparate...
# 1

These tables aren't from AdventureWorks though are they? I've tried looking just now...

If you could let us know the structure of your DB we might be able to figure it out.

Thanks

Nick

NickSwan at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 2

Not from AdvetureWorks, but I took the (working) AdvWorks example and modified it to fit my database/views.

v_Policies contains just ActorID (int) and PolicyID (int). The ActorID will be provided by the Current User filter. v_CreditLimits contains ActorID (int), PolicyID (int), BuyerID (int), BuyerName (varchar(128)). When the user select a Policy, it's CreditLimits should be displayed.

Here's some quick test data:

create table v_Policies (
ActorID int,
PolicyID int
)
go

insert into v_Policies
select 1, 1
union select 1, 2
union select 1, 3
go

create table v_CreditLimits (
ActorID int,
PolicyID int,
BuyerID int,
BuyerName varchar(128)
)
go

insert into v_CreditLimits
select 1, 1, 10, 'Company 1'
union select 1, 1, 10, 'Company 1'
union select 1, 1, 11, 'Company 2'
union select 1, 1, 12, 'Company 3'
union select 1, 1, 13, 'Company 4'
union select 1, 1, 14, 'Company 5'
union select 1, 1, 15, 'Company 6'
union select 1, 1, 16, 'Company 7'
union select 1, 2, 17, 'Company 8'
union select 1, 2, 18, 'Company 9'
union select 1, 2, 19, 'Company 10'
union select 1, 2, 20, 'Company 11'
union select 1, 3, 21, 'Company 12'
union select 1, 3, 22, 'Company 13'
union select 1, 3, 23, 'Company 14'
union select 1, 3, 24, 'Company 15'
union select 1, 3, 25, 'Company 16'
union select 1, 3, 26, 'Company 17'
go

HansErikLange at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 3

Hans,

From a quick glance I suggest you look at the following

Here you have defined two identifiers

<Identifiers>
<Identifier Name="PolicyID" TypeName="System.Int32" />
<Identifier Name="ActorID" TypeName="System.Int32" />
</Identifiers>

Here you only defined one field as an Indentifier

<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" Name="PolicyID"/>
<TypeDescriptor TypeName="System.Int32" Name="ActorID"/>

Replace the line above with this and it should work

<TypeDescriptor TypeName="System.Int32" IdentifierName="ActorID" Name="ActorID"/>


You have also only defined one parameter in your sql which I would expect 2 as you have defined this entity as requiring 2 values to identify it.

<Parameters>
<Parameter Direction="In" Name="@PolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" AssociatedFilter="ID" Name="PolicyID">
<DefaultValues>

<Property Name="RdbCommandText" Type="System.String">
select PolicyID, ActorID, BuyerID, BuyerName from dbo.v_CreditLimits where PolicyID = @PolicyID
</Property>

HTH,

Andrew

andrewwoodward at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 4

Thank you for your reply! It has helped me to better understand the BDC.

I can make it work if I have single column keys in the database and only one identifier per entity. Unfortunately my data originates from a view, and I have a two column key.

My appdef now looks like this:

<Entities>
<Entity EstimatedInstanceCount="10000" Name="CreditLimit">
<Identifiers>
<Identifier TypeName="System.Int32" Name="PolicyID" />
<Identifier TypeName="System.Int32" Name="BuyerID" />
</Identifiers>
<Methods>
<Method Name="GetCreditLimits">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
select PolicyID, ActorID, BuyerID, BuyerName from dbo.v_CreditLimits
where PolicyID &gt;= @MinPolicyID and PolicyID &lt;= @MaxPolicyID
and BuyerID &gt;= @MinBuyerID and BuyerID &lt;= @MaxBuyerID
</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Comparison" Name="PolicyID"><Properties><Property Name="Comparator" Type="System.String">Equals</Property></Properties></FilterDescriptor>
<FilterDescriptor Type="Comparison" Name="BuyerID"><Properties><Property Name="Comparator" Type="System.String">Equals</Property></Properties></FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@MinPolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" AssociatedFilter="PolicyID" Name="PolicyID">
<DefaultValues>
<DefaultValue MethodInstanceName="CreditLimitFinderInstance" Type="System.Int32">0</DefaultValue>
<DefaultValue MethodInstanceName="CreditLimitSpecificFinderInstance" Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@MaxPolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" AssociatedFilter="PolicyID" Name="PolicyID">
<DefaultValues>
<DefaultValue MethodInstanceName="CreditLimitFinderInstance" Type="System.Int32">9999999</DefaultValue>
<DefaultValue MethodInstanceName="CreditLimitSpecificFinderInstance" Type="System.Int32">9999999</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@MinBuyerID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="BuyerID" AssociatedFilter="BuyerID" Name="BuyerID">
<DefaultValues>
<DefaultValue MethodInstanceName="CreditLimitFinderInstance" Type="System.Int32">0</DefaultValue>
<DefaultValue MethodInstanceName="CreditLimitSpecificFinderInstance" Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@MaxBuyerID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="BuyerID" AssociatedFilter="BuyerID" Name="BuyerID">
<DefaultValues>
<DefaultValue MethodInstanceName="CreditLimitFinderInstance" Type="System.Int32">9999999</DefaultValue>
<DefaultValue MethodInstanceName="CreditLimitSpecificFinderInstance" Type="System.Int32">9999999</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="CreditLimits">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="CreditLimitsDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CreditLimitsDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" Name="PolicyID" />
<TypeDescriptor TypeName="System.Int32" Name="ActorID" />
<TypeDescriptor TypeName="System.Int32" IdentifierName="BuyerID" Name="BuyerID" />
<TypeDescriptor TypeName="System.String" Name="BuyerName" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Finder" ReturnParameterName="CreditLimits" ReturnTypeDescriptorName="CreditLimitsDataReader" ReturnTypeDescriptorLevel="0" Name="CreditLimitFinderInstance" />
<MethodInstance Type="SpecificFinder" ReturnParameterName="CreditLimits" ReturnTypeDescriptorName="CreditLimitsDataReader" ReturnTypeDescriptorLevel="0" Name="CreditLimitSpecificFinderInstance"/>
</MethodInstances>
</Method>
</Methods>
</Entity>
<Entity EstimatedInstanceCount="10000" Name="Policy">
<Identifiers>
<Identifier TypeName="System.Int32" Name="ActorID" />
<Identifier TypeName="System.Int32" Name="PolicyID" />
</Identifiers>
<Methods>
<Method Name="GetPolicies">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
Select ActorID, PolicyID from v_Policies
where ActorID = @ActorID and PolicyID &gt;= @MinPolicyID and PolicyID &lt;= @MaxPolicyID
</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Comparison" Name="ProfileActorID"><Properties><Property Name="Comparator" Type="System.String">Equals</Property></Properties></FilterDescriptor>
<FilterDescriptor Type="Comparison" Name="MinMaxPolicyID"><Properties><Property Name="Comparator" Type="System.String">Equals</Property></Properties></FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@ActorID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="ActorID" AssociatedFilter="ProfileActorID" Name="ActorID">
<DefaultValues>
<DefaultValue MethodInstanceName="PolicyFinderInstance" Type="System.Int32">0</DefaultValue>
<DefaultValue MethodInstanceName="PolicySpecificFinderInstance" Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@MinPolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" AssociatedFilter="MinMaxPolicyID" Name="PolicyID">
<DefaultValues>
<DefaultValue MethodInstanceName="PolicyFinderInstance" Type="System.Int32">0</DefaultValue>
<DefaultValue MethodInstanceName="PolicySpecificFinderInstance" Type="System.Int32">0</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@MaxPolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" AssociatedFilter="MinMaxPolicyID" Name="PolicyID">
<DefaultValues>
<DefaultValue MethodInstanceName="PolicyFinderInstance" Type="System.Int32">9999999</DefaultValue>
<DefaultValue MethodInstanceName="PolicySpecificFinderInstance" Type="System.Int32">9999999</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Policies">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="PolicyDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="PolicyDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierName="ActorID" Name="ActorID" />
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" Name="PolicyID" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Finder" ReturnParameterName="Policies" ReturnTypeDescriptorName="PolicyDataReader" ReturnTypeDescriptorLevel="0" Name="PolicyFinderInstance" />
<MethodInstance Type="SpecificFinder" ReturnParameterName="Policies" ReturnTypeDescriptorName="PolicyDataReader" ReturnTypeDescriptorLevel="0" Name="PolicySpecificFinderInstance" />
</MethodInstances>
</Method>
<Method Name="GetCreditLimitsForPolicy">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
select PolicyID, ActorID, BuyerID, BuyerName
from dbo.v_CreditLimits
where Polisenummer = @PolicyID
</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
</Properties>
<Parameters>
<Parameter Direction="In" Name="@PolicyID">
<TypeDescriptor TypeName="System.Int32" IdentifierName="PolicyID" Name="PolicyID" />
</Parameter>
<Parameter Direction="Return" Name="CreditLimits">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="CreditLimitDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CreditLimitDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierEntityName="CreditLimit" IdentifierName="PolicyID" Name="PolicyID" />
<TypeDescriptor TypeName="System.Int32" Name="ActorID" />
<TypeDescriptor TypeName="System.Int32" IdentifierEntityName="CreditLimit" IdentifierName="BuyerID" Name="BuyerID" />
<TypeDescriptor TypeName="System.String" Name="BuyerName" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
</Method>
</Methods>
</Entity>
</Entities>
<Associations>
<Association Name="PolicyToCreditLimit" AssociationMethodEntityName="Policy" AssociationMethodName="GetCreditLimitsForPolicy" AssociationMethodReturnParameterName="CreditLimits" AssociationMethodReturnTypeDescriptorName="CreditLimitDataReader" IsCached="true">
<SourceEntity Name="Policy</