Lookup Column - System.FormatException: String was not recognized as a valid DateTime.

Sep 14, 2012 at 5:47 AM
Edited Sep 14, 2012 at 5:47 AM

Hi,

 

I have a very basic config file as per below but for some reason am receiving the error as detailed below. I dont know where it is getting dates confused as Im not telling it sync any date columns?

 

<?xml version="1.0" encoding="utf-8" ?><Configuration>  <ConnectionStrings Default="SLAM">    <add Name="SLAM" ConnectionString="Database=Data Source=.;User Id=ExtranetSyncSA;Password=*****" />    </ConnectionStrings>

  <DataMapping DataSchema="SLAM">


    <List Site="projects/beta" Name="Projects" ActivationOrder="1">     

<Fields>

<Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>             </Fields>   

</List>

 <List Site="projects/beta" Name="Milestones" ActivationOrder="2">     
<Fields>        

<Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="false"></Field> <Field  Name="Project" Column="ProjectListItemID" SPType="Lookup" AssociatedTypeName="Projects"></Field>

      </Fields>   

</List>       

</DataMapping>
  </Configuration>

 

 

System.FormatException: String was not recognized as a valid DateTime.     at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)     at AWS.SLAM.SharePoint.SPListItemExtender.VerifyItemRequiresSlamUpdate(SPListItem listItem)     at AWS.SLAM.SharePoint.DefaultListItemExtensions.EnsureSlamAssociations(SPListItem listItem, List`1 associationFields, Boolean force)     at AWS.SLAM.SharePoint.DefaultListItemExtensions.GetFieldValues(SPListItem listItem, List`1 outputFields)     at AWS.SLAM.SharePoint.SPListItemExtender.GetFieldValues(SPListItem listItem, List`1 outputFields)     at AWS.SLAM.Events.DefaultEvents.Update(SPListItem listItem, IDataServices dataServices, IConfigurationManager configuration, ILogger logger, String eventPrefix)

Nov 21, 2012 at 5:13 PM

Did you get an answer to this, I have attempted to get SLAM 2.1 working with again very simple syntax, but I am getting exactly the same error.  Not a single date time field is included.  Also the Association tables are not getting populated with any data, just the standard tables.

Nov 22, 2012 at 2:04 AM

Me too. I'm using the same config file I used in the 2007 version of this site. Get this error regarding datetime after attempting to activate.

System.Exception: Logging error: insert into [SLAM].[MessageLog] select convert(datetime, '11/21/2012 19:56:03.922', 101), 'Information', 'Deactivation Initiated' System.Data.SqlClient.SqlException: Invalid object name 'SLAM.MessageLog'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)

at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)

at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)

at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)

at System.Data.Linq.DataContext.ExecuteCommand(String command, Object[] parameters)

at Slam.Data.DataContextExtender.ExecuteCommand(DataContext context, Boolean commit, String command)

at Slam.Data.SqlLogDataRepository.LogMessage(String tableSchema, String messageType, String message)

at Slam.Data.SqlLogDataRepository.LogMessage(String tableSchema, String messageType, String message)

at Slam.Activation.Activator.Deactivate(String siteCollectionUrl)

at Slam.Administration.Activation.btnAttachEvents_Click(Object sender, EventArgs e)

at System.Web.UI.WebControls.Button.OnClick(EventArgs e)

at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)

at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Dec 14, 2012 at 9:55 AM

May not be the correct thing to do or be suitable for others, but I did the below.

When debugging the SLAM source code two issues where found:

1. Associated Lists where searched in every site, which for me causes a problem as I had an empty list of same name in root folder.  Resolved this so it only looks in parent of listitem.
2. Was getting error around datetime conversion and associations were being ignored.  Modified to require update if datetime unable to parse.
Both methods below needed updating in SPListItemExtender.cs

Modified Method GetLookupValueListItems
 
 //for (int i = 0; i < listItem.Web.Site.AllWebs.Count; i++)
 string parent = listItem.ParentList.ParentWeb.Name;
 //TC - changed I below in between [] from i to parent, as should only check site listitem is in.
 using(SPWeb lookupSite = listItem.Web.Site.AllWebs[parent])

public static bool VerifyItemRequiresSlamUpdate(this SPListItem listItem)
{
 string updatedDateString = listItem.GetSlamPropertyValue("LastUpdated");
        //TC - Added try catch as it was causing association to fail.
        bool updatereq = true;
        try
        {
  updatereq =  !(!String.IsNullOrEmpty(updatedDateString) && (DateTime.Parse(updatedDateString).CompareTo(DateTime.Parse(listItem["Modified"].ToString())) >= 0));
        }
        catch
        {
         return true;
        }
        return updatereq;
}

Not sure if this is best solution, but resolved my issue.

Developer
Dec 14, 2012 at 4:50 PM

To the first solution:

What may be necessary, which I am looking into doing, is to check the listItem's ParentList.ParentWeb first, then look in other sites.  The reflection used in the method to find the LookupList value allows it to find custom lookup list field values that may not actually be of type SPFieldLookup, which may be pointing to lists in other sites.  Likewise it should also work with a SPFieldLookup pointing to another site.  Limiting the selection to just the ParentList.ParentWeb means cross site lookups stop working.

To the second solution:

This is ok, though if I understand correctly the problem is that DataTime.Parse is failing.  If updatedDateString has a value it may be a matter of forcing Parse to look for a particular format in all cases.  Once a DateTime is parsed then culture difference shouldn't matter.  If a culture mismatch between the updatedDateString and expected format for Parse is the source of the issue then it may be that that check will always fail.  VerifyItemRequiresSlamUpdate loses its purpose then, and you will find a lot of unnecessary updating is happening.

Mar 22, 2013 at 1:28 PM
Edited Mar 22, 2013 at 1:29 PM
I had this same issue yesterday.

Regarding the second solution, it appears that the 'LastUpdated' SLAM property value is in US date format but the 'Modified' field in my list is in UK format due to the site being set to use UK regional settings.

I've updated the above VerifyItemRequiresSlamUpdate method to use SPUtility.ParseDate on the 'Modified' field only:
public static bool VerifyItemRequiresSlamUpdate(this SPListItem listItem)
{
        bool result;
        string updatedDateString = listItem.GetSlamPropertyValue("LastUpdated");
        string modifiedDateString = listItem["Modified"].ToString();
        SPWeb web = listItem.Web;

        try
        {
            result = !(!String.IsNullOrEmpty(updatedDateString)
                && (DateTime.Parse(updatedDateString, DateTimeFormatInfo.InvariantInfo)
                    .CompareTo(SPUtility.ParseDate(web, modifiedDateString, SPDateFormat.DateTime, false)) >= 0));
        }
        catch (Exception ex)
        {
            try
            {
                result = !(!String.IsNullOrEmpty(updatedDateString)
                    && (DateTime.Parse(updatedDateString)
                        .CompareTo(SPUtility.ParseDate(web, modifiedDateString, SPDateFormat.DateTime, false)) >= 0));
            }
            catch (Exception)
            {
                ILogger logger = ComponentService.GetInstance<ILogger>();
                logger.LogMessage("Exception", "Unable to compare dates: updated = " + updatedDateString + ", modified = " + modifiedDateString + ", " + ex.StackTrace);
                result = true;
            }
        }

        return result;
}
I also noticed that in the 'LastUpdated' datetime is being set using the InvariantInfo, hence the first check. At the end of the sync, I was receiving an exception on the updatedDateString masked as System.Data.SqlClient.SqlException: There is already an object named 'SLAM' in the database, hence the second check.

It seems to work for me - exception free - and hopefully prevents a lot of unnecessary updating?

Hope this helps.