Querying an external list.

richv · February 1st, 2013, 06:34 PM

Hey all...
I'm attempting to grab the BDCIdentity value from my PartLookup field (lookup to an external list) and retrieve the SPListItem that corresponds to this site column's value on the current SPListItem in the child list. You figure that would be fairly simple. It is turning out to be quite painful.
I tried to use a bunch of different approaches and none seem to work.

So, the goal actually is in an ItemAdding event receiver, for the current affected SPListItem, grab the BDCIdentity value (ie '__bg#######) and locate the corresponding SPListItem in the external list. I actually need to retrieve another field value for that list.

Anyway, here is the final version of my code and I'm in a simple console app. I'm logged into the SharePoint Server as the farm account when I'm executing the code.

Code:

        static void Main(string[] args)
        {
            using (SPSite site = new SPSite(_sSiteURL))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    Console.WriteLine("Site: " + site.Url);

                    SPList partList = null;

                    foreach (SPList lList in web.Lists)
                    {
                        if (lList.RootFolder.Name == "PartList")
                        {
                            partList = lList;
                            break;
                        }
                    }
                    
                    SPQuery query = new SPQuery();
                    query.Method = "<Method Name='GetAllPartEntitys'><Filter Name='Description' Value='{0}'/></Method>";
                    query.ViewFields = "<FieldRef Name='BdcIdentity'/><FieldRef Name='sName'/>";
                    query.Query = "<Where><Contains><FieldRef Name='sName'/><Value Type='Text'>HP</Value></Contains></Where><OrderBy><FieldRef Name='BdcIdentity'/></OrderBy>";
                    SPListItemCollection foundItems = partList.GetItems(query);
                    Console.WriteLine(String.Format("Count of FoundItems: {0}", foundItems.Count));

                }

            }

        }

When the code gets to the final Console.WriteLine, it throws the following exception:

Code:

System.UnauthorizedAccessException was unhandled
  Message=Attempted to perform an unauthorized operation.
  Source=Microsoft.SharePoint
  StackTrace:
       at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
       at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
       at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
       at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
       at Microsoft.SharePoint.SPListItemCollection.get_Count()
       at SetExternalLookupField.Program.Main(String[] args) in C:\source\Blackcreek\SharePointUtils\SetExternalLookupField\SetExternalLookupField\Program.cs:line 66
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

I can browse the rows in external list without a problem. This same code will also not run under the event receiver.

Please note that I also attempted to use the caml syntax from Page 143 in the book. Same result.

Also, the final iteration of the approach I attempted is from this site.http://sharepoint.stackexchange.com/...-finder-method