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.

 

[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

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

 

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

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

 

[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

 

[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

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

 

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

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

 

[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

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

 

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

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

 

[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

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

 

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

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

 

[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

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

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

 

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

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

 

[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

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

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

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

 

[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

I see I missed a + sign. Correction:

var strSQL = "shape //<---Unterminated string constant
{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

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

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


:

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

 

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

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

 

[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

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

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

I see I missed a + sign. Correction:

var strSQL = "shape //<---Unterminated string constant
{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

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


:

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


:

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

 

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