its showing weird binary data from the BDC data column in list

Question

Hello Gentlemen,

Here I am experimenting the BDC with a simple example of pulling distinct data from a sql server 2005 to a sharepoint list column "SELECT distinct Color FROM Product" .


Blue     
Green    
Murali
red      
White    
Yellow

when i configured the bdc using Business Data List the data showing correct. But when I use this it in a list column with bdc data type its returnig me the binary type of data.
 
__bk82007400270056005600e60002000200020002000200
No exact match was found.

However in the bdc picker the data is displaying properly. The data type in sql is nvarchar. I tried changing different data types but its showing the same kind of it.

Here is the BDC:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<LobSystem xmlns: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.0" Name="ProductBDC3" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<LobSystemInstances>

<LobSystemInstance Name="ProductBDC3" >

<Properties>

<Property Name="rdbconnection Data Source" Type="System.String">SEIMOSSDEV\OFFICESERVERS</Property>

<Property Name="rdbconnection Initial Catalog" Type="System.String">Test</Property>

<Property Name="rdbconnection Integrated Security" Type="System.String">SSPI</Property>

<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>

<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RevertToSelf</Property>

<Property Name="RdbConnection Pooling" Type="System.String">false</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

<Entities>

<Entity EstimatedInstanceCount="100" Name="Color">

<Properties>

<Property Name="Name" Type="System.String">Color</Property>

</Properties>

<Identifiers>

<Identifier Name="Color" TypeName="System.String" />

</Identifiers>

<Methods>

<Method Name="GetProducts">

<Properties>

<Property Name="RdbCommandText" Type="System.String">

SELECT distinct Color FROM Product

</Property>

<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

</Properties>

<Parameters>

<Parameter Direction="Return" Name="Color">

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

IsCollection="true" Name="ProductDataReader">

<TypeDescriptors>

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

Name="ProductDataRecord">

<TypeDescriptors>

<TypeDescriptor TypeName="System.String" IdentifierName="Color" Name="Color">

<LocalizedDisplayNames>

<LocalizedDisplayName LCID="1033">ProductID</LocalizedDisplayName>

</LocalizedDisplayNames>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Name="ProductFinderInstance" Type="Finder" ReturnParameterName="Color" ReturnTypeDescriptorName="ProductDataReader" />

<MethodInstance Name="ColorFinderInstance" Type="SpecificFinder" ReturnParameterName="Color" ReturnTypeDescriptorName="ProductDataReader" />

</MethodInstances>

</Method>

</Methods>

</Entity>

</Entities>

</LobSystem>

appreciate your help.

Thanks

Answer 1

Off-Topic:

Thanks to "jardelu" for making me aware of the book-pusher who was in this thread and another one.

Answer 2

The business data column makes use of the specific finder method. This method should only return a single row of data that is uniquely identified by the BDC identifier you have declared for your entity.

Unfortunately it is your SpecifcFinder method instance that is not working properly, and so when you select a row of data in the picker - the business data column can not actually select which one to use.


As a general rule we've also found it makes things much easier if you define your Finder and SpecificFinder methods separately. I know the example in the MOSS sdk does not do it that way, but separating them makes it much easier to debug and fix problems.

Hope this helps
Nick

---

http://www.bdcmetaman.com

Answer 3

Hey Nick,
Can you offer any more advise based on the Specific Finder Method Instance I have below?  I am experiencing the same issue..

I have also added the code from the Specific Finder stored proc that this is calling

<Method Name="GetSpecBizInfo">
- <Properties>
  <Property Name="RdbCommandText" Type="System.String">proc_SP_GetDevelopersInfoBySpecDBA</Property> 
  <Property Name="RdbCommandType" Type="System.String">StoredProcedure</Property> 
  </Properties>
- <Parameters>
- <Parameter Direction="In" Name="@dbaName">
- <TypeDescriptor TypeName="System.String" Name="dbaName" IdentifierName="[BusinessID]">
- <DefaultValues>
  <DefaultValue MethodInstanceName="GetSpecBizInfoSpecificFinder" Type="System.String">%</DefaultValue> 
  </DefaultValues>
  </TypeDescriptor>
  </Parameter>
- <Parameter Direction="Return" Name="GetSpecBizInfo">
- <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="GetSpecBizInfoDataReader">
- <TypeDescriptors>
- <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="GetSpecBizInfoDataRecord">
- <TypeDescriptors>
  <TypeDescriptor TypeName="System.Int32" Name="BusinessID" IdentifierName="[BusinessID]" /> 
  <TypeDescriptor TypeName="System.String" Name="dbaName" /> <br/><br/><br/><br/>

ALTER PROCEDURE [dbo].[proc_SP_GetDevelopersInfoBySpecDBA]

	-- Add the parameters for the stored procedure here
@dbaName varchar(100)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT 
	*
FROM 
	View_Dealers VD
WHERE
	IsPOSDeveloper = 1
	AND 
	[Status] <= 3
	AND
	dbaName = @dbaName
END

Answer 4

Have the same problem.
I see the different records in the picker, but as soon as selecting them, the result is just binary data.

maybe someone, can provide us with a little bit more detailled howto fix this issue,

thx a lot

Answer 5

Being a client of Lightning Tools, I decided to email Nick directly to see if he could offer any follow-up on this, based on the code I posted above.

This was his response:

"Hi Fred,

 

is the parameter @dbaName enough to return a unique row of data?

 

The specific finder method needs to return only a single row of data, if you pass in a value to dbaName and it returns more than 1 record you will get the error you see.

 

Hope this helps

Nick"

This was indeed the cause of my issue. Special thanks to Nick for helping me figure this out. 

For anyone who may be considering a full license to BDC Meta Man I  would like to add that Lightning Tools has proven to be very helpful and quite prompt with their support.

Answer 6

I run the query in sql mgmt studio against my db and it showed me for a particular value of my paremter (forename+lastname) only 1 record.

So depending on the value I type in in the query string of my business data column there are 2 possibilities:

1. It only gets 1 record back. Then the specific finder should work (it doesnt: "no exact amtch found)

2. If i type in a value, with more than 1 record the error message "no exact message found" is correct, but then the user should have the possibility to choose the right one by using the picker.

I have 2 filterdescripor within my method, in which im using the specific finder:

1. One is to filter the data within the picker and by just using the query string (using the ID)

2. one ist to filter the data using the query string (using forename +lastname)

If i implement just the ID Filter the Specific Finder works: But I am only able to search over the exact ID when I use the query string.

If I implement both, I always get the mesasge no exact match found:

This is my code for the method i used the SpecificFinder

<Method Name="GetCustomerInColumn">

            <Properties>

              <Property Name="RdbCommandText" Type="System.String">

               SELECT  Customer_ID,member_name, member_forename, convert(varchar(10),Customer_Birthday,104) as Customer_Birthday, (Customer_ID+' '+member_name+' '+member_forename+' '+convert(varchar(10),Customer_Birthday,104)) AS AllValues    

               FROM Customer INNER JOIN   member ON member.member_oid = Customer.Customer_OID

    	      WHERE (Customer_ID=@TN OR @TN IS NULL) AND ((member_name+' 'member_forename)=@Name OR (member_forename+' 'member_name)=@">Customer_ID=@TN OR @TN IS NULL) AND ((member_name+' 'member_forename)=@Name OR (member_forename+' 'member_name)=@Name) 
           	</Property>              	
		<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

            </Properties>

            <FilterDescriptors>

              <FilterDescriptor Type="Comparison" Name="IDFilter" FilterDescriptor Type="Comparison" Name="NameFilter" />

            </FilterDescriptors>

           <Parameters>

      	   <Parameter Direction="In" Name="@TN">

           <TypeDescriptor TypeName="System.String" IdentifierName="Custimer_ID" AssociatedFilter="IDFilter" Name="IDFilter">

            </TypeDescriptor>

          </Parameter>
	<Parameter Direction="In" Name="@TN">

           <TypeDescriptor TypeName="System.String"  AssociatedFilter="NameFilter" Name="NameFilter">

           </TypeDescriptor>

          </Parameter>

          <Parameter Direction="Return" Name="members">

                <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="TNDataReader" IsCollection="true">

                 <TypeDescriptors>

                    <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="TNDataRecord">

                      <TypeDescriptors>

                        <TypeDescriptor TypeName="System.String" Name="member_name">

                         <LocalizedDisplayNames>

                         <LocalizedDisplayName LCID="1031">

                           Lastname  
			</LocalizedDisplayName>

                         </LocalizedDisplayNames>

                         </TypeDescriptor>

                         <TypeDescriptor TypeName="System.String" IdentifierName="Customer_ID" Name="Customer_ID">

                         <LocalizedDisplayNames>

                          <LocalizedDisplayName LCID="1031">

                           ID        
	       		</LocalizedDisplayName>

                         </LocalizedDisplayNames>

                         </TypeDescriptor>

                         <TypeDescriptor TypeName="System.String" Name="Customer_Birthday">

                         <LocalizedDisplayNames>

                          <LocalizedDisplayName LCID="1031">

                           Birthday  
			</LocalizedDisplayName>

                         </LocalizedDisplayNames>

                         </TypeDescriptor>

                         <TypeDescriptor TypeName="System.String" Name="member_forename">

                         <LocalizedDisplayNames>

                           <LocalizedDisplayName LCID="1031">

                           Forename      
			 </LocalizedDisplayName>

                         </LocalizedDisplayNames>

                         </TypeDescriptor>

                         <TypeDescriptor TypeName="System.String" Name="AllValues" >

                          <LocalizedDisplayNames>

                          <LocalizedDisplayName LCID="1031">

                            All Values
			</LocalizedDisplayName>

                        </LocalizedDisplayNames>

                        <Properties>

                        <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>

                         <Property Name="ShowInPicker" Type="System.Boolean">true</Property>

                        </Properties>

                      </TypeDescriptor>

                      </TypeDescriptors>

                       </TypeDescriptor> 

                      </TypeDescriptors>

                      </TypeDescriptor>             

                 </Parameter>

               </Parameters>

            <MethodInstances>

              <MethodInstance Name="TNSpecificFinder" Type="SpecificFinder" ReturnParameterName="members"/>
  	</MethodInstances>

          </Method>

 

Answer 7

I'm having a similar issue using just the finder method using BDC Metaman.  I have a simple SQL DB that contains vendor contact information.  I'm using Metaman to create the app def.  In SharePoint, I need to add this contact information into a SP list.  I can get everything working correctly up to the point where I choose the vendor in the Picker and hit ok.  Once I hit OK, it says exact match not found and I get a big binary number.  I'm creating the filter on the Vendor Name.  I have about 5000 vendors and the picker will only show up to 200 items so I need a filter for people to be able to search and find the appropriate vendor.

Thank you

Answer 8

Hi erickers,

In order to use the BDC Picker you need to specify a Specific Finder Method. The BDC works off 3 main methods

1. Finder - "Select PersonId, FirstName, LastName, Age, Occupation From tbl_People"
   
   This will return all of your data, this is used for the BDC Data List - it is just that a list of all of the data
   
2. Specific Finder - "Select PersonId, FirstName, LastName, Age, Occupation From tbl_People Where (PersonId = @PersonId)"
   
   This will return just one row of data, this is used for the Profile Page, BDC Picker and BDC Item - it brings back 1 row
3. IdEnumerator - "Select PersonId from tbl_People"
   
   This is used by search

If you do not specify a specific finder there is no way that the picker can be used as it does not know the SQL query to use to bring back the one row of information you require, there will be no identifiers set up to be used

Hope this helps

Regards,

Phill

---

BDC Meta Man Web Edition - Out Now

Answer 9

Thank you very much. 

I apologize for the ignorance but I'm rather new at this. 

Now when I create the Specific Finder, it just bring up ALL records and it still populates the results in binary code.  Do I have to modify the specific finder in any way in Metaman?  Also, do I need to add a filter to the specific finder?  Do I have to remove the Finder Method from the list of the three methods?

Thank you very much!!!