Data Connections

Re: Limit number of rows returned from data connection?

geoffc — Mon, 05 Jan 2009 12:24:29 GMT

Jimmy, I just changed the code and everything is now working the way I want. Thank you once again for your excellent assistance.

Re: Limit number of rows returned from data connection?

Jimmy — Thu, 01 Jan 2009 16:45:51 GMT

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("*"))

Re: Limit number of rows returned from data connection?

Hilary Stoupa — Wed, 31 Dec 2008 18:01:32 GMT

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.... :)

Re: Limit number of rows returned from data connection?

geoffc — Wed, 31 Dec 2008 16:59:24 GMT

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";

Re: Limit number of rows returned from data connection?

Hilary Stoupa — Wed, 31 Dec 2008 16:11:21 GMT

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?

Re: Limit number of rows returned from data connection?

geoffc — Wed, 31 Dec 2008 16:00:04 GMT

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!

Re: Limit number of rows returned from data connection?

Shiva — Wed, 31 Dec 2008 12:47:04 GMT

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";

Re: Limit number of rows returned from data connection?

geoffc — Wed, 31 Dec 2008 12:31:07 GMT

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!

Re: Limit number of rows returned from data connection?

Shiva — Wed, 31 Dec 2008 11:55:16 GMT

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.

Re: Limit number of rows returned from data connection?

geoffc — Tue, 30 Dec 2008 13:47:28 GMT

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();

}

Re: Limit number of rows returned from data connection?

Shiva — Tue, 30 Dec 2008 13:05:39 GMT

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";

Re: Limit number of rows returned from data connection?

geoffc — Tue, 30 Dec 2008 12:17:31 GMT

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?

Re: Limit number of rows returned from data connection?

Shiva — Tue, 30 Dec 2008 06:41:03 GMT

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";

Re: Limit number of rows returned from data connection?

geoffc — Mon, 29 Dec 2008 12:58:47 GMT

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";

Re: Limit number of rows returned from data connection?

Shiva — Mon, 29 Dec 2008 06:45:47 GMT

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