SalesTable record via QueryService

This question is not answered

Hi,

I am attempting to retrieve a SalesOrder record in the SalesTable via the QueryService.

I am able to use ExecuteStaticQuery to return all the rows in the table via the SalesTableSelect query in AOT, but I cannot retrieve a single row using ExecuteQuery method.

Here is my C# code so far:

 

QueryServiceClient qsc = new QueryServiceClient();

Paging paging = null;

QueryMetadata query = new QueryMetadata();

QueryDataSourceMetadata salesOrderDataSource = new QueryDataSourceMetadata();

DataSet result;

 

query.Name = "SalesTableQuery";

query.DataSources = new QueryDataSourceMetadata[1];

salesOrderDataSource.Name = "SalesTable DataSource";

salesOrderDataSource.Enabled = true;

salesOrderDataSource.Table = "SalesTable";

salesOrderDataSource.DynamicFieldList = true;

salesOrderDataSource.DynamicFieldListSpecified = true;

query.DataSources[0] = salesOrderDataSource;

 

query.DataSources[0].Ranges = new QueryRangeMetadata[1];

QueryDataRangeMetadata range = new QueryDataRangeMetadata();

range.FieldName = "SalesId";

range.TableName = "SalesTable";

range.Enabled = true;

range.Value = mySalesId;

query.DataSources[0].Ranges[0] = range; 

                            

result = qsc.ExecuteQuery(query, ref paging);

Error:  Cannot find table 0

 

I know the SalesTable exists and I know the record with my SalesId exists.

Thank you for your help.

T. Brooks

All Replies
  • Hi Trevor :-)

    Can you try remove the salesOrderDataSource.DynamicFieldListSpecified = true

  • Hi Joris!

    I did try that but it still returns the error: Cannot find table 0.   :-(

  • FYI If I take the range off it still doesn't return anything.

  • Your code (copy&paste) works for me (AX2012 R2). Could you provide more details about the exception (type, call stack)?

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Hi Martin!

    We are on AX 2012 CU3.

    Stack trace:

      at System.Data.DataTableCollection.get_Item(Int32 index)

      at CLGSalesOrderWeb.SalesOrderPage.Page_Load(Object sender, EventArgs e) in C:\Users\tbrooks\documents\visual studio 2010\Projects\CLGSalesOrderWeb\CLGSalesOrderWeb\SalesOrderPage.aspx.cs:line 336

    Type:

    System.IndexOutOfRangeException

    The error occurs after the call to ExecuteQuery, when attempting to iterate the resulting dataset with this code:

                                       for (int ix = 0; ix < dataset.Tables[0].Rows.Count; ix++)

                                       {

                                           DataRow datarow = dataset.Tables[0].Rows[ix];

                                       }

    I also see this in the exception:

    _COMPlusExceptionCode = -532462766

    Thank you Martin!

  • Is there a way to see what actually executed in AX?  I'm pretty sure the query is making it to AX, it's just not returning anything.

  • Hi Trevor,

    I didn't realize your execute actually returned, I thought that threw the error. I know what the issue is, you're not specifying your company so the service will take the default company of your user's profile. Inside AX, in your user options, what is your default company? I bet it's DAT or some other company with no data, which is why it's not finding any records.

  • I also understood it threw the exception somewhere in the code posted in the question, not that it returns valid but empty DataSet. I agree with Joris that wrong data area is the most common source of the problem.

    You could use Event Tracing to see what's hapenning inside AX.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Hi,

    I did look at my user info and I am set to the proper company, however, I would like to set up Event Tracing to see exactly what's happening on the AX side. (Not sure how to do that.)

    I also added these lines:

                                   query.AllowCrossCompany = true;

                                   query.AllowCrossCompanySpecified = true;

    But to no avail, still doesn't return any rows.

    Please help troubleshoot further, I know we are almost there.

    Regards,

    T. Brooks

  • AX supports Event Tracing for Windows (ETW) and allows you to trace many events, including those about X++ and SQL calls. Find details in Windows Event Tracing in Microsoft Dynamics AX 2012. Trace Parser may help you understanding the trace data. I've never tried to trace query service, so I can't tell you exactly what you'll get.

    Another option is to trace SQL commands using SQL Server Profiler.

    Martin "Goshoom" Dráb | Freelancer | Goshoom.NET Dev Blog

  • Hi,

    Thank you for the information Martin.

    With the help of Joris and friends I was able to pinpoint the issue to a web service conflict, and I am now receiving results from the QueryService!  That's great!

    The second part of the problem I'm having is posting a SalesOrder via the FormletterService.

    I believe there is a conflict between the two service references SalesOrderService and FormletterService, but I'm not sure.

    I separated the services into separate namespaces:

    using CLGQueryService = CLGSalesOrderWeb.DAXQueryServiceReference;

    using CLGInvoice = CLGSalesOrderWeb.DAXSalesOrderInvoiceService;  //FormletterService

    using CLGSalesOrder = CLGSalesOrderWeb.DAXSalesOrderServiceTest;   //SalesSalesOrderService

    But I still get an error that seems to be reference related:

    Server stack trace:

      at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)

      at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)

      at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)

      at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)

      at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:

      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

      at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

      at CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterService.postSalesOrderInvoice(FormletterServicePostSalesOrderInvoiceRequest request)

      at CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterServiceClient.CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterService.postSalesOrderInvoice(FormletterServicePostSalesOrderInvoiceRequest request) in C:\Users\tbrooks\documents\visual studio 2010\Projects\CLGSalesOrderWeb\CLGSalesOrderWeb\Service References\DAXSalesOrderInvoiceService\Reference.cs:line 1811

      at CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterServiceClient.postSalesOrderInvoice(CallContext CallContext, SalesFormLetterInvoiceContract _contract) in C:\Users\tbrooks\documents\visual studio 2010\Projects\CLGSalesOrderWeb\CLGSalesOrderWeb\Service References\DAXSalesOrderInvoiceService\Reference.cs:line 1818

      at CLGSalesOrderWeb.SalesOrderPage.Page_Load(Object sender, EventArgs e) in C:\Users\tbrooks\documents\visual studio 2010\Projects\CLGSalesOrderWeb\CLGSalesOrderWeb\SalesOrderPage.aspx.cs:line 413

    This error points to this area in Reference.cs:

           public CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterOutputContract postSalesOrderInvoice(CLGSalesOrderWeb.DAXSalesOrderInvoiceService.CallContext CallContext, CLGSalesOrderWeb.DAXSalesOrderInvoiceService.SalesFormLetterInvoiceContract _contract) {

               CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterServicePostSalesOrderInvoiceRequest inValue = new CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterServicePostSalesOrderInvoiceRequest();

               inValue.CallContext = CallContext;

               inValue._contract = _contract;

               CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterServicePostSalesOrderInvoiceResponse retVal = ((CLGSalesOrderWeb.DAXSalesOrderInvoiceService.FormletterService)(this)).postSalesOrderInvoice(inValue); //---------------Line 1818

               return retVal.response;

           }

    Which is interesting because in the FormletterService logs I can see that an XML message is being received by the server, but I would expect to see the response in there too but there wasn’t one.

    Any ideas how to solve this issue?

  • One other note, when I isolate the FormletterService in its own project with no other service references, it works.  How do I get the SalesSalesOrderService reference and the FormletterService to play nicely together?

  • Sorry for all of the posts but in my attempt to make all of this work I have new information.

    When I comment out the parmSalesTable parameter of the SalesFormLetterInvoiceContract, the call to postSalesOrderInvoice appears to return a response with no error.

    When I comment back in the parameter, I get the error Length cannot be less than zero.

    Parameter name: length

    Therefore I do not believe it is a web service reference problem, but perhaps an incomplete SalesOrder dataset...I'm really not sure.

    Regards,

    T. Brooks

  • I've been meaning to blog about this, but if you add one reference to a service you can then go into your project's folder on disk and locate the .svcmap file. You can add more service references manually in the file, then in Visual Studio update the reference. This will re-use the same proxies, in the same namespace, between the services without conflict.

    I'm not convinced that that is your issue though. Your code wouldn't compile if you had any sort of conflict on that.

  • It does seem as if the DataSet returned from the QueryService is not in the proper format as a parameter to SalesFormLetterInvoiceContract.