Executing queries in CRM. RRS feed

  • Question



    How can i execute a query in the CRM 4.0.

    My requirement is,

    I've an entity named "Projects" having a field named "serial no"(int)- say,1234 .this filed is readonly.not the primary key.

    When a user save a new record,i want to generate a new serial no for the newly saved record which should be = (current maximum value of serial no ) +1.

    So can i execute a query to find the current maximum value of the serial no.so that i could increment it by 1 and can set to the new serial no.

    Waiting for a reply.

    Wednesday, July 9, 2008 10:45 AM


All replies

  • Wednesday, July 9, 2008 10:50 AM
  • I agreed with Joel.  You can achieve this by implementing the PostCreate plugin. You can take a loo at this site for information on how to implement this: http://blogs.msdn.com/crm/archive/2008/05/13/auto-numbers-in-microsoft-dynamics-crm.aspx



    Wednesday, July 9, 2008 1:14 PM
  • Ok.I'll check this link.But my actual requirement is to execute query at the LOAD EVENT of the opportunity entity-details page.

    my query is " SELECT MAX(new_code) FROM dbo.OpportunityExtensionBase where new_year="1"


    new_code is an integer field..


    If this method is not fair......Any other alternatives??

    Waiting for reply.Urgent please....

    Thursday, July 10, 2008 4:32 AM
  • If this is what you want to do, you should create a custom web service or webform, accessing the database and return the result in an xml form (e.g. <max_value>23</max_value>)


    in your load_event, write javascript to access the webform or webservice and get the max_value return value.  For detail on how to write javascript for this, go to http://www.captain.at/howto-ajax-form-post-get.php.



    Thursday, July 10, 2008 2:09 PM
  • can i execute this query in a Plugin?

    Friday, July 11, 2008 4:41 AM

    Yes you can.


    Run your query within the plugin, then update the projects entity with the returned ID.


    I suggest you register the plugin to run as a Synchronous process to ensure that no other Project is allocated the same ID.


    Hope that Helps,


    Catherine Eibner

    Microsoft Certified Business Management Solutions Professional - Applications for Microsoft Dynamics CRM

    Friday, July 11, 2008 6:18 AM
  • I've created a plugin to execute that query.But it didnt work!

    I've added a step to this plugin as pre-create-opportunity entity.Help me plz.

    My plugin code is,


    Private Const strConn = "Data Source=TEST911;Initial Catalog=zenitinformatica_MSCRM;Integrated Security=True"

    Public Function getIDmax(ByVal yearval As Integer) As Integer

    Dim i As Integer

    Dim _DTtable As New DataTable

    Dim oConn As New SqlClient.SqlConnection(strConn)


    Dim oCommand As New SqlClient.SqlCommand("SELECT MAX(new_code) FROM dbo.OpportunityExtensionBase WHERE new_year='" + yearval.ToString.Trim + "'", oConn)

    i = oCommand.ExecuteScalar()


    Return i

    End Function


    Public Sub Execute(ByVal context As Microsoft.Crm.Sdk.IPluginExecutionContext) Implements Microsoft.Crm.Sdk.IPlugin.Execute

    Dim newcode As Integer = 1000000

    Dim maxid As Integer

    Dim selectedIndex As Integer

    If context.PrimaryEntityName = EntityName.opportunity.ToString() Then


    If context.InputParameters.Properties.Contains(ParameterName.Target) AndAlso TypeOf context.InputParameters.Properties(ParameterName.Target) Is DynamicEntity Then

        Dim entity As DynamicEntity = TryCast(context.InputParameters.Properties(ParameterName.Target), DynamicEntity)

      If entity.Properties.Contains("new_year") Then

         selectedIndex = DirectCast(entity.Properties("new_year"), Picklist).Value

         maxid = getIDmax(selectedIndex)

        If maxid = 0 Then

         newcode = 1000000


         newcode = maxid + 1

       End If

       'Here i want to save the newly generated code

       entity.Properties("new_code") = newcode

       Dim service As ICrmService = context.CreateCrmService(True)


     End If

    End If

    End If

    End Sub
    Monday, July 14, 2008 10:39 AM