External List Missing New and Edit Forms

I was recently walking a customer through creating an External Content Type [ECT] that uses stored procedures instead of just pointing directly to a table. When we created an External List using the External Content Type, the list didn’t have the option to create new items, nor could we edit items. SharePoint Designer displayed the following dialog when the external list was being created:

Looking at the list in SharePoint Designer would show the following in the forms section:

The external content type has all the types required for the external list to support creating and editing items, so we were initially at a loss for why the warnings and the resulting list missing the forms:

At this point, if you are using the default Diagnostic Logging settings, you’ll see the following entries to line up with the error dialog displayed in SharePoint Designer. 

10/30/2011 20:03:16.17     w3wp.exe (0x1434)                           0x189C    SharePoint Foundation             General                           fvo2    Medium     
VLPROV: Data source does not support Create, no NewForm.aspx page is being provisioned for list with ID 'e5d7abe6-84f4-4a2d-b2bc-bff5c4b9f00c', URL '/Lists/MYDB People List'.    72e37591-e309-452e-9feb-19ec93841cfc

10/30/2011 20:03:16.17     w3wp.exe (0x1434)                           0x189C    SharePoint Foundation             General                           fvo3    Medium     
VLPROV: Data source does not support Update, no EditForm.aspx page is being provisioned for list with ID 'e5d7abe6-84f4-4a2d-b2bc-bff5c4b9f00c', URL '/Lists/MYDB People List'.    72e37591-e309-452e-9feb-19ec93841cfc

Once you enable Verbose logging for the SharePoint Foundation - General category, you’ll get more details that start to point to the root cause for the behavior :

10/30/2011 20:05:14.19     w3wp.exe (0x1434)                           0x0230    SharePoint Foundation             General                           f9ca    Verbose    
Updater 'PeopleEdit' skipped because it could not be determined to be in the ViewGroup induced by SpecificFinder 'PeopleGetOne'.    276e6ba8-e00d-4ef9-b1f9-023321f3832a

10/30/2011 20:05:14.19     w3wp.exe (0x1434)                           0x0230    SharePoint Foundation             General                           f9c8    Verbose    
Creator 'PeopleCreate' skipped because it could not be determined to be in the ViewGroup induced by SpecificFinder 'PeopleGetOne'.    276e6ba8-e00d-4ef9-b1f9-023321f3832a

Mapping up the BCS specific references to SharePoint Designer, the SpecificFinder is your Read Item method on your External Content Type. The ViewGroup is the list of fields returned by the SpecificFinder. When SharePoint Designer is building out the external list, it is using the input and output parameters for the stored procedures to line up the columns. If the columns returned from the Read Item command do not match the input parameters for the Create and Update commands, the forms are not generated.  For example, given the following table named People:

The Read Item stored procedure named PeopleGetOne may look like:

The PeopleEdit stored procedure may look like the following:

Notice that PeopleGetOne returns a column named ID, but the PeopleEdit stored procedure takes in a parameter named @PersonID. This is when you hit this problem. In order to have SharePoint Designer create a New Form, you would need to change the PeopleEdit’s parameter to be @ID instead of @PersonID.