InfoPath Dev

Hi all,

in my form I am trying to limit the number of hits the user gets when searching for products. As my product db will contain 40000+ entries, I do not want to display those in my form if the user doesn't limit the search results.

So, I went ahead and followed this guide http://support.microsoft.com/kb/826992 to enable wildcards in my search.

Then, I modified the example so that in the end, the SQL statement issued is this one:

select TOP(10) "product_id","SHORT_TEXT" from "dbo"."Products" as "Products" WHERE ("product_id" LIKE 'test%') AND ("SHORT_TEXT" LIKE 'test%')

Thsi gives me an error popup saying:

"Infopath cannot run the specified query. No query fields have been generated because the specified SQL statement is too complex for InfoPath to parse"

The SQL statement without the TOP(10) works fine:

select "product_id","SHORT_TEXT" from "dbo"."Products" as "Products" WHERE ("product_id" LIKE 'test%') AND ("SHORT_TEXT" LIKE 'test%')

How can I achieve to limit the number of hits ?

Cheers

moe 

Hi Greg,

I went ahead and created a view and bound it to my form:

CREATE VIEW [Top500Products] AS SELECT TOP(500) product_id,SHORT_TEXT,selected FROM Products

but then I thought, hey, that's not gonna give me what I want.

My user types in a SHORT_TEXT, and the query issued will look like this:

SELECT [product_id],[SHORT_TEXT],[selected]

FROM [Test].[dbo].[Top500Products]

WHERE "SHORT_TEXT" LIKE 'userinput%';

This will give me all rows which contain a shorttext that starts with "userinput" and which are in the first 500 rows of the products table, in contrast to a maximum of 500 rows which contain a shorttext that starts with "userinput" and which are in the Products table.

Could you give me more details how my view needs to look like in order to achieve the behaviour of

SELECT TOP(500) FROM Products WHERE "SHORT_TEXT" LIKE 'userinput%'

Thanks and cheers

moe

Hmm, I see.

Another possibility is to leave the default SQL command in a way it works, and then use code to update the Command property of the data adapter object. I'm not sure if that will work any differently, but it might.

I just stumbled across this article http://www.brettb.com/SQL_Help_Rowcount_Limit_Rows.asp which says I could use a SQLServer ROWCOUNT variable to do what I want.

And it works, the SQL statement I now issue via XDocument.Query() is:

SET ROWCOUNT 500; select * from Products where short_text like '00%22';

Without the set rowcount this gives me 1173 hits, now it gives me 500. 

Cheers

moestavern

Awesome! Glad you found a solution. Thanks for sharing it here!