Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Nickel Elite Contributor
Posts: 86
Registered: ‎10-21-2009
Accepted Solution

Problem With Insert or update custom DateTime field through Sage.Sdata.Client.dll

[ Edited ]

Hi,

 

When I am trying to insert or update any custom date field through an application consuming SDATA using following code snippet its giving me an Exception,

 

Exception caught during the processing of a message

Verb: PUT
Uri: http://192.168.1.154:4444/sdata/slx/dynamic/-/Contacts('C6UJ9A0028EW')?include=Address&format=atomen...

Original Message: could not update: [Sage.SalesLogix.Entities.Contact#C6UJ9A0028EW][SQL: UPDATE CONTACT SET MODIFYDATE = ?, WORKPHONE = ?, TESTDATE = ? WHERE CONTACTID = ?]

Inner Exception Message: The statement has been terminated. : The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Stack Trace:    at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.UpdateOrInsert(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, Object rowId, ISessionImplementor session)
   at NHibernate.Action.EntityUpdateAction.Execute()
   at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
   at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
   at NHibernate.Engine.ActionQueue.ExecuteActions()
   at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
   at NHibernate.Impl.SessionImpl.Flush()
   at NHibernate.Transaction.AdoTransaction.Commit()
   at Sage.Platform.TransactionScope.PerformDisposal(ICollection sessions)
   at Sage.Platform.Framework.Scopes.AbstractScope.Dispose()
   at Sage.Platform.NHibernateRepository.NHibernateRepository.SaveImpl(Object instance)
   at Sage.Platform.NHibernateRepository.NHibernateRepository.Sage.Platform.Repository.IRepository.Save(Object instance)
   at Sage.Platform.EntityFactory.Save(Object instance)
   at Sage.Platform.SData.RequestHandlerBase`3.InternalPut(SDataUri uri, String ifMatch, TFeedEntry entry)
   at Sage.Platform.SData.RequestHandlerBase`3.Put(TFeedEntry entry)
   at Invoke51e452adc27d4426bb28ce4d07d208f0.Invoke(Object , IRequest )
   at Sage.Integration.Messaging.RequestTargetRegistration.RequestTargetInvoker.Invoke(IRequest request)
   at Sage.Integration.Messaging.Request.Process(RequestTargetInvoker invoker)
   at Sage.Integration.Adapter.AdapterController.RealAdapterController.Process(IRequest request)
   at Sage.Integration.Adapter.AdapterController.RealAdapterController.ProcessWorker(IProtocolRequest protocolRequest)
   at Sage.Integration.Adapter.AdapterController.Process(IProtocolRequest request)
   at Sage.Integration.Messaging.MessagingService.Process(IProtocolRequest protocolRequest)

 

For Insert,


  SDataTemplateResourceRequest mysdataTemplate = new  SDataTemplateResourceRequest(mydataService);
            mysdataTemplate.ResourceKind = "Contacts";
            //mysdataTemplate.Include = "Address";
            //read in the template
            AtomEntry tempContactEntry = mysdataTemplate.Read();
            SDataPayload tempContactPayload = tempContactEntry.GetSDataPayload();
            //Get payloads from Account
            SDataPayload account = (SDataPayload)tempContactPayload.Values["Account"]?? new SDataPayload();
            SDataPayload shippingaddress =(SDataPayload)tempContactPayload.Values["ShippingAddress"] ?? new SDataPayload();
            tempContactPayload.Values.Clear();
            //set the values...
            tempContactPayload.Values["FirstName"] = txtFirstName.Text;
            tempContactPayload.Values["LastName"] = txtLastName.Text;
            tempContactPayload.Values["WorkPhone"] = txtWorkPhone.Text;
            tempContactPayload.Values["HomePhone"] = txtHomePhone.Text;
            tempContactPayload.Values["Email"] = txtEmail.Text;
            tempContactPayload.Values["AccountName"] = "Ajay";// myRow.Row["AccountName"];
            tempContactPayload.Values["TestDate"] = DateTime.Now.ToString();  //Custom DateTime field that creting problem. 
            //special for accounts.
            tempContactPayload.Values["Account"] = account;
            account.Key = lblAccountID.Text.ToString();
            //have to plug in some default values for the address (since it is not on our UI)
            //address.Values["Description"] = "Mailing";
            //address.Values["Address1"] = "123 Main Street";
            //tempContactPayload.Values["Address"] = address;
            shippingaddress.Values["Description"] = "Mailing";
            shippingaddress.Values["Address1"] = "123 Main Street";
            tempContactPayload.Values["shippingaddress"] = shippingaddress;
            //get a single resource
            mydataSingleRequest = new SDataSingleResourceRequest(mydataService);
            mydataSingleRequest.ResourceKind = "Contacts"; 
            //set the single resource to the new atomentry
            //tempContactEntry.SetSDataPayload(tempContactPayload);
            mydataSingleRequest.Entry = tempContactEntry;
            //create the new record...
            AtomEntry newContactEntry = mydataSingleRequest.Create();

 

 

For Update,

                 payload.Values.Clear();
                //load nav with values from UI
                payload.Values["FirstName"] = txtFirstName.Text;
                payload.Values["LastName"] = txtLastName.Text;
                payload.Values["WorkPhone"] = txtWorkPhone.Text;
                payload.Values["HomePhone"] = txtHomePhone.Text;
                payload.Values["Email"] = txtEmail.Text;
                //nce update
                payload.Values["CUSERADDRESS1"] = "ADDR1";
                payload.Values["CUSERADDRESS2"] = "ADDR2";
                payload.Values["CUSERADDRESS3"] = "ADDR3";
                payload.Values["CCity"] = "MUM";
                payload.Values["CPinCode"] = "400040";
                payload.Values["CState"] = "MH";
                payload.Values["ModifyDate"] = DateTime.Now.ToString();//This datetime field is working fine.
                payload.Values["ModifyUser"] = "SDATAUSER";
                payload.Values["TypeOfUser"] = "SDATAUSER";
                payload.Values["CInternetAdditionalConn"] = Convert.ToBoolean("FALSE");
                payload.Values["CPaymentOption"] = 1000;
                //payload.Values["PRIMARYOFFICEID"] = null;
                //payload.Values["PRIMARYLLID"] = null;
                //payload.Values["PRIMARYVSATID"] = "Q6UJ9A08KYC5";
                payload.Values["DEALERDELETIONFLAG"] = "Y";
                DateTime? curDate= DateTime.Parse(DateTime.Now.ToString());
                payload.Values["TestDate"] = curDate;//Custom DateTime field that creting problem.

                AtomEntry myNewEntry = mydataSingleRequest.Update();

 

 

But, when i did tried by changing the concerned database field type fromdatetime to datetime2, though its not throwing any error, its picking a datevalue '0001-01-01 00:00:00.0000000' for the records having value NULL for this field and while  inserting '0001-01-01 00:00:00.0000000'  as a value for this field in place of whatever value we are passing for insert or update.

 

The details of my system are as under,

OS- Windows Server 2008 R2

DataBase- SQL Server 2008 R2

SalesLogix Version : SLX 7.5.3.4227

 

Your help in this regard will be highly  solicited.

 

Thanks in advance

Nickel Elite Contributor
Posts: 86
Registered: ‎10-21-2009

Re: Problem With Insert or update custom DateTime field through Sage.Sdata.Client.dll - Problem Resolved

The SData Client library, contained in the Sage.SData.Client.Common namespace is a class called “SyndicationDateTimeUtility”.  This class has methods to convert normal date/time values into what SData wants/gives you.

 

·ParseRfc3339DateTime – This will convert an SData Date/Time value into a “normal” Date/Time value (when getting values through SData.

 

·ToRfc3339DateTime – This will convert a “normal” Date/Time value into one that SData understands (for inserts & updates).

 

Below is sample code that gets and sets a custom Date/Time value via SData (client libraries).  Hopefully this helps…

 

public DateTime LastChecked

{

get

    {

        if (_entity.Values["LastChecked"] != null)

             return SyndicationDateTimeUtility.ParseRfc3339DateTime(_entity.Values["LastChecked"].ToString());  

            // Convert SData Date/Time to standard Date/Time

        else

             return _defaultDate;

            }

 

 set

     {

         _entity.Values["LastChecked"]=SyndicationDateTimeUtility.ToRfc3339DateTime(value);

         // Convert standard Date/Time to SData Date/Time

     }

}