Secondary data source query

[ssaral]

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

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

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

[ssaral]

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

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

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

[ssaral]

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

[ssaral]

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

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

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

[ssaral]

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

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

Ah... I see you are using a join between two tables. Appending the WHERE
clause to the SQL query isn't going to work then. The best thing to do in
this case is to copy over the entire SQL SELECT string and add the WHERE
clause in code.

So instead of

var strSQL = XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command;

do something like
var strSQL = "shape

{select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from

\"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = " + node.text
+ "} 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\""

Note: I removed the single quotes from around node.text, since I suspect
that the WAP_APPL_ID is of a numeric type and not a string. If it's a string,
put back the single quotes. Make sure to retrieve node before composing the
SQL statement.

So call

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

before
var strSQL = ...

Then put the follwoing code after var strSQL = ....

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

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

---

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

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"

[ssaral]

OK, I get an error message that reads Un-terminated string constant for the
line that starts with var strSQL = "shape... in InfoPath

The code I used is below:

// Retrieve the field in the InfoPath form whose value will be used

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

// Copy over the entire SQL SELECT string and add the WHERE clause
var strSQL = "shape //<- error message refers to this line

{select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from

\"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = " + 'node.text'
+ "} 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\""

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

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

I tried putting ; at the end of various lines but that didn't seem to fix
the problem.

As far as the Xpath for the form field goes I changed it to \\my:field1
above I don't know if that's correct. my:myFields/my:field1 should have
worked before, when I look at the Data Source pane it's listed as:

Data source: Main

- myFields
+ queryFields
+ dataFields
my:field1

thanks for all your help, ssaral

[ssaral]

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.

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

> OK, I get an error message that reads Un-terminated string constant for the
> line that starts with var strSQL = "shape... in InfoPath

The un-terminated string constant error is probably being caused by the
single quotes you added around node.text. They should be inside the double
quotes. So instead of

..." + 'node.text' + "...

you should use

...' " + node.text + " '... //<- spaces between quotes added only for clarity

So this would be your new strSQL:
var strSQL = "shape

{select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from

\"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = '" node.text

+ "'} 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\""

Since you don't have spaces in your table or field names, you can also try:
var strSQL = "shape
{select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from
dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" node.text
+ "'} 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"

which is a little bit more readable.

> As far as the Xpath for the form field goes I changed it to \\my:field1
> above I don't know if that's correct. my:myFields/my:field1 should have

//my:field1 is the correct syntax. And you're right, it should have worked.

Let me know how things work out with these minor changes.

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

So this means you can also retrieve node.text now?

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

I see I missed a + sign. Correction:

var strSQL = "shape
{select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from

\"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = '" + node.text

+ "'} 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\""

or

var strSQL = "shape
{select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from

dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" + node.text
+ "'} 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"

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

[ssaral]

I tried both code samples below, and both are still giving me the same error
message 'Unterminated string constant' on the line same line.

thanks, ssaral

"S.Y.M. Wong-A-Ton" wrote:

> I see I missed a + sign. Correction:
>

> var strSQL = "shape //<---Unterminated string constant

[ssaral]

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

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

Try cutting up the string in pieces like this:

var strSQL = "";
strSQL += " shape ";
strSQL += " {select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from ";
strSQL += " dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" + node.text
+ "'} ";
strSQL += " as PICCT_APPL ";
strSQL += " append ";
strSQL += " ({select LOGICAL_NAME, VENDOR, VENDOR_ID, WDV_ASSET_DSC from ";
strSQL += " dbo.PICCT_DEVICE as PICCT_DEVICE} ";
strSQL += " relate LOGICAL_NAME TO LOGICAL_NAME) as PICCT_DEVICE ";

That should solve the problem.

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

Forgot to mention: Do not just copy and paste the code, but make sure that
each piece of the SQL statement enclosed by double quotes fits on 1 single
line.

strSQL += " ... "; //<- must fit on one line and NOT break to the following
line

[ssaral]

that did it...you are the man...thanks for all the help...

ssaral

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

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

[ssaral]

sorry about that...that's even cooler

[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