InfoPath Dev

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:

string querySeed = queryField.Value;

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

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

//get the original command

string origCommand = newQuery.Command.ToString();

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

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

//run the query using new command

//put everything back

//set the query field back

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

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

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 + "'))";  

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.

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 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!