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

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

DateTime values in external lists must fall within the range of 1/1/1900 and 12/31/8900

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :C#

I have a WCF service and I have setup a BCS External Content Type to. All working fine, I have created a default list which displays the list of records. Problem is though one of the fields in the WCF is a datetime and if it has a value then all is fine but when you set the datetime field to nothing in the WCF I get the list showing all right but when I click on the record to get the default item form I get the following error. If I use a infopath form as default then it fails.:

DateTime values in external lists must fall within the range of 1/1/1900 and 12/31/8900. This form cannot be displayed because the external item contains this date: 01/01/0001 00:00:00

Obviously the datetime field minimum value is 1/1/0001 or something like that. Doesn't like nullable date fields. I don't want to set the field to 1/1/1900 as I want the default item form to display 1/1/1900 as a null or empty, don't want to make the field string as having the date fields in the list helps with sorting. I don't want to create specific list webpart or form at this stage but use the default list a form the Sharepoint designer creates.

Any ideas on how to handle or make null datetime field go back to the ECT?



View Complete Post

More Related Resource Links

Dim CalendarList As SPList = Web.Lists(NameOfList) error: value does not fall within expected range



The following line  is throwing error message " value does not fall within expected range "

Using Site As New SPSite(SitePath)

   Using Web As SPWeb = Site.OpenWeb()

    Dim CalendarList As SPList = Web.Lists(NameOfList) ''''''''' throws error

tha value of NameOfList is 'Project%20Calander'

the url of the list is 'http://dev/working/landing/testsite/Lists/Project%20Calendar/calendar_copy(1).aspx'

Its the correct name of the list so not sure what as it could be expecting?


System.DateTime.AddDays: Value to add was out of range.


I'm getting the following error:

[ArgumentOutOfRangeException: Value to add was out of range.
Parameter name: value]
   System.DateTime.Add(Double value, Int32 scale) +7657639
   System.DateTime.AddDays(Double value) +19

The line from which this error originates has the following code:

expires = Now.AddDays(30)

It occurs irregularly and irreproducably. The server date/time is correct and set to GMT. I've heard that it could be some curiosity to do with timezones, but that is speculative.

It seems that we're some time from the year 10,000 yet, so I can't understand why adding thirty days to the current time could cause this! Any ideas? Culture settings perhaps?

Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

Sql DateTime2 to .NET DateTime conversion results in out-of-range exception

I posted it in C# General because I thought that the subject was not Sql Smo related, although I use Smo extensively even in this routine for connection. Unfortunately after a few days no constructive suggestion has been advanced. Two posts the thread collected are not helpful. I am wondering if Alok or someone else with the status of MSFT Sql Server developers take a look at this very importan problem for me which became a stumbling block.Thanks. AlexB - Win_7 Pro64, SqlSer64 WinSer64

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

Value does not fall within the expected range.

I am trying to upload a csv file into a sharepoint list. (as demonstrated in SDK sample that i downloaded from here http://www.microsoft.com/click/SharePointDeveloper/html/Default.html)                 //open the CSV file for reading using (StreamReader fileRdr = File.OpenText(paramFileName)) { // connect to the site collection using (SPSite spSite = new SPSite(paramWeb)) { //open the web using (SPWeb spWeb = spSite.OpenWeb()) { //get the List from the site SPList spList = spWeb.Lists[paramList]; //read the files in the header string line = fileRdr.ReadLine(); string[] fieldNames = line.Split(splitArray); //get an Array of field from the List - this assumes that the fields name match // we need the field id when inseting rows SPField[] spFields = new SPField[fieldNames.Length]; for (int looper = 0; looper < fieldNames.Length; looper++) { string strName = fieldNames[looper];

External Lists and Infopath forms issue with Secondary Data Sources

I've followed this tutorial for creating and customising forms for external data (http://blogs.msdn.com/infopath/archive/2010/03/11/customize-an-external-list-form-in-infopath-2010.aspx).  The only difference being that my seconday data source is not an XML file (I've tried database connection and existing sharepoint lists both internal and external) When running the design checker I get the message "Additional Data connections not supported" and my form won't publish.  Any ideas?

Problems with access to external lists in SharePoint 2010

Our environment is SharePoint 2010 Enterprise, Windows Server 2008 R2 Standard and SQL server 2008 R2.   We have External lists that access a local database in SharePoint SQL Server. When new lists are added and permissions are given in the SharePoint Central Administration the domain users had to be in the Windows administration group to access the lists.   Recently in the same environment the domain users were removed from Windows Administration user group and could access the external lists.   In another similar test server we found adding the domain users to Windows Administration does not give the new domain users access to External lists.   The inconsistency is the issue. Has anyone seen this issue or provide a possible solution?

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

Starting a list of lists with values

Dear wizards, In C# we can start a List with values as follows: List<double> foo = new List<double> (new double[10]); This will yield me a list with 10 0's. However, I wished to do something like this: List<List<double>> = new List<List<double>(new double[10])>(10); In other words, I would like to start up 10 Lists, each having 10 zeroes.   Grateful if someone help me out  

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?

Cannot access Workflow from List Item menu: "Value does not fall within the expected range."


We are using Sharepoint 2007 sp2.

I am using Sharepoint Designer 2007 to create workflow for some List, suddenly I cannot acess some list items' workflow,and was informed  "Value does not fall within the expected range." , but other items are all ok.

I don't know whether this is concerned with the service pack 2, I installed sp2 yesterday.

Replace standart DispForm with my application page for External Lists



Hi all,

I'd like to use my application pages (with code behind, not modified with Sharepoint Designer pages) for View/Edit/New operations.

As I know there is an issue how to do it:



SPContentType ct=web.Lists["ListName"].ContentTypes["ContentTypeName"];



And this works fine for common Lists, but when I'm trying to do it with External List,

I have an Exception on Update method:

SPException: The collection cannot be modified.


I've tryied:


web.AllowUnsafeUpdates = true;


ct.ReadOnly = false;

// or
ct.Sealed = false;

and nothing of it helped me...

Is anybody knows how can I make it? May be another method to do it?

Sharepoint Foundation - Create and deploy custom edit/view forms for external lists


Firstly, we are using sharepoint foundation so Infopath forms are not an option for us.

We have a Visual Studio sharepoint project which deploys a complete sharepoint site. The site contains a number of external lists and we want to be able to create customised view/edit forms for these lists and add them to our Visual Studio project so they can be deployed as part of the new site. The idea is that we can deploy the solution to a fresh site with a default set of forms which can then be customised for different implementations of the site.

Here's the steps we have been using to try and do this:

1. Deploy the base project (BDC) to the sharepoint site

2. Create external lists based on the BDC content types

3. Use Sharepoint Designer to create new edit/view forms for the lists and set these as the default forms for the list

3. Save the site as a template

4. Import the template into a new Visual Studio project and select the lists we require. This gives us a List Definition, a pages module (with element file), an elements file and a schema file (both for the List Instance).

5. We then add the contents of the imported project to our main solution as follows:

  • Create a new List Definition (including List Instance)
  • Copy the contents of the imported Elements.xml to the Elements file for our List Instance.

Value does not fall within the expected range? Using Internal Field Name returned from SPListItem Fi

Here's the code.

myDocument is an SPListItem.
string fieldName = "Document Category";
string internalFieldName = myDocument.Fields[fieldName].InternalName.ToString();
Type myType = myDocument[internalFieldName].GetType();
internalFieldName gets the value "Document_x0020_Category"

But myType = myDocument[internalFieldName].GetType() throws an exception error of "Value does not fall within the expected range"??

If the fieldname is found in the fields collection, and returns the internal field name, why would using the internal field name as an indexer into the document throw an exception?

myDocument has a value in field "Document Category" when I view properties on the document in the document library! I am expecting the field type to be of

Surface data in external lists in Sharepoint Foundation 2010



I have seen numerous articles for the above, but they all just gloss over the step I am having a problem with, assuming nobody will have an issue with it. 

I'm trying to create an External Content Type so I can use it to display external data (from SQL Server) in an External List.  When I get to the step to add the connection and the SQL Server connection dialog box appears, I enter the server and database names and click OK with the Connect with User's Identity radio button still selected and I get the error message: login failed for user ''. The user is not associated with a trusted SQL Server connection.  I've tried to read up on the different purposes of the radio buttons on offer on this dialog box but don't know which I should really be using and don't think I can create a target application ID anyway in Foundation (for the other two radio button options) as that has to be done in the secure store services.  Can anybody help with this?

Workfow support for External Lists?


Will there be Workflow support added to External Lists post beta?

SharePoint Developer - MCTS SharePoint Dev, WSS Dev

Intergen: http://www.intergen.co.nz
Twitter: http://twitter.com/chakkaradeep
Blog: http://www.chakkaradeep.com
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