Query SQL Secondary data source

[Mike]

I didn't think I was trying anything that should be complicated but I'm not
having much success.

I'm trying to run a query to one table in a SQL database and return the
value of only a single record. I'll use those values plus other user entered
values to submit a new record to a second table in the same SQL database.

It seems like I have it somewhat sorted out, but querying the secondary data
source tries to return all records from that table to the form, but I only
want it to return the one record that matches the value of another field that
the user input. The table is far to large to return all records and then
filter them in a reasonable time. How do I get the query for the secondary
data to not return the entire table?

Seems I can do this just fine with the main connection because it has
queryFields, but the secondary doesn't have queryFields.

Thanks

 

[Paresh]

I didn't think I was trying anything that should be complicated but I'm not
having much success.

I'm trying to run  a query to one table in a SQL database and return the
value of only a single record.  I'll use those values plus other user entered
values to submit a new record to a second table in the same SQL database.

It seems like I have it somewhat sorted out, but querying the secondary data
source tries to return all records from that table to the form, but I only
want it to return the one record that matches the value of another field that
the user input.  The table is far to large to return all records and then
filter them in a reasonable time. How do I get the query for the secondary
data to not return the entire table?

Seems I can do this just fine with the main connection because it has
queryFields, but the secondary doesn't have queryFields.

Thanks

I think, you will need to change your SQL query to get the desired
result. If you are querying the database and it returns multiple
records, try to filter at sql front only rather applying filters. etc
on InfoPath form.

Thanks,
Paresh
http://pareshs.wordpress.com/

 

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

While there is an "Edit SQL" option on the data connection, you cannot pass a
dynamic value to it. What you could do however, is temporarily modify the
Command property on the data connection to contain your own modified query
(including a WHERE-clause with a value the user entered), retrieve the data,
and then restore the Command. In the following article I used a stored
procedure, but you could also use a normal dynamic SQL statement:
http://www.bizsupportonline.net/inf...ction-execute-sql-server-stored-procedure.htm

 

[Mike]

I went with the WHERE clause option. I used info I found at this site to
complete what I needed.
http://radio.weblogs.com/0131777/stories/2004/02/27/howToPopulateItemsFromASecondaryDataSource.html

Thanks!