Asked by:
How do I update a date value from SQL table to CRM datetime

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