Querying secondary data connection - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Querying secondary data connection

Last post 02-28-2009 10:41 PM by Donna Kelly. 45 replies.
Page 1 of 4 (46 items) 1 2 3 4 Next >
Sort Posts: Previous Next
  • 10-24-2008 08:43 AM

    Querying secondary data connection

    Hello, I need to query a secondary data source to return specific rows of data based on a value entered in a field on my infopath form. The secondary data connection is to a SQL server database table. I can get this working perfectly when using the table as a main data connection. I simply add the query field I want to use along with the data fields to the form and use the "Run Query" button to generate the results. I am unable to replicate this with the table as a secondary data connection. Does anyone know how to do this with a secondary data source? Any help is appreciated. Thank you.

  • 10-24-2008 09:21 AM In reply to

    Re: Querying secondary data connection

    It involves about 7 lines of code, but this solution may be of some interest to you:
    http://www.infopathdev.com/forums/p/9467/33496.aspx#33496

    or this one:
    http://www.infopathdev.com/forums/p/8940/31780.aspx#31780

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-27-2008 04:30 AM In reply to

    Re: Querying secondary data connection

    Thank you Jimmy, the first link worked wonderfully. Now is there any way I can have that query actually save when the form is saved? I notice it disappears whenever I open the saved document.

  • 10-27-2008 07:25 AM In reply to

    Re: Querying secondary data connection

    Saving a form from InfoPath only saves the data that is in the main data source.  Since your query results are in a secondary data source, they naturally would not be saved with the form, so if you want to save the data, you will need a way of copying it over to your main data source.

     I can think of two ways to accomplish this.  The first is to create a group in your main data source that is similar to the results node, programmatically iterate through the query results, and copy them over one field value at a time. This can be done fairly simply.

    The second is to actually add part of the secondary data source's schema to your main data source, and programmatically copy over entire nodes from the results.  This is somewhat involved, but elegant in its own way.

     I'd be happy to explain either method to you tomorrow.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-27-2008 07:56 AM In reply to

    Re: Querying secondary data connection

    Thanks Jimmy. An explanation of the first option you mentioned would be great. Creating the duplicate fields in the main data source is not a problem. Programatically referencing each field from the query and copying those values into the newly created fields in the main data source seems more troubling to me.

  • 10-27-2008 10:28 PM In reply to

    Re: Querying secondary data connection

    Ok, sure thing.  the first thing I did was create a group in my main data source that mimicked the dataFields group in the secondary data source:

    Notice that I chose to use different names for my groups, but for simplicity's sake, I used the same column names and in the same order.  Note however that I used elements for the column data, while the secondary data source uses attributes, though using attributes for both wouldn't be any more difficult.

     This is the code I used, which would be inserted after the dc.Execute(); in this example.

    // store the namespace URLs as strings for easy reference
    string myNamespace = NamespaceManager.LookupNamespace("my");
    // the attributes on the secondary data source results have no prefix, as seen in the data source taskpane
    string blankNamespace = NamespaceManager.LookupNamespace("");

    // select the node where the results will be placed
    XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:QueryResults",NamespaceManager);
    // select the secondary data source node where the results are located
    XPathNavigator queryDataFields = DataSources["TestTable1"].CreateNavigator();
    queryDataFields = queryDataFields.SelectSingleNode(
    "/dfs:myFields/dfs:dataFields", NamespaceManager);

    // remove all ResultRow children of the my:QueryResults node in the main data source
    while (dataFields.MoveToChild("ResultRow", myNamespace))
    {
         dataFields.DeleteSelf();
    }

    // iterate through each results node in the secondary data source
    foreach(XPathNavigator resultRow in queryDataFields.Select("*")){
        
    // add a row to the results' repeating table/section. The name that corresponds to
        
    // "ResultRow_19" here can be found on the Advanced tab of the repeating row/section's
        
    // properties
        
    CurrentView.ExecuteAction(ActionType.XCollectionInsert, "ResultRow_19");

         // Select all of the nodes in the newly created row and copy values into them.
         // Their names must correspond precisely to the names of the nodes you want to copy over
         foreach (XPathNavigator resultNode in dataFields.Select("my:ResultRow[last()]/*", NamespaceManager))
         {
              resultNode.SetValue(queryResultRow.GetAttribute(resultNode.LocalName, blankNamespace));
         }
    }


     

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-29-2008 06:12 AM In reply to

    Re: Querying secondary data connection

    Jimmy, the code worked wonderfully and was very easy to follow. Thanks again for your help, you are a great asset to this forum!

     

  • 10-29-2008 08:33 AM In reply to

    Re: Querying secondary data connection

    Great to hear that!  I'm glad I was able to help.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 12-01-2008 08:38 AM In reply to

    Re: Querying secondary data connection

    I'm trying to query the secondary data source and display the results of the query in a repeat table in the main data source. Obviously something is to working right for me at this point.
    I have been using your code I find online, but the major problem I'm having at the moment it I can's seem to be able to refresh the repeat table to show the query results. after

     foreach (string colName in columnNames)
                        {
                            writer.WriteStartElement(colName, myNamespace);
                            MessageBox.Show(colName.ToString());
                            writer.WriteValue(resultRow.GetAttribute(colName, blankNamespace));
                            writer.WriteEndElement();
                        }
                        writer.WriteEndElement();

     

    do I need to have another line of code to display the results copied from secondary data source to the repeat table in the main data source.

    Your help will be greatly appreciated.

    Thanks

     

    The whole code I have for this is

     

     


     public void CTRL10_7_Clicked(object sender, ClickedEventArgs e)
            {
                // retrieve field1's value and store it in a string
                string field1 = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/dfs:queryFields/q:FRAQSTemplate/@CatID", NamespaceManager).Value;
                const string queryString = "select * from [FRAQSTemplate] where [CatID] = 3";


                // make sure that the search string isn't blank
                if (!string.IsNullOrEmpty(field1))
                {
                    // instantiate an object to access the data connection. TestTable1 is the name of the data connection
                    AdoQueryConnection dc = (AdoQueryConnection)DataConnections["SecondaryConnection"];

                    // insert field1's value into the query and assign it to the data connection's query
                    dc.Command = string.Format(queryString);

                    // run the query
                    dc.Execute();


                    //AdoQueryConnection newQuery = (AdoQueryConnection)MainDataSource.QueryConnection;
                    //newQuery.Execute();
                }
                else
                {
                    // show an error when field1 is blank
                    MessageBox.Show("The search field cannot be blank!");
                }

           }

     

     

     

            public void CTRL19_7_Clicked(object sender, ClickedEventArgs e)
            {
                // store all of the column names in an array, to be used below
                string[] columnNames = { "ID", "CatID", "QuestionNO", "Question", "Active", "Version", "TS" };
                // store the namespace URLs as strings for easy reference
                string myNamespace = NamespaceManager.LookupNamespace("my");
                // d is the prefix of the repeating nodes in the secondary data source results, as seen in the data source taskpane
                string dNamespace = NamespaceManager.LookupNamespace("d");
                // the attributes on the secondary data source results have no prefix, as seen in the data source taskpane
                string blankNamespace = NamespaceManager.LookupNamespace("");

                // select the node where the results will be placed
                XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("//dfs:myFields/my:QueryResults", NamespaceManager);
                // select the secondary data source node where the results are located
                XPathNavigator queryDataFields = DataSources["SecondaryConnection"].CreateNavigator();
                queryDataFields = queryDataFields.SelectSingleNode("//dfs:myFields/dfs:dataFields", NamespaceManager);

                // remove all ResultRow children of the my:QueryResults node in the main data source
                while (dataFields.MoveToChild("ResultRow", myNamespace))
                {
                    dataFields.DeleteSelf();
                }

                // iterate through each results node in the secondary data source
                foreach (XPathNavigator resultRow in queryDataFields.SelectChildren("FRAQSTemplate", dNamespace))
                {
                    // append a new child to the my:QueryResults node
                    using (XmlWriter writer = dataFields.AppendChild())
                    {
                        // write the element name
                        writer.WriteStartElement("ResultRow", myNamespace);
                        // copy over each of the attributes
                        foreach (string colName in columnNames)
                        {
                            writer.WriteStartElement(colName, myNamespace);
                            MessageBox.Show(colName.ToString());
                            writer.WriteValue(resultRow.GetAttribute(colName, blankNamespace));
                            writer.WriteEndElement();
                        }
                        writer.WriteEndElement();
                    }
                }
            }

  • 12-01-2008 09:35 AM In reply to

    Re: Querying secondary data connection

    Is the repeating section simply failing to refresh or are you getting an error?  If there's an error, could you tell me what it is?

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 12-02-2008 12:38 AM In reply to

    Re: Querying secondary data connection

    there is no error, but the repeating section can not refresh itself to show the copied records. in other words the repeating section will always be empty and I have no reason as to why. any piece of code I'm missing here?
  • 12-19-2008 04:49 AM In reply to

    Re: Querying secondary data connection

    Hi Jimmy, I have also had great success running the secondary query but couldn't store the data.  I have followed your code (see below) but when I display the repeating table bound to ResultRow it is empty.  Any ideas?

     // store all of the column names in an array, to be used below

    string[] columnNames = { "ID", "Name", "Perf", "DateEOM" , "NAV" };

    // store the namespace URLs as strings for easy reference

    string myNamespace = NamespaceManager.LookupNamespace("my");

    // d is the prefix of the repeating nodes in the secondary data source results, as seen in the data source taskpane

    string dNamespace = NamespaceManager.LookupNamespace("d");

    // the attributes on the secondary data source results have no prefix, as seen in the data source taskpane

    string blankNamespace = NamespaceManager.LookupNamespace("");

    // select the node where the results will be placed

    XPathNavigator dataFields = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:StoreData", NamespaceManager);

    // select the secondary data source node where the results are located

    XPathNavigator queryDataFields = DataSources["PGW import data"].CreateNavigator();

    queryDataFields = queryDataFields.SelectSingleNode("/dfs:myFields/dfs:dataFields", NamespaceManager);

    // remove all ResultRow children of the my:QueryResults node in the main data source

    while (dataFields.MoveToChild("ResultRow", myNamespace))

    {

    dataFields.DeleteSelf();

    }

    // iterate through each results node in the secondary data source

    foreach (XPathNavigator resultRow in queryDataFields.SelectChildren("PGW import data", dNamespace))

    {

    // append a new child to the my:QueryResults node

    using (XmlWriter writer = dataFields.AppendChild())

    {

    // write the element name

    writer.WriteStartElement("ResultRow", myNamespace);

    // copy over each of the attributes

    foreach (string colName in columnNames)

    {

    writer.WriteStartElement(colName, myNamespace);

    writer.WriteValue(resultRow.GetAttribute(colName, blankNamespace));

    writer.WriteEndElement();

    }

    writer.WriteEndElement();

    }

    }

  • 12-19-2008 05:41 AM In reply to

    Re: Querying secondary data connection

    Looks to me like this is the problem line:

    foreach (XPathNavigator resultRow in queryDataFields.SelectChildren("PGW import data", dNamespace))

    I can't imagine how there could possibly be a node in your secondary data source with spaces in it.  Please double-check what the name of that field should be.  Also, whereas the data source in my example happened to use the prefix d:, your situation will not necessarily be the same.  Please double-check on that too.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 12-19-2008 06:41 AM In reply to

    Re: Querying secondary data connection

    Jimmy, you genius, that was all it was.  It works brilliantly, thank you.  My only challenge now is to somehow get the ADO Data connection speed down from 30 seconds.

  • 12-19-2008 11:04 AM In reply to

    Re: Querying secondary data connection

    Did you happen to have the same code in Jscript.  I was testing in C# but realise I have committed to Jscript in the production form. I'm not a strong enough programmer to convert.

Page 1 of 4 (46 items) 1 2 3 4 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.