.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

External List & SPListItemCollection.GetDataTable & DateTime

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :SharePoint


Using Office SharePoint Server 2010 (Build 14.0.5128.5000). There External Content Type (defined methods Read Item and Read List) for submission to the SQL server with the following contents:

 dt = getdate
Further, the written code of the Web Part to read:


DataTable GetDataTable(SPList list, SPQuery query)
SPListItemCollection items = list.GetItems(query);
items.GetDataTable(); // (1)
At the point (1) code coincides with the exception of:


[FormatException: String was not recognized as a valid DateTime.]
   System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) +4588098
   System.String.System.IConvertible.ToDateTime(IFormatProvider provider) +56

View Complete Post

More Related Resource Links

External List Profile Pages

I have some external data sources that are related to each other. I created the associations in sharepoint designer, and they all work.  I have a page that has a list of people, that you can search, and when you search for them, it takes you to the profile page that shows the two related lists for that person. This works how I want it. What I want to do has four parts. First, on the initial page that displays my list, I want the primary field (case number) to link to the profile page for that person, instead of the display form. I have tried this with a custom action, but I don't think I did it correctly. Second, on the profile page itself, I have those two related tables that show up. One table is a questionnaire the person filled out, and one is information about interviews they were given.  In the interviews section, I would like the viewer to have an "add interview" button at the bottom, just like any list web part would have. I don't have the option in the web part properties. Finally, on the questionnaire section, I need to alter the view. Right now, there are headings across the top, with the information going left to right. I would like to set it up with a box style, like you can normally do in a list. I would also like to change the field labels from generic field names to more descriptive ones. In addition to that, the questionnaire is entere

External List - Access Selected Items

Hello, The situation;- An external list in SP 2010 from SQL A custom ribbon/button that calls via JS a Dialog box(Application page) and passes the selected items IDs Some code behind to process items after confirmation from user. Question is how to access the selected items in the code behind. As far as I can tell the JS 'getselecteditems' only returns the list ID, which in the case of a standard list is fine, but with an External list returns something like '_B00234' - how do I use this or how to return a more meaningfull ID? (I would rather work in my confort zone VB/C# than JS to process items) casey

Connecting a Sharepoint Custom list with an external Data-source

Hi I am planning to connect a Sharepoint Custom list with an external Data-source. External DB will be in Access 2007 format. More Information: I need a solution that we can connect our sharepoint list (one-way) to online db in a way that new  information is directly updated in the sharepoint list, whilst other info changed in sharepoint list  is not synced back to original value. Is there a way to solve this? -saumil

Changing the field type in an External List

I have created an External Content Type to a database containing document titles and the URLs to those documents.  I created the External content type operations for read item and read list with the title and url fields (there's a number identifier too). I next created an external list using my external content type. It's great - I can see the information I need from the external Database. BUT... The URL field appears as text and I'd like a clickable Hyperlink.  I'm not sure how I can do this.  Any ideas? Cheers,

Create drop-down filter for External List

I hope this is an easy question. I will have an external list of a bunch of people.  There are several ways I want to display this data: All of them, Only the ones that have a value of 1-4 in the status field, Only the ones that have any other value in the status field, and people that have a value of 1-4 in the status field, but are at a certain location. I want to be able to put a drop-down filter above the list where people can select that. Could someone please point me in the right direction? Time is of the essence.  

Need to link multiple External Content List items to a single document metadata column

I have an external content list - for my purposes, the data isn't any fancier than a list of terms provided by a web service (read only - I don't need update/delete functionality). I want to be able to link any subset of those terms to a single document metadata column (in other words, I need it to work like a normal content list that is defined to allow multiple values in a column) but SharePoint doesn't let me do that on an ECL. I need the content list to be externally driven since more values are going to be added to an outside system over time, but I think it's possible that I may not need a full-fledged ECL list to make this work this way in SharePoint. Just to clarify: I don't need the metadata values in my documents to maintain a PERSISTENT link to the web service (if someone changes an underlying value in the web service DB, it doesn't need to be updated in the document table too). I just want SharePoint to offer up an updated set of source values from the web service if somebody goes in and edits the document metadata. Any ideas for how I can make this work?

Cannot delete external data list column due "Parameter name: combinedEncoded" error

Hello!   I migrated a Sharepoint 2007 to a Sharepoint 2010 by dbattach method. And now I want to delete the BDC list columns, but I'm getting this exception: System.ArgumentException:   Parameter name: combinedEncoded    at Microsoft.SharePoint.BdcClientUtil.SplitStrings(String combinedEncoded) What should I do to delete properly this columns?   Many thanks!

Invalid URL error on InfoPath Form Publish on an External List

I am using SP2010 Enterprise on Windows 7 and SPD 2010 & InfoPath 2010 all on the same machine in test.  I create an External List based on a ECT and Click on the 'Design Forms in InfoPath' icon and 'Item' which drops down from it.  That launches InfoPath Designer 2010 & downloads the form from http://<site>/lists/<mylistname>/Item/template.xsn and sets you up in an edit session.  I can make changes on the form but when I publish via File | Publish | External List (Publish Form To An External List) I get a save prompt for local copy & then an error: The following URL is not valid: http://<site>/lists/<mylistname>/Item/template.xsn  (same URL as it started with during the initial download). I took screen shots of the messages to make sure. Some things I have tried & observations: If I change to an internal Custom List the publish step (to list not external of course) works just fine. I also ran this test launching SPD as Administrator with no difference.  I stopped the System Event Notification service per http://serena-yeoh.blogspot.com/2006/08/infopathformurlnotvalid.html  'net stop sens' with no difference Any ideas are welcome. BTW I notice that this step of launching the InfoPath designer does change the form on the list in Sharepoint albeit slightly -

Excel External Table Data can only create new SharePoint List when Exporting from Excel?

I have a SQL Query that I refresh everyday in Excel. I want to be able to export this list over to a SharePoint list right after my refresh in Excel but I can only Export to a SharePoint list only once. If I try to export again to the same list then it will give me the error "The specified list name is already in use on this server. You must rename the list before publishing it to the server." I have web parts that have connections to this list and I do not want to export my Query to a new SharePoint list everyday. Does anyone know a way around this issue?

Unable to load External list in Sharepoint Foundation - error due to trial period expired

I've created an external content type and external list on Sharepoint Foundation. Everything is OK when creating and viewing these items in create mode in sharepoint designer. When trying to view the external list in the browser I get error message "Unable to display this Web Part..." When I opened the same list in the mobile view url I get a more descriptive error message: "The shim execution failed unexpectedly - The trial period for this product has expired.." I have read and followed the instruction related to this thread concerning expired trial period bug, and iisreseted the server after change but I still can't display the list. http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/644ab342-4eaa-468c-95c1-f345a4f65f26#644ab342-4eaa-468c-95c1-f345a4f65f26 Any ideas anyone??

Customizing External List Fields

I have two SharePoint features:  a BDC model and an external list definition.  Everything deploys and works as expected.  However, I would like to customize the fields in the list.  For example, I want a date only field, multi-line text field and a choice field instead of a date and time field and two single-line text fields.  I am using a custom schema.xml in the list definition, but the fields section is ignored.  I have also tried to alter the fields through the object model with no luck.  Currently, the solution is easy to deploy.  So I'd like to avoid additional steps in SharePoint Designer.  Although I don't see any options for customizing field types anyway (outside of manually editing the forms).  I'm not using the Enterprise edition, so Infopath is not an option.  In the BDC model in Visual Studio I set a few fields to Nullable DateTime.  The Central Admin shows it as DateTime and required though.  Surely there is a way to change that behavior.  Any suggestions?

CAML query on external list is not working

Hi, I have an external list, display data from SQLServer 2008. Table on SQL have an UniqueIdentifire (GUID) type column as a Primary key. It also has one identifier column, Its value is increased by 1, as the item is created. In share point server side code, I want to fetch single record against the primary key GUID value. I write down the CAML query on GUID column and run without any error. But all the external list data is returned instead of Single list item. I also try to filter on Auto Incremented column, but same problem is occurred.

BDC, external list with many-to-many Association, how to input data

Hi, I have built a BDC with VS2010 and Sharepoint Enterprise as target platform. It is basically a technical evaluation about many-to-many Associations in external content types. Its has 2 entities which are Person and Ticket and should reflect a data model where persons can work on multiple tickets and tickets can also involve multiple persons. For linking those 2 entities I have a link table which can be read via the "Navigators" and be modified via the "Associator/Disassociator". I made the model completely with the VS2010 bdcm designer and as far as I know its good. (Its a "foreign key less" association, the identifiers are UInt32.) Now my question is how do I actually use those manipulators, or at least show data and related data. With 1-to-n relations Sharepoint did render an interface for handling the relation for me, but with a keyless n-to-m relation I dont get any interface for connecting the entities at all. Then I tried to make this via Infopath, but as far as I can tell, you cant use a 2nd datasource there with external List. (And it seems to be supposed to be this way.)

Filter External List by a range of data

I have an external list web part that I am currently using a choice filter on to filter by a certain status.  The options are yes, no, or (empty).  However, I would like to be able to add an additional option to filter where the person has the previous status set to no, but has a value in a different field greater than 90. I realize that the choice filter probably wouldn't do this, and that I could do this easily with separate list views, where I have some sort of web part that can change the list views on the fly. I would think I could use one of those list filters to do this, but when I tried to do that for my original problem, I was unable to connect the filter to my external list. I could connect the list view filter to any internal list without an issue however.  Somebody commented on my old thread that I posted as cptInsane0 that I could just make an infopath view form that has buttons that do this.  I have dabbled with infopath a little bit, but I wasn't able to get infopath to connect to my external list either.  I first had to connect to an internal one, and then for some reason I could add the external list as an alternate data source. I can't be the first person who has had this issue. Could someone point me in the right direction?

SharePoint: External List in C# ?

Hello!   First of all, sorry to be here again to ask for help. But it's pretty hard to program for 2010 if u never programmed 2007. I would like to write a Console application or Powershel script to configure my SharePoint.   Meaning: I want it to make all the lists, External content types etc. Making a list with PS or Console application in C# is peanuts. But i can not figure out how to make an External List? The External Content type needed for the External List is allready on SharePoint with the name "Account".   So now i want to add a List which uses this External Content Type, so an External List. But i just can't figure out how this is done in either C# or PS.   Remember, everything has to be done by code, no sharepoint clicks can be involved.   Thanks!

Publishing HTML Field with External List

I have an external content type I created and an external list that I provisioned off of the content type.  I want to customize how the user edits some of these columns.  I need one of my list columns to be a field type "image" and one to be a field type of "HTML".  I tried exporting the list as an wsp and importing into an empty sharepoint project.  When I review the custom schema.xml for the list I can see the fields referenced from the externa content type.  I altered these fields so that they conform to the types I need.  For some reason when I deploy the list definition and instance those fields are still textboxes.  I have googled this topic and there doesn't seem to be much out there.  Does anyone have any idea as to how I can accomplish this.  I originally wanted to build my list structures out in sharepoint but there are many parent child relationships and after realizing I cannot create a lookup type field that references a sharepoint list declaritively in CAML due to the fact that the listid has not been created because it hasn't been provisioned I abandoned that concept and put my tables in a SQL database.  I need the user to be able to associate a sharepoint image and some HTML in a couple of columns in this table.

Create Lookup Site Column based on External List (SharePoint 2010)


I've got an External List and I want to create a Site Column, which is Lookup to a column in my external list. If I tick any of the columns in 'Add a column to show each of these additional fields' and click OK i get the following error message:

[NullReferenceException: Object reference not set to an instance of an object.]
  Microsoft.SharePoint.SPBusinessDataField.IsInDefaultView() +58
  Microsoft.SharePoint.SPBusinessDataField.CreateSpFieldForBdcType(String bdcName, Boolean hidden, Boolean readOnly, Boolean idField, SPAddFieldOptions op) +809
  Microsoft.SharePoint.SPBusinessDataField.AddNeededSecondaryFields(String wssNames, String[] oldNames, String[] newNames, SPAddFieldOptions op) +315
  Microsoft.SharePoint.SPBusinessDataField.OnAdded(SPAddFieldOptions op) +240
  Microsoft.SharePoint.SPFieldCollection.AddFieldAsXmlInternal(String schemaXml, Boolean addToDefaultView, SPAddFieldOptions op, Boolean isMigration, Boolean fResetCTCol) +759
  Microsoft.SharePoint.SPFieldCollection.Add(SPField field) +61
  Microsoft.SharePoint.ApplicationPages.FieldNewPage.Save() +513
  Microsoft.SharePoint.ApplicationPages.FieldNewPage.BtnOk_Click(Object sender, EventArgs e) +46
  System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
  System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +139
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend