locked
Update account record RRS feed

  • Question

  • I am writing a SQLCLR database project , to add and update records in CRM from one of our SQL 2008 R2 servers.

    Having had a lot of help, I have worked out how to insert a new account into the accounts table, but now need to know how i can update an account from the account 'name'

    Does anybody know of any tutorials or examples that could help. I can find loads on inserting a record but not for updating onse


    Dont ask me .. i dont know

    Monday, July 8, 2013 1:28 PM

Answers

  • It's not clear if you're using the Crm Web services to modify data in a supported way, or if you're doing unsupported direct SQL updates. If you're using the Crm Web Services, then you can only update a record via it's primary key. So, if you only know the name, you'll need a RetrieveMultiple request to get the record(s) with that name, then do the update

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Pete Newman Friday, July 19, 2013 1:52 PM
    Monday, July 8, 2013 3:29 PM
    Moderator

All replies

  • It's not clear if you're using the Crm Web services to modify data in a supported way, or if you're doing unsupported direct SQL updates. If you're using the Crm Web Services, then you can only update a record via it's primary key. So, if you only know the name, you'll need a RetrieveMultiple request to get the record(s) with that name, then do the update

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by Pete Newman Friday, July 19, 2013 1:52 PM
    Monday, July 8, 2013 3:29 PM
    Moderator
  • Hi David

    I should have explained a bit more, I am using the CRM Web Services, rather than the unsupported direct SQL, and am very new to this sort of thing.

    In accounts , I think I'm right in thinking that the name field is the primary key for which we use a 6 digit number. Would i be right in thinking that I have to do a lookup on that field.

    I have been looking for an example of an update, but they are hard to find. In old sql it would be easy

    I am creating the account with ;

                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); 


    Dont ask me .. i dont know

    Monday, July 8, 2013 3:42 PM
  • The primary key is the accountid, which is a Guid.

    The CRM SDK has examples of using an Update; the difference from the linked code is that you'll need to get the accountid from a QueryExpression, with a ConditionExpression on the name


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, July 9, 2013 8:31 AM
    Moderator
  • Hi,

    I need help. been trying to get this resolved for ages, and am just going round in circles

        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void UpdateCustomer(SqlString LicenceNo, SqlString CompanyName, SqlInt32 Software, SqlInt32 SoftwareStatus)
        {
    
    
    
    
    
            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = "XXXXXXXXXX";
    
            using (CrmService crmService = new CrmService())
            {
                crmService.Url = "https://xxxxxxxxxxx:xxxx/MSCRMServices/2007/CrmService.asmx";
                crmService.Credentials = new System.Net.NetworkCredential("XXXXXX", "#XXXXXX", "XXXXX");
                crmService.CrmAuthenticationTokenValue = token;
                crmService.UnsafeAuthenticatedConnectionSharing = true;
                crmService.PreAuthenticate = true;
    
    
                ColumnSet cols = new ColumnSet();
                cols.Attributes = new string [] {"name", "account_id"};
                ConditionExpression condition = new ConditionExpression();
                condition.AttributeName = "name";
                condition.Operator = ConditionOperator.Equal;
                condition.Values = new string [] {"000002"};
                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 accounts = crmService.RetrieveMultiple(query);
    
         account updaterecord = new account();
                updaterecord.new_companyname = "UPDATED RECORED";
    
    
    
            }
        }
    Basicly what I'm trying to do is pass a name value and update the new_companyname with a value. I have tried hardcoding it in to try and get it working, but am in need of help

    Dont ask me .. i dont know


    • Edited by Pete Newman Wednesday, July 10, 2013 4:49 PM security
    Wednesday, July 10, 2013 12:15 PM