Remove From My Forums

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

Question
0

Sign in to vote

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

0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

is that a datatable you are trying to map? Is the datatable field type declared?

Friday, September 5, 2014 11:41 PM
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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

Asked by:

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

Question

All replies