Two queries with one field in common

[Joanne]

Hello, I have a main data source with a query field with two tables in it,
both with a "LoginID" field. I populate the first table's field with a name,
e.g. JSMITH. The field name is @loginID. Then I want the query to select
only those fields in the second table which have the same name in the
@loginID field, but I keep getting an error.
Here is the code:

'Get the original query
strOrigSQLCommand=XDocument.QueryAdapter.Command

set
queryMentor=XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:tblMentor_Lookup")

QueryMentor.SelectSingleNode("@MentorLoginID").text=strinfo
strSQLQuery=XDocument.QueryAdapter.BuildSQLFromXMLNodes(QueryMentor)
'Concatenate WHERE clause and original SQL statement

IT IT ON THE NEXT LINE THAT THE CODE FAILS (in the where clause,but if I
only use one table it works fine).

strSQLQuery= strOrigSQLCommand + " WHERE " + strSQLQuery
XDocument.QueryAdapter.Command=StrSQLQuery
XDocument.Query()

 

[Zhang Haiguang]

If you extract the form template(.xsn) file, and open manifest.xsf in
notepad, you will see the command of the data connection with two tables in
it will be like the following:
shape {select
"CustomerID","CompanyName","ContactName","ContactTitle","Address","City","Region","PostalCode","Country","Phone","Fax"
from "dbo"."Customers" as "Customers"} as "Customers" append ({select
"OrderID","CustomerID","EmployeeID","OrderDate","RequiredDate","ShippedDate","ShipVia","Freight","ShipName","ShipAddress","ShipCity","ShipRegion","ShipPostalCode","ShipCountry"
from "dbo"."Orders" as "Orders"} relate "CustomerID" TO "CustomerID") as
"Orders"
So, if you need to add a where clause in it, you should add the where clause
before the character }.
Please reference the following article:
http://support.microsoft.com/kb/189657 (How To Use the ADO SHAPE Command)

InfoJet Service
InfoPath Web Form
[http://www.infojetsoft.com]