SQL Database Integration

Re: Filtering Secondary Data Source

vapires — Thu, 26 Jan 2006 12:16:07 GMT

Hello Matt,

Thanks for your help.

I am able to now selectSingleNode into my query. I am using c#.
The problem I am having is that when I try to use the QueryAdapter I do not see the command attribute.
ie I do not see oSecondary.QueryAdapter.Command
I can just get to oSecondary.QueryAdapter therefore I cannot build my query. Is there something I'm missing to do?

As a work around I have tried doing the following, but this does not work either. Any help is appreciated.

string sNodeText = thisXDocument.DOM.selectSingleNode("//dfs:myFields//dfs:dataFields/d:PPProject/d:PPAssoc/@ContactID").text;

//Define SQl Connection
SqlConnection ADConnection = new SqlConnection();
ADConnection.ConnectionString = "password=mycity;user id=mycity;data source=sql02;persist security info=False;initial catalog=CustomApps";
SqlCommand ADCommand = new SqlCommand();
ADCommand.Connection = ADConnection;
ADCommand.CommandText = "select * from PPEmployee(nolock) where contactid=" + sNodeText;
ADCommand.CommandType = CommandType.Text;
DataSet ADData = new DataSet();
SqlDataAdapter ADAdapter = new SqlDataAdapter();
ADAdapter.SelectCommand = ADCommand;
ADConnection.Open();
ADAdapter.Fill(ADData, "AD");
ADConnection.Close();

There are only 10 types of people in the world - Those who understand binary, and those who don't.

Re: Filtering Secondary Data Source

Matt Faus — Wed, 25 Jan 2006 14:46:33 GMT

Well, you could just grab it in your code, like this:

var sNodeText = XDocument.DOM.selectSingleNode( "/my:myFields/my:IdField" ).text;

Then, construct the query string with the variable:

oSecondary.QueryAdapter.Command = "SELECT * FROM customTable WHERE id > " + sNodeText;

Be sure to substitute appropriate XPath for your form.

---------------
Matt Faus
InfoPath Dev Texas

Re: Filtering Secondary Data Source

vapires — Wed, 25 Jan 2006 12:05:43 GMT

Hello Matt,

One more question. If I choose to do it by custom SQL commands, how would I get the text box node value in the where clause?
Appreciate your help,
Vidette

// Another way to do it use custom SQL commands
oSecondary.QueryAdapter.Command = "SELECT * FROM customTable WHERE id > 12";
oSecondary.Query();

There are only 10 types of people in the world - Those who understand binary, and those who don't.

Re: Filtering Secondary Data Source

deborahh — Tue, 24 Jan 2006 22:11:57 GMT

Thanks, we are going in the direction you have pointed us. We're going to give a try and hopefully get it to work.

Re: Filtering Secondary Data Source

Matt Faus — Tue, 24 Jan 2006 18:58:58 GMT

I'm sorry, I forgot that the Query was done on the main data connection. Check out the following code for some better info:

var oSecondary = XDocument.DataObjects( "Name of Secondary Data source" );

// One way to do it - set the query fields correctly then run query
oSecondary.DOM.selectSingleNode( "....." );
oSecondary.Query();

// Another way to do it use custom SQL commands
oSecondary.QueryAdapter.Command = "SELECT * FROM customTable WHERE id > 12";
oSecondary.Query();

---------------
Matt Faus
InfoPath Dev Texas

Re: Filtering Secondary Data Source

deborahh — Tue, 24 Jan 2006 15:56:26 GMT

Thanks for your response Matt. I looked over your HowTo and I see how it could work if you have a table that is in the main connection. However, what do you do if the table you're working on isn't a main data source?

Example:

Main Connection:
Projects (ProjectID, ProjName); ProjectID is 1 to many to ProjectID in PContacts
PContacts(PCID, CustomerID, ProjectID)

Secondary Data Source:
Contacts (ContactID, ContactName)

I want to use the SDS connection for Contacts to show all ContactID = filter PContacts.ContactID where ProjectID is equal to the current selected project in the form.

Thanks for your quick response.

Re: Filtering Secondary Data Source

Matt Faus — Tue, 24 Jan 2006 15:27:26 GMT

Hello Deborah and welcome to the Forum!

I have recently written a HowTo that addresses this issue. The HowTo describes creating a master/detail view with a database, but I think the code behind filtering the detail view should still apply to you. Check it out:

http://infopathdev.com/howto/tutorials/default.aspx?i=0bb37b4de44348408680a53a449f2353

I hope this helps, let us know if you have any more questiosn!

---------------
Matt Faus
InfoPath Dev Texas

Filtering Secondary Data Source

deborahh — Tue, 24 Jan 2006 15:16:36 GMT

I've been looking around to see if there is a way to filter data from a secondary data source and am at a loss. This particular table is SQL.

Once I drag the SDS table onto the form, the option to filter (as you would from a main data connection) isn't there. I want to be able to see all items for the section that fit a particular criteria in the form. I know that filtering for the SDS repeating section through Properties is not available.

Is there an easy way to create a filter for the SDS?

Thanks in advance for any help you can provide.