locked
How do I update a date value from SQL table to CRM datetime RRS feed

  • Question

  • private void MapColumns()
            {
                try
                {
                    columnMapping = new Dictionary<string, string>();

                    columnMapping.Add("bah_derivedawarddate", "Award_Date");
                    columnMapping.Add("bah_atpduedate", "Proposal_Submission_Date");
                    columnMapping.Add("ascent_opportunitynumber", "OpportunityID");

     

                }

    I have a SQL table and am trying to update the CRM database with values from SQL table.

    The SQL table has datetime for "Award_Date"

    IN CRM it is also datetime.

    Its giving me a incorrect attribute value type system.string.  How do I update a date value from SQL to CRM datetime

    Friday, September 5, 2014 1:51 PM

All replies

  • hello Nandhini,

    please let us know crm version. Are you trying to update crm database directly?

    regards,

    Jithesh

    Friday, September 5, 2014 10:29 PM
  • We are in crm 2011.

    Yes I am updating CRM database directly , its a custom date field.

    So in SQl table the  datatype of "award_date"  is datetime.

    In CRM I created the award_date  attribute as as datetime.

    I am having the same issue for another field its the money field.

    In sql table I have the "award_value" as money

    and in CRM also the award_value attribute is   money.

    This also throws an incorrect attribute value type system.string.

    Please let  me know.

    Thanks

    Friday, September 5, 2014 10:51 PM
  • Hello,

    I am Sure you have justification for directly updating crm. It is not supposed by Microsoft.

    now you do not have crm in picture, in effect, you are updating a sql server database from your program.

    please cast the string to date time type. For currency, update the base field.

    better, use platform service to update crm. All these issues will go away and your data consistency, will be taken care by the platform - including any plugin or workflows registered.

    regards,

    Jithesh

    Friday, September 5, 2014 11:02 PM
  • columnMapping.Add("bah_derivedproposaltotal", "Total_Est_Sales");

                            crm field value          sql table value

    columnMapping.Add("bah_derivedawarddate", "Award_Date");

                            crm field value           sql table value

    how to cast these fields , please let me know.

    Friday, September 5, 2014 11:12 PM
  • is that a datatable you are trying to map? Is the datatable field type declared?

    Friday, September 5, 2014 11:41 PM
  • Yes its a datatable

    Total_Award_value(money,null) this is the source SQL table

     CRM it is bah_derivedawardvalue  which is currency type with precision 2 -- target

    Saturday, September 6, 2014 12:12 AM
  • When creating a datatable - the source, have you specified the columns and the data type? Or loading the result of a query into a datatabe?.

    pre define the source and destination datatable and the type.

    regards

    Jithesh

    Saturday, September 6, 2014 1:34 AM
  • Yes I have created a table in SQL server and specified the datatypes.

    Also then querying the table into a datatable.

    DataTable proposalTable = null;

    DataSet proposalSet= null;

    "SELECT * FROM PTables.dbo.ProposalTable";


     proposalSet = ExecuteQuery(command);

    if (proposalSet != null && proposalSet.Tables.Count > 0)

                    {

                        proposalTable = proposalSet.Tables[0];

                    }

    else


                    {
    return false;

                    }

    Saturday, September 6, 2014 1:36 PM
  • hi,

    please explicitly create the data table and specify the column definition. Then load the data table using the query.

    example http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(v=vs.110).aspx

    look at the function similar to that.

    regards,

    Jithesh

    Sunday, September 7, 2014 12:32 AM