locked
Update query in CRM on on save event RRS feed

  • Question

  • Is it possible to put a sql update query in the customizations?
    In the onsave of opportunityproduct .
    Monday, November 2, 2009 11:17 AM

Answers

  • But the opportunityproductid i cannot get off my form.
    How can i get the opportuinityproductid? 


    Your original question: "...In the onsave of opportunityproduct ...."

    It's the current record -- crmForm.ObjectId!

    Not only can you get it, it IS it.

    Thursday, November 5, 2009 4:12 PM

All replies

  • Hi,

    You could call CrmService update method or could also call a custom web service which would update the required fields\table,  on the onSave event using Ajax.

    Regards,
    Nishant Rana

    http://nishantrana.wordpress.com
    Monday, November 2, 2009 11:26 AM
  • Hi, Huub.

    This is unsupported customization but check following url:
    http://billoncrmtech.blogspot.com/2008/07/client-side-scripting-more-javascript_21.html

    I suggest you to use supported ways (webServices with JavaScript or Plugins).

    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    • Proposed as answer by mardukes Monday, November 2, 2009 8:14 PM
    Monday, November 2, 2009 11:41 AM
    Moderator
  • SQL injection.  You should NEVER put SQL ANYWHERE outside of the Management Studio or a stored procedure.  And you are not permitted to write stored procedures on the MSCRM data base.  You should use the CrmService.Update method.

    Monday, November 2, 2009 8:11 PM
  • Do you know where i can find the CrmService.Update method?
    Tuesday, November 3, 2009 7:16 AM
  • Check following urls:

    CrmService Update method .
    Update method using JavaScript .
    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Tuesday, November 3, 2009 7:20 AM
    Moderator
  • I get the error message: Unexpected error occured

    i used this method:

    var Nieuwbedrag = crmForm.all.priceperunit.DataValue;

    var Identificatie = crmForm.all.new_wijzigingsnummer.DataValue;

    var authenticationHeader = GenerateAuthenticationHeader();

     

    // Prepare the SOAP message.

    var xml = "<?xml version='1.0' encoding='utf-8'?>"+

    "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+

    " xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+

    " xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+

    authenticationHeader+

    "<soap:Body>"+

    "<Update xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+

    "<entity xsi:type='OpportunityProduct'>"+

    "<priceperunit>"+Nieuwbedrag+"</priceperunit>"+

    "<new_wijzigingsnummer>"+Identificatie+"</new_wijzigingsnummer>"+

    "</entity>"+

    "</Update>"+

    "</soap:Body>"+

    "</soap:Envelope>";

    // Prepare the xmlHttpObject and send the request.

    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");

    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);

    xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Update");

    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

    xHReq.setRequestHeader("Content-Length", xml.length);

    xHReq.send(xml);

    // Capture the result

    var resultXml = xHReq.responseXML;

     

    // Check for errors.

    var errorCount = resultXml.selectNodes('//error').length;

    if (errorCount != 0)

    {

     var msg = resultXml.selectSingleNode('//description').nodeTypedValue;

     alert(msg);

    }

    // Display a confirmation message.

    else

    {

    alert("Wijziging met id = "+Identificatie+" successvol update.");

    }

    Tuesday, November 3, 2009 10:14 AM
  • Hi.

    You have to fill opportunityproductid with correct identifier of opportunity product to make update.
    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Tuesday, November 3, 2009 10:16 AM
    Moderator
  • Sorry I don't get it
    Tuesday, November 3, 2009 11:02 AM
  • from the link Andriy provided you forgot this part: "<contactid>"+contactId+"</contactid>"+

    In your case you want <opportuinityproductid> instead of <contactid> because you have to identify which record you are updating.  You supplied two pieces of update data but not the record identity.  Yes the type is opportunityproduct (like tablename) but which record (like WHERE ID=...)

    CrmService.Update is talking to the database not your web form.

    Tuesday, November 3, 2009 8:55 PM
  • But the opportunityproductid i cannot get off my form.
    How can i get the opportuinityproductid? 
    Wednesday, November 4, 2009 7:50 AM
  • But the opportunityproductid i cannot get off my form.
    How can i get the opportuinityproductid? 

    Hi, Huub.

    You can retrieve child opportunity products and identifiers (opportunityproductid) using RetrieveMultiple or Fetch .

    Truth is opened the prepared mind My blog - http://a33ik.blogspot.com
    Wednesday, November 4, 2009 8:02 AM
    Moderator
  • But the opportunityproductid i cannot get off my form.
    How can i get the opportuinityproductid? 


    Your original question: "...In the onsave of opportunityproduct ...."

    It's the current record -- crmForm.ObjectId!

    Not only can you get it, it IS it.

    Thursday, November 5, 2009 4:12 PM