custom query with two tables in the same database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sgkaplan - 14 Mar 2006 19:34 GMT

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.

Reply to this Message

S.Y.M. Wong-A-Ton - 15 Mar 2006 12:06 GMT

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.
[quoted text clipped - 3 lines]
> How should I do?
> Thanks.

Reply to this Message

sgkaplan - 15 Mar 2006 16:27 GMT

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

>Put the following code in the event handler of a button
> ---
[quoted text clipped - 13 lines]
> // 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
[quoted text clipped - 10 lines]
> > How should I do?
> > Thanks.

Reply to this Message

S.Y.M. Wong-A-Ton - 16 Mar 2006 11:47 GMT

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:
>
[quoted text clipped - 46 lines]
> > > How should I do?
> > > Thanks.

Reply to this Message

sgkaplan - 16 Mar 2006 20:02 GMT

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,
[quoted text clipped - 59 lines]
> > > > How should I do?
> > > > Thanks.

Reply to this Message

S.Y.M. Wong-A-Ton - 19 Mar 2006 19:46 GMT

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:
[quoted text clipped - 67 lines]
> > > > > How should I do?
> > > > > Thanks.

Reply to this Message