none
Problem with string parameters and OData

    Question

  • Hi!

    I am currently developing an OData provider for our software product time cockpit. Users should be able to query data from the system using OData and PowerPivot. My problem is that PowerPivot does not accept queries that contain string parameters even if they conform with the OData standard.

    Here is an example: http://localhost:40511/CockpitDataService.svc/GetCurrencies?Code='USD'

    This OData Query works fine if I execute it in the browser. If I enter this URI in PowerPivot I get the following error message: Failed to connect to the server. Reason: Format of the initialization string does not conform to specification starting at index 0.

    If I run the query without the parameter everything works fine. If I use e.g. an integer parameter (e.g. http://localhost:40511/CockpitDataService.svc/GetCurrencies?Code=5) it does also work. Seems that I am doing something wrong just with the string parameter value.

    Kind regards,
    Rainer.


    Rainer
    Tuesday, May 24, 2011 12:26 PM

Answers

  • Hi Rainer,

    Just ran into the same problem and we finally figured it out!

    Your example does not use the correct OData query though:

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?Code='USD' should probably rewrite this to

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq 'USD'

    The problem persists however and it seems the problem are the handling of single-quotes in Power Pivot...

    If the query ends with a single-quote then it fails!

    Solution (that I've tried on Power Pivot for Excel 2010 is to either NOT end your query with a single-quote or escape the single-quotes using %27

    So I'm betting that if you do either of these 2 then you would be fine:

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq %27USD%27
    or
    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq 'USD' and Code ne null

    I hope this helps!

    Kind Regards,
    Dennis Lauritzen
    Senior Solution Architect
    ScanJour A/S

    Thursday, October 31, 2013 12:11 PM

All replies

  • Some additional information:

    1. No exception thrown by OData service implementation.
    2. In the debugger I see that Excel is executing the OData service request. The parameters are passed to the service correctly!
    3. Feed content (i.e. OData result) is equal (currently I ignore the parameter in the OData service)

     


    Rainer
    Tuesday, May 24, 2011 12:40 PM
  • Hi Rainer,

    Just ran into the same problem and we finally figured it out!

    Your example does not use the correct OData query though:

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?Code='USD' should probably rewrite this to

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq 'USD'

    The problem persists however and it seems the problem are the handling of single-quotes in Power Pivot...

    If the query ends with a single-quote then it fails!

    Solution (that I've tried on Power Pivot for Excel 2010 is to either NOT end your query with a single-quote or escape the single-quotes using %27

    So I'm betting that if you do either of these 2 then you would be fine:

    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq %27USD%27
    or
    http://localhost:40511/CockpitDataService.svc/GetCurrencies?$filter=Code eq 'USD' and Code ne null

    I hope this helps!

    Kind Regards,
    Dennis Lauritzen
    Senior Solution Architect
    ScanJour A/S

    Thursday, October 31, 2013 12:11 PM
  • Hi Dennis!

    Thank you for your reply. Unfortunately we found more reasons to drop our plans for an OData provider for Power Pivot. The main reason was the lacking of an end-user friendly OData query builder. We have decided to embed an RDLC-based reporting feature directly in our product. Excel integration is done using the ClosedXML component.

    Because of this decision I did not analyse the problem any further. However, thanks for providing a workaround. I will definitively mark it as answer.

    Regards,
    Rainer.


    Rainer

    Tuesday, November 05, 2013 9:21 AM