none
Urgernt help needed

    Question

  • I have been trying and failing at this for over a week now, and have to admit defeat.

    I'm trying to create a SQLCRL database project to add and update records from SQL to CRM2011. I have, with help managed to get the Insert record completed, but have been stuck with trying to get  an accounted from an attribute value. There have been numerous snippets of code I have tried, but I have to admit, I feel a bit out of my depth. I would welcome any help me get this working and understand. What I want to do is update an account in crm based on a name. All the documents I have found say I need to get the accountid for the record. Each name is unique, so I thought the following would return me the accountid, but I don't know how or if I should use retrieve or what I have below and how to return it back. I am using crmservices

     

       [Microsoft.SqlServer.Server.SqlProcedure]
        public string  GetAccountid (SqlString LicenceNo)
        {
            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = "xxxxxxx";
    
            using (CrmService crmService = new CrmService())
            {
                crmService.Url = "https://xxxxx.xxxx:xxx/MSCRMServices/2007/CrmService.asmx";
                crmService.Credentials = new System.Net.NetworkCredential("xxxx", “xxxxxxx", "XXX");
                crmService.CrmAuthenticationTokenValue = token;
                crmService.UnsafeAuthenticatedConnectionSharing = true;
                crmService.PreAuthenticate = true;
    
    
                ColumnSet columns = new ColumnSet();
                columns.Attributes = new string[] {"new_companyname", "name"};
                ConditionExpression accountnName = new ConditionExpression();
                accountnName.AttributeName = " name ";
                accountnName.Operator = ConditionOperator.Equal;
                accountnName.Values = new string[] {LicenceNo};
    
                FilterExpression filter = new FilterExpression();
                filter.FilterOperator = LogicalOperator.Or;
                filter.Conditions = new  ConditionExpression [] {accountnName};
    
                QueryExpression query = new QueryExpression();
                query.ColumnSet = columns;
                query.EntityName = EntityName.account.ToString();
                query.Criteria = filter;
    
                RetrieveMultipleRequest request = new RetrieveMultipleRequest();
                request.Query = query;
    
                 RetrieveMultipleResponse res = (RetrieveMultipleResponse)crmService.Execute(request);
    
    
                 return;
    
    
            }
        }
    

     

     


    Dont ask me .. i dont know

    Thursday, July 11, 2013 5:35 PM

Answers

  • Which version of the SDK are you using? Reference striking me as a bit odd.

    Would expect to see these references

    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Client;
    using Microsoft.Xrm.Sdk.Query;
    using Microsoft.Xrm.Sdk.Discovery;
    
    // This namespace is found in Microsoft.Crm.Sdk.Proxy.dll assembly
    // found in the SDK\bin folder.
    using Microsoft.Crm.Sdk.Messages;


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    • Marked as answer by Pete Newman Monday, July 15, 2013 6:43 PM
    Monday, July 15, 2013 6:10 PM

All replies

  • Hey Pete,

    Have you tried adding AccountID to the columnset? 

    columns.Attributes = new string[] {"new_companyname", "name", "accountid"};

    Let me know when you get a second!


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    Friday, July 12, 2013 8:58 PM
  • Hi Brett,

    Thank you for you help. If I'm honest, I'm a bit out of my depth, and no matter how many threads I have read, I still don't get it. What I'm trying to do is parm in an attribute value and update the account. My code has been chopped so many times as I try different things. The only doc's I can find to get the accounted is what I have used above. LicenceNo is the 'name' and is unique, and from what I have read I need to get the accountid to be able to update that record. Before I even try to add extra parameters I was trying to get

                account updaterecord = new account();
                updaterecord
    .new_companyname = "UPDATED RECORED";

    I don't understand why I have to use RetrieveMultiple, when there is only 1 value for the LicenceNo, and have spent hours trying to figure out how to get the accountid and then use that accounted to update the field new_companyname with the value "UPDATED RECORED"

    Of course this would not be the complete code, but will help me learn, and something I can build from


    Dont ask me .. i dont know

    Friday, July 12, 2013 9:27 PM
  • No worries, will try and help you through this one :).

    To answer the RetrieveMultiple question in a bit more detail. To execute an "Update" request the id for the record has to be included. While company name may be unique, in order for CRM to understand which record to update you need to get the id for the record in this case accountid. To get the accountid when all you have is the CompanyName  you will have to make a call to CRM prior to executing the "Update" request. That is the reason I recommended adding the accountid column to the RetrieveMultiple request. If you already had the accountid available like company name you wouldn't have to do this.

    account updaterecord = new account();
    updaterecord.new_companyname = "Updated Record";
    
    // Set the id for the record to update if you don't have the accountid prior to running the update request you will need to retrieve this column from CRM
    updaterecord.id = <GUID goes here>;
    
    // Executes Update
    crmService.Update(updaterecord);

    Does needing the accountid make more sense?

    Side note: if updaterecord.new_companyname isn't resolving (meaning you don't have all the entity data pre cached in a cs file). You can also write this like this:

    updaterecord["new_companyname"] = "new company name";


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    Friday, July 12, 2013 10:29 PM
  • Hi Brett, That's starting to make more sense, but what I don't know is how to get the accounted from  RetrieveMultipleResponse res = (RetrieveMultipleResponse)crmService.Execute(request);

    Dont ask me .. i dont know

    Friday, July 12, 2013 10:33 PM
  • Glad to hear it.

    Lets walk through a few lines of code that perform this request

    // Create the ColumnSet that indicates the fields to be retrieved.
    
       ColumnSet cols = new ColumnSet();
    
       // Set the properties of the ColumnSet.
    
       cols.Attributes = new string [] {"new_companyname", "accountid"};
    
       // Create the ConditionExpression.
    
       ConditionExpression condition = new ConditionExpression();
    
       
    
       // Set the condition for the retrieval to be when the specific company name
    
       condition.AttributeName = "new_companyname";
    
       condition.Operator = ConditionOperator.Like;
    
       condition.Values = new string [] {"new_companyname value here};
    
       // Create the FilterExpression.
    
       FilterExpression filter = new FilterExpression();
    
       
    
       // Set the properties of the filter.
    
       filter.FilterOperator = LogicalOperator.And;
    
       filter.Conditions = new ConditionExpression[] {condition};
    
       // Create the QueryExpression object.
    
       QueryExpression query = new QueryExpression();
    
       
    
       // Set the properties of the QueryExpression object.
    
       query.EntityName = EntityName.account.ToString();
    
       query.ColumnSet = cols;
    
       query.Criteria = filter;
    
      
    
       // Retrieve the account.
    
       BusinessEntityCollection account = service.RetrieveMultiple(query);


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    Saturday, July 13, 2013 12:26 AM
  • Hi brett,

    Im getting an error on 

    BusinessEntityCollection account = service.RetrieveMultiple(query);

    error is  
    'SqltoCRMTest.crmSdk.service' does not contain a definition for 'RetrieveMultiple'

     

    Dont ask me .. i dont know

    Saturday, July 13, 2013 12:38 AM
  • Hey Pete,

    Can you include a more of a code sample so I can see what is going on around the service? Also, include the items in your using statements at the top please :)


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    Saturday, July 13, 2013 1:32 AM
  • Hi Brett,

    This is my code so far, It's not pretty, but in my defence, Im still learning

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using SqltoCRMTest.crmSdk;
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void CreateNewCustomer(SqlString LicenceNo, SqlString CompanyName, SqlInt32 Software, SqlInt32 SoftwareStatus) 
        { 
            CrmAuthenticationToken token = new CrmAuthenticationToken(); 
            token.AuthenticationType = 0;
            token.OrganizationName = "xxxxxxx"; 
            
            using (CrmService crmService = new CrmService()) 
            { 
                crmService.Url = "https://xxxxx.xxxxxx:xxxx/MSCRMServices/2007/CrmService.asmx"; 
                crmService.Credentials = new System.Net.NetworkCredential("xxxx", xxxxx", "xxxx"); 
                crmService.CrmAuthenticationTokenValue = token; 
                crmService.UnsafeAuthenticatedConnectionSharing = true; 
                crmService.PreAuthenticate = true;
                account account = new account();
                account.name = LicenceNo.Value;
                account.new_companyname  = CompanyName.Value;
      
                account.new_software = new Picklist();
                account.new_software.Value  = Software.Value;
                account.new_state = new Picklist();
                account.new_state.Value = SoftwareStatus.Value ;
                crmService.Create(account); 
            } 
        }
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void GetAccountid(SqlString LicenceNo)
        {
            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = "xxxxxx";
            using (CrmService crmService = new CrmService())
            {
                SqlPipe sp;
                sp = SqlContext.Pipe;
                
                crmService.Url = "https://xxxxx.xxxx:xxx/MSCRMServices/2007/CrmService.asmx";
                crmService.Credentials = new System.Net.NetworkCredential("xxxx", xxxx", "xxx");
                crmService.CrmAuthenticationTokenValue = token;
                crmService.UnsafeAuthenticatedConnectionSharing = true;
                crmService.PreAuthenticate = true;
                crmService.CrmAuthenticationTokenValue = token;
                crmService.UnsafeAuthenticatedConnectionSharing = true;
                crmService.PreAuthenticate = true;
                            ColumnSet cols = new ColumnSet();
                cols.Attributes = new string[] { "new_companyname", "accountid" };
                ConditionExpression condition = new ConditionExpression(); 
                
                
                condition.AttributeName = "name";
                condition.Operator = ConditionOperator.Like;
                condition.Values = new string [] {(string) LicenceNo };
                FilterExpression filter = new FilterExpression();
                filter.FilterOperator = LogicalOperator.And;
                filter.Conditions = new ConditionExpression[] { condition };
                QueryExpression query = new QueryExpression();
                query.EntityName = EntityName.account.ToString();
                query.ColumnSet = cols;
                query.Criteria = filter;
                BusinessEntityCollection account = service.RetrieveMultiple(query);
                 return;
            }
        }
    };


    Dont ask me .. i dont know

    Saturday, July 13, 2013 9:36 AM
  • Which version of the SDK are you using? Reference striking me as a bit odd.

    Would expect to see these references

    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Client;
    using Microsoft.Xrm.Sdk.Query;
    using Microsoft.Xrm.Sdk.Discovery;
    
    // This namespace is found in Microsoft.Crm.Sdk.Proxy.dll assembly
    // found in the SDK\bin folder.
    using Microsoft.Crm.Sdk.Messages;


    If this post answers your question, please click "Mark As Answer" on the post and/or "Mark as Helpful"

    • Marked as answer by Pete Newman Monday, July 15, 2013 6:43 PM
    Monday, July 15, 2013 6:10 PM
  • Hi Brett,

    After spending another fruitless day trying to get this working, I posted up my newest attempt on another thread. The Microsoft.Xrm references are not available to me inside of the SQLCLR database project.

    I will close this down as answered, and thank you for investing your time in assisting me. Without people like you, there would be a great number of us struggling and throwing in the towel.

    The new thread is http://social.msdn.microsoft.com/Forums/en-US/11abc621-61ff-47c5-8cfa-5c1b1f123a25/still-struggling-with-updating-an-account

    once again Brett, thank you for you your help


    Dont ask me .. i dont know

    Monday, July 15, 2013 6:43 PM