custom query with two tables in the same database

[sgkaplan]

hello, i have seem many questions about how to do custom queries. Answers
are examples with one table.
I want to do a custom query (add a where to the select with "LIKES", etc)
but I have in the form fields of two different tables. They are includes in
the same database.
How should I do?
Thanks.

 

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

Did you also see the post in this newsgroup with the title "Secondary data
source query"? It shows how to add a WHERE-clause to the SQL statement of two
related tables in a secondary data source. You should be able to easily
extend it to your own situation.

 

[sgkaplan]

Hello, I saw the post "Secondary data source query". This is:

Put the following code in the event handler of a button
---
// 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();

I did this, but don't work. When I put fields of one table this work, but
don't work if I put fields of two tables.

I have fields of table1 and table2. Table1 is the primary table and has a
relationship with table2.
In the line:
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");
I write:
var node =
XDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:table1");

When I write a value in a field of table1 and press the query button I
obtain an error.
If I write a value only in a field of table2 and press the query button The
result is the SELECT with out WHERE and work fine.

 

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

Why are you writing to the query fields? You need to retrieve the SELECT
statement that is saved in the Command of the DataObject of the data source,
change this SELECT statement to include your WHERE-clause using the values
that the user inserted in whatever query fields you are using on your form,
and then call Query() on the data source.

Look at the entire thread (all 22 messages) of the post I refered to. Since
you are using two related tables, you need to change the SELECT statement in
a slightly different way, because a SHAPE is being used. You must not just
append a WHERE since that is not going to work.
---
S.Y.M. Wong-A-Ton

Hello, I saw the post "Secondary data source query". This is:

Put the following code in the event handler of a button
---
// 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();

I did this, but don't work. When I put fields of one table this work, but
don't work if I put fields of two tables.

I have fields of table1 and table2. Table1 is the primary table and has a
relationship with table2.
In the line:
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");
I write:
var node =
XDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:table1");

When I write a value in a field of table1 and press the query button I
obtain an error.
If I write a value only in a field of table2 and press the query button The
result is the SELECT with out WHERE and work fine.

Did you also see the post in this newsgroup with the title "Secondary data
source query"? It shows how to add a WHERE-clause to the SQL statement of two
related tables in a secondary data source. You should be able to easily
extend it to your own situation.

 

[sgkaplan]

Thank you.
My problem is:
If I execute the statement int the query analizer, I can view that the
instruction shape don't exist. Then this statement don't work.
Where do I include the WHERE-clause in this statement then?
I included an WHERE 1 = 1 and didn't work.

Why are you writing to the query fields? You need to retrieve the SELECT
statement that is saved in the Command of the DataObject of the data source,
change this SELECT statement to include your WHERE-clause using the values
that the user inserted in whatever query fields you are using on your form,
and then call Query() on the data source.

Look at the entire thread (all 22 messages) of the post I refered to. Since
you are using two related tables, you need to change the SELECT statement in
a slightly different way, because a SHAPE is being used. You must not just
append a WHERE since that is not going to work.
---
S.Y.M. Wong-A-Ton

Hello, I saw the post "Secondary data source query". This is:

Put the following code in the event handler of a button
---
// 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();

I did this, but don't work. When I put fields of one table this work, but
don't work if I put fields of two tables.

I have fields of table1 and table2. Table1 is the primary table and has a
relationship with table2.
In the line:
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");
I write:
var node =
XDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:table1");

When I write a value in a field of table1 and press the query button I
obtain an error.
If I write a value only in a field of table2 and press the query button The
result is the SELECT with out WHERE and work fine.

Did you also see the post in this newsgroup with the title "Secondary data
source query"? It shows how to add a WHERE-clause to the SQL statement of two
related tables in a secondary data source. You should be able to easily
extend it to your own situation.
---
S.Y.M. Wong-A-Ton


:

hello, i have seem many questions about how to do custom queries. Answers
are examples with one table.
I want to do a custom query (add a where to the select with "LIKES", etc)
but I have in the form fields of two different tables. They are includes in
the same database.
How should I do?
Thanks.

 

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

You mustn't look in Query Analyzer, but in InfoPath under "Tools > Data
Connections". Select the connection and then edit the SQL statement through
the "Data Connections" dialog box. You can edit this same SQL statement
through code by accessing the Command property.
----
S.Y.M. Wong-A-Ton

Thank you.
My problem is:
If I execute the statement int the query analizer, I can view that the
instruction shape don't exist. Then this statement don't work.
Where do I include the WHERE-clause in this statement then?
I included an WHERE 1 = 1 and didn't work.

Why are you writing to the query fields? You need to retrieve the SELECT
statement that is saved in the Command of the DataObject of the data source,
change this SELECT statement to include your WHERE-clause using the values
that the user inserted in whatever query fields you are using on your form,
and then call Query() on the data source.

Look at the entire thread (all 22 messages) of the post I refered to. Since
you are using two related tables, you need to change the SELECT statement in
a slightly different way, because a SHAPE is being used. You must not just
append a WHERE since that is not going to work.
---
S.Y.M. Wong-A-Ton

Hello, I saw the post "Secondary data source query". This is:

Put the following code in the event handler of a button
---
// 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();


I did this, but don't work. When I put fields of one table this work, but
don't work if I put fields of two tables.

I have fields of table1 and table2. Table1 is the primary table and has a
relationship with table2.
In the line:
var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1");
I write:
var node =
XDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:table1");

When I write a value in a field of table1 and press the query button I
obtain an error.
If I write a value only in a field of table2 and press the query button The
result is the SELECT with out WHERE and work fine.





:

Did you also see the post in this newsgroup with the title "Secondary data
source query"? It shows how to add a WHERE-clause to the SQL statement of two
related tables in a secondary data source. You should be able to easily
extend it to your own situation.
---
S.Y.M. Wong-A-Ton


:

hello, i have seem many questions about how to do custom queries. Answers
are examples with one table.
I want to do a custom query (add a where to the select with "LIKES", etc)
but I have in the form fields of two different tables. They are includes in
the same database.
How should I do?
Thanks.