Conjunction inserts AND as well as OR

[davidt]

HI all,

Why does the following code include both an AND and an OR in the SQL?

Customers objCustomers = new Customers();

objCustomers.Query.OpenParenthesis();
objCustomers.Where.CustomerReferenceNo.Value = "%" + txtCustomerSearch + "%";
objCustomers.Where.CustomerReferenceNo.Operator = WhereParameter.Operand.Like;
objCustomers.Query.CloseParenthesis();
objCustomers.Query.AddConjunction(WhereParameter.Conj.Or);

objCustomers.Where.Title.Value = "%" + txtCustomerSearch.Text + "%";
objCustomers.Where.Title.Operator = WhereParameter.Operand.Like;
objCustomers.Query.AddConjunction(WhereParameter.Conj.Or);

etc etc

Resulting SQL:

SELECT * FROM [dbo].[Customers] WHERE ([CustomerReferenceNo] LIKE @CustomerReferenceNo ) OR AND [Title] LIKE @Title OR AND .......so on and so forth.

Thank you
David

[mgnoonan]

Hi David,

Sorry for the delay answering your question.

Advanced custom queries can be a little tricky to navigate, a problem I hope to resolve soon. There is no need to specify a conjunction between parameters, as one gets generated for you automatically (thus producing the problem you have encountered). If you look at the overload for obj.Query.Load(), you will see that one of them takes a string parameter that is the conjunction:

obj.Query.Load(); // Uses AND between parameters automatically
obj.Query.Load("OR"); // Uses OR between parameters

If you are using the same conjunction between all of your parameters, you can remove the AddConjunction calls and use one of the above lines to get the correct behavior. If your code is more complex than the snippet you provided, I'm afraid the more advanced query capabilities can be somewhat of a black art. A shortcut solution may be to just write the SQL by hand and put it in a custom Load method in your concrete class.

Or you can post more code and I will help you try to solve it. You can email me through the EasyObjects site if you need more privacy than the forum.

Just a side note, you should use StartsWith, Contains, and EndsWith instead of the Like operator. That way you don't have to build the correct wildcard structure.