Secondary data source query

[ssaral]

sorry about that...that's even cooler

Actually, it's woman. Glad you got it working now.

 

[BFSmith]

I am having the same type of problem...can't seem to properly reference the
field value...I have a post dated 2/17

Yes, I can retrieve the node when using only one table...changing
my:myFields/my:field1 to //my:field1 seems to have fixed that.

So this means you can also retrieve node.text now?
---
S.Y.M. Wong-A-Ton

A slight victory...I have the code working with one table...now if we get it
working with the joined tables we'll be golden.

:

I forgot to mention that since the second message box returned "True", the
XPath to your node containing the value for the WHERE clause is also
incorrect. So you must fix the following line too

var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");

and in particular "my:myFields/my:field1", before proceeding.

XDocument.UI.Alert(node == null); // <- must return "False"

---
S.Y.M. Wong-A-Ton


:

It is finding the table, the first window displays:

shape
{select "LOGICAL_NAME","WAP_APPL_ID","WAP_APPL_STTS_CDE" from
"dbo"."PICCT_APPL" as "PICCT_APPL"} as "PICCT_APPL"
append
({select "LOGICAL_NAME","VENDOR","VENDOR_ID","WDV_ASSET_DSC" from
"dbo"."PICCT_DEVICE" as "PICCT_DEVICE"}
relate "LOGICAL_NAME" TO "LOGICAL_NAME") as "PICCT_DEVICE"

The other window says True; so I think the Where clause is not being formed
correctly. The error message now is Object Required.

The code I used looks like this:

// Retrieve the SQL statement of the data source
var strSQL = XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
// WHERE clause; created field1 under the main data source
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");
XDocument.UI.Alert(node == null);
XDOcument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where WAP_APPL_ID = '" + node.text + "'";

// Populate the command with the new SQL statement
XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["PICCT_APPL"].Query();

thanks, ssaral

:

I'm guessing that the table cannot be found if you are getting 'Object not
found'. If you are getting 'Object required', your XPath query to find the
node is incorrect.

Let's add some debugging information to your code to see what's going on,
okay?

Add the following line of code right after
var strSQL = XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command;

XDocument.UI.Alert(strSQL); // <= line to add

What does it return?

Add the following lines of code right after
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");

XDocument.UI.Alert(node == null); // <= line to add
XDocument.UI.Alert(node.text); // <= line to add

What do they return?

And my last question: What exactly did you enter for dbo.table.fieldname and
fieldname? Please use the correct names you used and not "table" or
"fieldname".

---
S.Y.M. Wong-A-Ton


:

I tired to use the code below but keep getting an error 'Object not found' on
the line that reads

strSQL += " where tableFieldName = '" + node.text + "'";

where it is not correctly putting together the updated SQL code; for
tableFieldName I inserted dbo.table.fieldname and fieldname both didn't work,
there seems to be syntax problem, can you help...

thanks, ssaral

:

I will give it a try and let you know....thanks

:

Put the following code in the event handler of a button or field on your
InfoPath form. I haven't tested this code, but with some minor tweaking you
should be able to get it to work.

---
// Retrieve the SQL statement of the data source
var strSQL = XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command;

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");

// Generate the new SQL statement with WHERE clause
strSQL += " where tableFieldName = '" + node.text + "'";

// Populate the command with the new SQL statement
XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["YourDataSourceName"].Query();

---
S.Y.M. Wong-A-Ton


:

I would like to do it dynamicly, hard coding the filter won't be as helpful.
What would be the proper syntax for that...in other owrds how do you tell SQL
that this is a field in the InfoPath form and not a field in the SQL table.
Can you provide an example...

Thanks, ssaral

:

You can filter the data in a secondary data source that is connected to SQL
Server by editing the SQL statement through Tools > Data Connections,
[Modify...] button, [Edit SQL...] button, and adding a WHERE clause to the
SELECT statement. This will produce a "static" filter.

You can also produce a "dynamic" filter by modifying the SQL statement for
the secondary data source through code and composing a WHERE clause using the
value from e.g. a field within your InfoPath form.

---
S.Y.M. Wong-A-Ton


:

Hi,

I would like to use a secondary data source to recieve data from SQL, but
currently no query fields apear in my secondary data source in InfoPath all I
can see are data fields and as such the form return everything when I run a
query. How can I query a secondary data source and only return the row that I
want...?

thanks, ssaral