Code

Re: Ignore Blanks in Query

BuildPro Guru — Wed, 10 Jun 2009 16:51:41 GMT

Well, you got me on the right track and in the process i realized a few mistakes that I had made. Here is my final code and it works like a charm:

string wwAlphMatch1 = string.IsNullOrEmpty(querySeed) ? "" : "([WWALPH] like '" + querySeed + "%' )and ";

string wwAlphMatch2 = string.IsNullOrEmpty(querySeedAddy) ? "" : "([szLotAddress_201] like '" + querySeedAddy + "%' )and ";

string customQuery = " where " + wwAlphMatch1 + " (BuyerNumber in (Select BuyerNumber from Lots where " + wwAlphMatch2 + " [Div_Desc] = '" + querySeedDiv + "'))";

Thanks so much for pointing me in the right direction!

Re: Ignore Blanks in Query

BuildPro Guru — Mon, 08 Jun 2009 09:35:58 GMT

Hello Jimmy.

Actually, this is deployed for 9 different divisions that are in the same DB and I am using the div_desc to filter the results. I think you are right about where the problem exists. I will give your bit a whirl and let you know how it works out.

Re: Ignore Blanks in Query

Jimmy — Mon, 08 Jun 2009 04:50:24 GMT

Are you sure it's returning all the rows, and not just all the ones that match the Div_Desc value?

It looks like the problem is this part:

([WWALPH] like '" + querySeed + "%')

When querySeed is blank, this will evaluate to:

([WWALPH] like '%')

which will match every row in the table. You might want to try something like this:

string wwAlphMatch = string.IsNullOrEmpty(querySeed) ? " = ''" : "like '" + querySeed + "%'";

string customQuery = " where ([WWALPH] " + wwAlphMatch + ") and (BuyerNumber in (Select BuyerNumber from Lots where [Div_Desc] = '" + querySeedDiv + "'))";

Re: Ignore Blanks in Query

BuildPro Guru — Mon, 08 Jun 2009 00:27:17 GMT

Hello Hilary. Yep. It works teriffic as long as I dont leave the WWALPH field blank.

Re: Ignore Blanks in Query

Hilary Stoupa — Sat, 06 Jun 2009 06:33:51 GMT

Have you run your code in debug to verify that newQuery.Command is exactly what you'd expect it to be?

Ignore Blanks in Query

BuildPro Guru — Wed, 03 Jun 2009 18:27:21 GMT

Hello Everyone.

IP 2007 + C#. I have a form based on a SQL DB and I am using query fields from two tables - the parent table and one other table. If any of the query fields from the parent table are blank, the query will return ALL of the data in that table. So I will have 20k+ records returned, but only one of them will have the rest of the information. How do I get it to ignore the blank fields in the parent table? Here is the bit of code that executes the query:

//get the value of the buyer and division fields

XPathNavigator queryField = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Buyers/@WWALPH", NamespaceManager);

string querySeed = queryField.Value;

XPathNavigator queryFieldDiv = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:queryFields/q:Lots/@div_desc", NamespaceManager);string querySeedDiv = queryFieldDiv.Value;

//clear the query fields so they are ignored in the query

queryField.SetValue("");queryFieldDiv.SetValue("");

//create custom where clauses (one for buyer and one for divsion)

string customQuery = " where ([WWALPH] like '" + querySeed + "%') and (BuyerNumber in (Select BuyerNumber from Lots where [Div_Desc] = '" + querySeedDiv + "'))";

//get the original command

AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;

string origCommand = newQuery.Command.ToString();

//Locate the end of the buyers shape in the command and add the buyers where clause

int firstBrace = origCommand.IndexOf("}");

//Insert the where clause at the end of the buyers shape

string origCommandFirstHalf = origCommand.Substring(0, firstBrace);string origCommandSecondHalf = origCommand.Substring(firstBrace, origCommand.Length - origCommandFirstHalf.Length);

newQuery.Command = origCommandFirstHalf + customQuery + origCommandSecondHalf;

//run the query using new command

newQuery.Execute();

//put everything back

newQuery.Command = origCommand;

//set the query field back

queryField.SetValue(querySeed);

queryFieldDiv.SetValue(querySeedDiv);