Blogger

  • This email address is being protected from spambots. You need JavaScript enabled to view it.

    Recent items

Login

Today I'm working with a particular error: when I search items that contains the single quote character through the REST API, I receive a bad request error (400 error).


The first steps is to analyze the response from the server:

  • Request: GET http://test.lucacostante.com/_api/search/query?querytext='path:http://test.lucacostante.com/Lists/Banners/Forms/AllItems.aspx OR path:http://test.lucacostante.com/Pages/L'universo.aspx'&rowlimit=5&selectproperties='Title,Path'
  • Response: Error 400: {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"'path:http://test.lucacostante.com/Lists/Banners/Forms/AllItems.aspx OR path:http://test.lucacostante.com/Pages/L'universo.aspx'\" is not valid."}}}

The problem is the single quote ' character into the query that provide to create a wrong query.

I've tried to encode the URL but this not resolve my problem. The solution is simpler: I need only to add another single quote to resolve my problem ('').

  • Request: GET http://test.lucacostante.com/_api/search/query?querytext='path:http://test.lucacostante.com/Lists/Banners/Forms/AllItems.aspx OR path:http://test.lucacostante.com/Pages/L''universo.aspx'&rowlimit=5&selectproperties='Title,Path'
  • Response: HTTP/1.1 200 OK
Published in SharePoint
Tuesday, 04 March 2014 22:46

How to use and configure large lists

Sometime,

we need to store into lists a lot of elements. All developers that know SharePoint knows that in the OOB views there is the limit of 5000 items.
I want suggest these recommendations to create a definition for a list that have a lot of elements:

  • allow the folders and create an event receiver in order to store the items into the folders (maximum 5000 items for each folder)
  • enable one o more indexed columns (not on the ID field and the best fields are text fields). Each column that will be used for where condition or $filter condition need to enabled as indexed column
  • if you are quering on lookup column, you must use this

<FieldRef Name='ColumnName' LookupId='TRUE' /><Value Type='Integer'   >LOOKUP_ITEM_ID</Value>

In the case that you have already the list and you have exceeded the threshold, these are the steps for the work around:

  • from the central administration, modify the threshold value up to the current list count on the current web application
  • add the indexed column
  • create a view that contains less of 5000 items (for example with where condition on created date)
  • from the central administration, reset the threshold value to 5000 on the current web application

In the case that you want to use the REST API on the list, remember that all fields that you need to apply $filter condition, need to defined as indexed column, otherwise you'll have the error "Internal server error".


See these links for more details

Published in SharePoint

In SharePoint 2010, there is a WCF service utilizing ADO.NET Data Service called ListData.svc fulfilling this goal.  You can submit the query by providing the URL containing various commands like sorting, filtering, positioning....etc. 

List all objects of the site
http:/<sp>/_vti_bin/ListData.svc

List all items of the list
http://<sp>/_vti_bin/ListData.svc/ListName

List specific item of the list
http://<sp>/_vti_bin/ListData.svc/ListName(<index>)

List specific item's field value of the list
http://<sp>/_vti_bin/ListData.svc/ListName(<index>)/(<field>)

Filtering
http://<sp>/_vti_bin/ListData.svc/ListName?$filter=<field> <operator> <value>

Lookup field query
http://%3csp%3e/_vti_bin/ListData.svc/ListName?$expand=<LookupList>&$filter=<LookupList>/<LookupField> <operator> <value>

Range (example: getting from 20th to 25th products)
http://<sp>/_vti_bin/Products?$skip20&$top=5


Check MSDN Reference Using Microsoft ADO.NET Data Services for all query options(expand, orderyby, skip, top, filter), operators (eq, ne, gt, ge, lt, le, and, or, not.....), and functions.

In my case, I've used ListData.svc to retrive items from a list. It works fine when I used as filter a field with one word as display name but when I used a field with two or more words as display name I receive this error 400 Bad Request and with Fiddler I inspect the request and view the error:

Show/Hidden xml code

View source
 
 
 
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <code/>
    <message xml:lang="it-IT">Syntax error at position 10.</message>
</error>
 
 
 

This is the request: "http://test.virtualsp.com/_vti_bin/ListData.svc/Omologations?$filter=startswith(Model Year,'mo')&$select=Model Year,Id".

Trying to resolve the problem, I've tested:

  • Use the single quote for the display name "http://test.virtualsp.com/_vti_bin/ListData.svc/Omologations?$filter=startswith('Model Year','mo')&$select='Model Year',Id": DOES NOT RESOLVE THE PROBLEM
  • Use the double quote for the display name "http://test.virtualsp.com/_vti_bin/ListData.svc/Omologations?$filter=startswith("Model Year",'mo')&$select="Model Year",Id": DOES NOT RESOLVE THE PROBLEM
  • Use the internal name of the field "http://test.virtualsp.com/_vti_bin/ListData.svc/Omologations?$filter=startswith(OMG_ModelYear,'mo')&$select=OMG_ModelYear,Id": DOES NOT RESOLVE THE PROBLEM
  • Use the display name removing the space from words "http://test.virtualsp.com/_vti_bin/ListData.svc/Omologations?$filter=startswith(ModelYear,'mo')&$select=ModelYear,Id": THIS RESOLVES THE PROBLEM

So, when you want use the ListData.svc and your fields display name has spaces, REMEMBER to remove it into the request and see this beautiful page.

Published in SharePoint