General

Re: Query a Secondary Data Source

Matt Faus — Thu, 12 Jan 2006 01:46:24 GMT

To get the count of the number of records returned you could do this (substitute obj for XDocument if necessary):

var recordsReturned = XDocument.DOM.selectNodes( "//dfs:myFields/dfs:dataFields/d:VendorList" );
var numberOfRecords = recordsReturned.length;

For an easier way to get at the fields there are a couple shortcuts. Check out this tutorial for more effecient XPath:http://www.w3schools.com/xpath/default.asp

Or you could use variables for commonly used XPath, like this:

var XP_vendlist = "/dfs:myFields/dfs:dataFields/d:VendorList/";
var node1 = obj.DOM.selectSingleNode( XP_vendlist + "@name" );

Hope this helps, let us know if you have any more questions!

---------------
Matt Faus
InfoPath Dev Texas

Re: Query a Secondary Data Source

Lewis68 — Wed, 11 Jan 2006 22:07:19 GMT

Ok, I believe I got it working. Thanks.

Two more questions:

How do I get a count of how many records are returned?

Also, is there an easier way to get at the fields then this? Maybe through the adoAdapter object? If not that's ok. I was just wondering.

var node1 = obj.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorList/@name");
var node2 = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorSelect/@name");
node2.text = node1.text;

Re: Query a Secondary Data Source

Agni Jonnalagadda — Wed, 11 Jan 2006 21:16:11 GMT

When you take a secondary dataconnection to DB you won't have queryFields. You will just have dataFields. This node will contain the rows returned by your DB.

Starting from your redline to "obj.Query();" should be replaced with the following lines of code

var adoAdapter = thisXDocument.DataObjects["asdd"].QueryAdapter;
//Below you should have your sql query
adoAdapter.Command = "select * from VendorList where number = " + r;
adoAdapter.Query();

Agni
InfoPath Dev India

Re: Query a Secondary Data Source

Lewis68 — Wed, 11 Jan 2006 18:32:04 GMT

Well I thought I had it working.

It only returns the first record in the table, regardless of the Vendor Number I use to query.

Also, when I add the Second Data Source(VendorList) to the form, and it asks if you want to Submit, or Retrieve data, I specify Retrieve. But in the Data Sources view the second data source structure is "myFields/dataFields/d:VendorList". Shouldn't it be "myFields/queryFields/q:VendorList"?

I tried the red line in place of the green, but then I get a (Null) error on the node line.

I'm stumped. Any idea's?

Below is the code.

var node = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorSelect/@vendor");
var r = node.text;

var obj = XDocument.DataObjects("VendorList");
obj.DOM.setProperty("SelectionNamespaces", 'xmlns:q="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-12-26T21:32:32"');

var node = obj.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:VendorList/@number");
var node = obj.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorList/@number");
node.text = r;

obj.Query();

var node = obj.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorList/@name");
var r = node.text;

var node = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorSelect/@name");
node.text = r;

Re: Query a Secondary Data Source

Lewis68 — Wed, 11 Jan 2006 17:46:13 GMT

Hi Agni,

I got it working. Thanks for your help.

Not exactly intuitive though. Am I the only one having problems with these simple tasks? I don't think I had this much trouble when I started in the industry 15 years ago programming in C/C++.

Lewis

Re: Query a Secondary Data Source

Agni Jonnalagadda — Wed, 11 Jan 2006 09:31:02 GMT

Hi Lewis,
You need to do setProperty of namespaces on your obj.DOM. Namespace is something which is prefixed before the node.
For ex:
obj.DOM.setProperty("SelectionNamespaces", "xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\" xmlns:d=\"http://tempuri.org/\"");

Agni
InfoPath Dev India

Re: Query a Secondary Data Source

Lewis68 — Wed, 11 Jan 2006 08:12:12 GMT

Hi Patrick,

Can you give me a link that talks about this?

I'm sure this has been talked about before, but I havn't been able to find it.

Thanks.

Re: Query a Secondary Data Source

Patrick Halstead — Wed, 11 Jan 2006 03:47:22 GMT

Hi Lewis,
This is an often discussed issue. Search the forum. You need to add the namespace first.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Query a Secondary Data Source

Lewis68 — Tue, 10 Jan 2006 23:48:59 GMT

I have a Secondary Data Source (VendorList) that I want to query and return a record with some fields, but I'm having trouble accessing the fields.

I need a lookup field based on the Vendor Number. When they leave the field, I want it to lookup the 2nd data source for the Name, Address etc. and populate fields in the main data source.

Below is the source in the OnAfterChange Event of the Vendor Number.
But it errors on the red line saying "Reference to undeclared namespace prefix: dfs". I've tried every XPath I can think of, but I still can't access it. If I remove the dfs prefixes, I get null object errors.

Any idea's what I'm doing wrong? Also, is there an easy way to figure out what the XPath should be for fields?

Thanks.

var node = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorSelect/@vendor");
var r = node.text;

var obj = XDocument.DataObjects("VendorList");
var node = obj.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorList/@number");
node.text = r;

obj.DOM.Query();

var node = obj.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorList/@name");
var r = node.text;

var node = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:dataFields/d:VendorSelect/@name
node.text = r;