Thursday, 31 March 2016

Generic SQL Error – RetriveMultiple ConditionOperator Equal Like and Contains



Today someone "me" wanted to change the requirements of the wrap I have over the CRM web services so I could reuse the method more often, but to do so I need to transform the method more standard. So I changed the ConditionOperator.Equal to ConditionOperator.Contains. But was giving me an error "Generic SQL Error" while using in the ConditionExpression. The requirement was to get all the records from an entity that contains a specific field with a certain attribute value.

Before, using Equal:
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, AttributeValue);

After, using Contains: You need to add % to the attributevalue but don’t work if the table or indexed view is not full-text indexed, therefore you need to use Like.
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Contains, “%”+AttributeValue+“%”);

After, using Like: You need to add % to the attributevalue
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), 
Microsoft.Xrm.Sdk.Query.ConditionOperator.Like, “%”+AttributeValue+“%”);

1 comment:

  1. I was pretty puzzled when I got the same Generic SQL Error, but changing the operator to like nad adding wildcards did the trick. Thanks!

    ReplyDelete