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.
Part 1: Set the Environment and Duplicate the Issue
Above: So I created the Table above and as you can see I used a GUID with (newid()) property set
Above: Added two people to the list and as you can see the GUID is present.
Part 2: Test it out
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
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
Above: The New User is persisted
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
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.
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
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.
Above: Here is our Test Case, let us add a new user called “User 95”
Above: And of course we error out because of the Primary Key Constraint in the LOB system
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”
Above: We try to put another record there..
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.