Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

How To: So you have a GUID in your External List huh, Yes you can Update the List… if you set it up right


Synopsis:  I fielded a question on MSDN again and it peaked my interest because it had to do with of course BCS. The stated question was an issue when trying to update an External List when a GUID was present.  So I setup a new table, created a… well you will see it below.

image

 

Part 1: Set the Environment and Duplicate the Issue

clip_image001

Above: So I created the Table above and as you can see I used a GUID with (newid()) property set

clip_image002

Above: Added two people to the list and as you can see the GUID is present.

Part 2: Test it out

clip_image003

Above: Created an External List from the ECT, External List is called “GUID in People List” and what I did was also a “Create All Operation” however you will notice that you DO NOT SEE that the GUID Field is not present in the List View

clip_image004

Above:  I created a New Item and populated it with values. Notice here again that the GUID is not present and my Required or “NOT NULL” values from SQL is also denoted

clip_image005

Above: The New User is persisted

clip_image006

Above:  The External Content Type with CRUD capabilities

Part 3: Explaining why my example presumably worked “once” as  yours probably did too and not afterwards

clip_image007

Above: As you would expect the IDGUID field as a primary key / Identity field is protected.  So, it is set to Read Only in the Return Parameter Configuration Wizard.

clip_image008

Above: Just to show you, the second field i am putting in the Picker is NOT read only but it is a Required Field in the LOB

Part 4:  Why Subsequent try’s FAIL and how to make it work… yes Visual Studio

 

clip_image006[5]

Above:  So this is the entry that is made from the last example. BUT because the GUID is set to all Zeros as the question indicated in the Forum and it HAS TO BE a primary key, any subsequent addition in SharePoint will fail because of the duplicate entry.

clip_image001[5]

Above:  Here is our Test Case, let us add a new user called “User 95”

clip_image002[5]

Above:  And of course we error out because of the Primary Key Constraint in the LOB system

clip_image003[5]

Above:  So just to take this all the way to the end, lets do something that we SHOULD NOT DO. We will remove the Read-Only property of the Primary Key in SharePoint; in doing so you will find out exactly WHY it fails and how to fix it in the end. Notice it says that it needs the “PreUpdaterField” this field is in Visual Studio NOT SharePoint Designer.  So this is yet another answer that I give to people that constantly ask me “Hey Fabian, why do i need to use Visual Studio”

clip_image004[5]

Above: We try to put another record there..

clip_image005[5]

Above: Yes Same Error because the LOB system WILL NOT permit it to do so.

Part 5: How to Make it Work

See my BLOG on how to do CRUD ECT and External Lists using Visual Studio. See https://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

I will try to do one specific for this example over the weekend time permitting.

Advertisements

April 17, 2010 - Posted by | Business Connectivity Services, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Error/Resolution, SharePoint How-To, SQL Server, Visual Studio 2010

3 Comments »

  1. Hi Fabian,

    This is great – probably one of the topmost blogs on SP2010.

    Can I have guidance about GUID field problem?
    ” … Part 5: How to Make it Work
    I will try to do one specific for this example over the weekend time permitting. ….”

    Thanks a million !

    Comment by EMT | October 17, 2010 | Reply

  2. I solved by adding one line of code in Creator method,
    just before .InsertOnSubmit

    Company.CompanyID = Guid.NewGuid();

    It works fine, but don’t know if this is the “correct way”

    Comment by EMT | October 17, 2010 | Reply

    • Dude, way cool. It can prove to be that simple. but my only word of caution is that if you are doing this to write back to the LOB system, and the LOB system is responsible for auto-incrementing that column and that column is a primary KEY you have just voilated a RDMS constraint.

      Comment by fabiangwilliams | October 17, 2010 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: