InfoPath Dev

I am currently querying SQL tables/views and storing the values into numerous repeating tables on my InfoPath form. I would like to store only the first or last 5 rows for all these queries. I was able to do this with the TOP command in the SELECT statement, but it looks like that only works with SQL tables but not views. Does anyone know how to limit the repeating table itself to only display 5 rows? Or does anyone know of any SQL code I can use to limit the returned rows? Any help is appreciated. Thank you!!! 

Hello,

We can do filter those using InfoPath filter condition and using code we can remove extra nodes. If we are doing in InfoPath filter condition or code also it will impact on performance. After getting the all rows we are filtering or removing means it takes unnecessarily time being this kind of operation.

My suggestion while getting the number rows limitation is should be in database. Here is the link to create the view using top command,

http://sqlug.be/blogs/resource_blog/archive/2008/07/09/sql-server-2005-vs-2000-views-with-top-percent-order-by.aspx

Thank you for your reply Shiva. Would I be entering in this code through InfoPath or is this something I would have to set up in SQL first? I am currently constructing these queries in my InfoPath form since the queries rely on user input in two fields. The results are then stored in a repeating table. Here is an example query string that I am using:

const string queryString = "select * from [TABLE] where [FIELD1]='{0}' and [FIELD2]='{1}' order by [YEAR] DESC, [MONTH] DESC";

Hello,

Here is the change in your SQL statement,

select TOP 5 * from [TABLE] where [FIELD1]='{0}' and [FIELD2]='{1}' order by [YEAR] DESC, [MONTH] DESC";

Yes, but for some reason this does not work when I query against a view and no results are returned. However, when I use this same statement for a table, it works wonderfully. Any ideas?

Hello,

Can you try once below statement for creating the view,

Create view newView as

select TOP 5 * from [TABLE] where [FIELD1]='{0}' and [FIELD2]='{1}' order by [YEAR] DESC, [MONTH] DESC";

Here is the query string. However, i'm not sure how to integrate this new query string with my code to store the retrieved values into the nodes.

const string queryString6 = "Create view newView as select TOP 5 * from [TABLE] where [FIELD1]='{0}' order by [YEAR] DESC, [MONTH] DESC";

if (!string.IsNullOrEmpty(field1))

{

AdoQueryConnection dc = (AdoQueryConnection)DataConnections["TABLE"];

dc.Command = string.Format(queryString6, uninum);

dc.Execute();

string[] columnNames = {"COL1","COL2","COL3","COL4"};

string myNamespace = NamespaceManager.LookupNamespace("my");

string dNamespace = NamespaceManager.LookupNamespace("d");

string blankNamespace = NamespaceManager.LookupNamespace("");

XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/my:queryResults1", NamespaceManager);

XPathNavigator queryDataFields = DataSources["TABLE"].CreateNavigator();

queryDataFields = queryDataFields.SelectSingleNode("/dfs:myFields/dfs:dataFields", NamespaceManager);while (dataFields.MoveToChild("resultsRow1", myNamespace))

{

dataFields.DeleteSelf();

}

foreach (XPathNavigator resultsRow1 in queryDataFields.SelectChildren("TABLE", dNamespace))

{

using (XmlWriter writer = dataFields.AppendChild())

{

writer.WriteStartElement("resultsRow1", myNamespace);foreach (string colName in columnNames)

{

writer.WriteStartElement(colName, myNamespace);

writer.WriteValue(resultsRow1.GetAttribute(colName, blankNamespace));

writer.WriteEndElement();

 

 

}

writer.WriteEndElement();

}

}

}

Hello,

First you have to execute that view (newView) in your database. Then you have to write the statement to get the data from view as below,

const string queryString6 = "select * from newView";

Rest of the code no change.

Hello Shiva, can you think of any other work arounds to just return the first five results? Unfortunately, I am not the dba so I do not have permissions to create new views. Thank you for your help. I really appreciate it!

Hello,

If you do not have permission to create any view you can write the below statement to give top 5 rows,

const string queryString6 = "select TOP 5 * from [TABLE] where [FIELD1]='{0}' order by [YEAR] DESC, [MONTH] DESC";

Shiva, unfortunately I am restricted to using these pre-built views and the TOP command does not work with the views (no results are returned). Is there anything else you can think of? Thank you!

Actually, I got curious and just tested this -- I tried using TOP to query a view in SQL Server Management Studio and it returned the 5 rows, as specified. Then I tried this as a SQL receive data connection in an IP 2007 form (editing the SQL command) and again had 5 rows returned. Could something else be preventing you from seeing the data you expect?

That certainly throws a kink into things. When I insert the TOP expression, nothing is returned from the view. When I remove the TOP expression. values are returned. All I am doing is modifying the SQL statement. From this

 const string queryString2 = "select * from [VIEW] where [FIELD1]='{0}' order by [YEAR] DESC ,[MONTH] DESC";

to this:

const string queryString2 = "select TOP 5 * from [VIEW] where [FIELD1]='{0}' order by [YEAR] DESC ,[MONTH] DESC";

Well, mind you, I was just trying this by editing the SQL in a data connection in the data connection wizard... and then again in a direct query. Can't imagine why it would work differently in code, though. I'm assuming when you test the two different query strings, you are using the same parameters for {0}, year & month.... :)

I think I've resolved this issue over private message with Geoffrey.  The issue was that results were coming back from the database, but the code to copy them to the main data source wasn't finding them.  It looks like this line was the problem:

foreach (XPathNavigator resultsRow1 in queryDataFields.SelectChildren("TABLE", dNamespace))  

Oddly enough, if you query an SQL view from InfoPath, the nodes of the result set will have the name of the view, but if you use "TOP X" in the query, the nodes are named after one of the tables in the view.  Changing that line to the following should work for just about any situation:

foreach (XPathNavigator resultsRow1 in queryDataFields.Select("*"))