none
Dynamic SQL Data connection in InfoPath RRS feed

  • Question

  • I'm trying to load quite a bit of information from a SQL server into an InfoPath form, but I want to trim that down by running a "dynamic query" based on a drop down list selection from the form. However there's no way that I can pass the variable back to the SQL server using the data connection (like you could with web services). So I'm looking to dynamically change the SQL query string within the data connection to include the variable (as a where x = y scenario). Have looked all over the place and can't find anything that relates to what I'm trying to do! I've seen code that can change the data connection itself, but not the SQL query with in the data connection. Is it even possible!?
    Tuesday, January 8, 2013 6:58 AM

Answers

  • Hi Ridethetorpedo,

    You can use code like this on the "click" event of a button to modify a SQL Server secondary data connection to use a "filter" from your form:

    NOTE: The SQL data connection I added is called: Orders and my filter field (from my form) is: txtCustomerID.

    //Create an XmlNamespaceManager
    XmlNamespaceManager ns = this.NamespaceManager;

    //Create an XPathNavigator for the main data source
    XPathNavigator xnMain = this.MainDataSource.CreateNavigator();

    //Cast the SQL Secondary Data Connection to an AdoQueryConnection.
    //This will allow modification of the SQL query
    AdoQueryConnection dsSQL = (AdoQueryConnection) this.DataConnections["Orders"];

    //Store the original SQL query (what it is before modification)
    string strOrigSQL = dsSQL.Command;

    //Get the CustomerID filter value to be used to limit the data
    string strCustomerFilter = xnMain.SelectSingleNode("/my:myFields/my:txtCustomerID", ns).Value;

    //Modify the SQL query
    dsSQL.Command = dsSQL.Command + " WHERE CustomerID = '" + strCustomerFilter + "'";

    //Now execute the connection
    dsSQL.Execute();

    //Reset the SQL query
    dsSQL.Command = strOrigSQL;

    //Clean up
    ns = null;
    xnMain = null;
    dsSQL = null;

    Let me know if this helps!

    Scott


    Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support

    Tuesday, January 15, 2013 6:57 PM

All replies

  • Hi,

    Did you create your InfoPath solution directly from your SQL Server
    database?
    If not, this is what you will want to do as the default behavior
    is you will
    get both "queryFields" and "dataFields." The query fields
    provide the exact
    functionality you desire(you can pass the variable back to the SQL server using the data connection ): enter a valid value into the
    query field(s) and
    click Run Query - the results will automatically be
    populated into your
    "data" fields.

    I hope this helps!


    Regards Avanish T

    Tuesday, January 8, 2013 7:31 AM
  • Thanks for the quick reply!

    I didn't create the SQL back end and thus when I create the data connection I don't get an option for a query field unfortunately. It only has data fields, hence the need to query it on the fly!

    • Proposed as answer by Jonnyl1970 Sunday, October 9, 2016 4:46 PM
    Tuesday, January 8, 2013 7:43 AM
  • Hi,

    Please check you main connection for this and try as:

    Go to your InfoPath form and then click on Refresh Fields option under the Data Menu and then in dialog box select Create a new connection to:->Recieve Data and then click on Next, here youhave to select Radio bottun for Database(Microsoft SQL Server Only) and then click on Next, Now you can change your Database and Modify your Sql query for access data in InfoPath form.

    So please try it , then you will see that in your Field pane both "queryFields" and "dataFields." are in it. 

    as:

    To create such type of form we have to start with database option at the time of create a new form:


    Regards Avanish T

    • Marked as answer by Ridethetorpedo Tuesday, January 8, 2013 9:01 AM
    • Unmarked as answer by Ridethetorpedo Tuesday, January 8, 2013 10:40 AM
    Tuesday, January 8, 2013 8:43 AM
  • Ah, it's because I had created the form initially as a filler form and not a database form. When I create it as a database form it gives me the option for query fields.

    Thanks for your help! I'll transfer everything into a database form, much appreciated!

    Tuesday, January 8, 2013 9:00 AM
  • Although I am only getting this for the "main" fields that I set up when I created this form.

    I've created a secondary data connection and this doesn't give me the query fields, it only shows data fields?

    Tuesday, January 8, 2013 9:06 AM
  • Yes, in Secondary data source, it gives only data fields because we use sceondary data source for filter's(drop down's available value) purpose in the form, so it doesn't contains query fields.

    For more on secondary data connection use, please look here:

    http://office.microsoft.com/en-us/infopath-help/filter-the-data-that-is-displayed-in-a-control-HA010024281.aspx

    and difference between both main and secondary:http://pravahaminfo.blogspot.com.es/2011/05/differences-between-main-vssecondary.html


    Regards Avanish T


    Tuesday, January 8, 2013 9:16 AM
  • Cool, thanks. I can get around it easily as I only want to filter one data source so I'll use that as the primary so it hopefully will work!

    Thanks for your help.

    Tuesday, January 8, 2013 9:34 AM
  • As it turns out this isn't going to work as there is no way that I can see where I can then publish the form with varying fields to my SharePoint site as it only allows me to publish fields that are in the main connection.

    So is there any way that I can query a secondary data connection without having to write too much code?

    I figure that adjusting the SQL code in the secondary data connection would be easiest as it only requires the changing of one variable.

    Given the number of records that are being brought down (20k+) I want to avoid loading the form with all that data then applying filters to the form/repeating sections as it takes a couple of minutes to load each time.

    Tuesday, January 8, 2013 10:40 AM
  • Hi Ridethetorpedo,

    You can use code like this on the "click" event of a button to modify a SQL Server secondary data connection to use a "filter" from your form:

    NOTE: The SQL data connection I added is called: Orders and my filter field (from my form) is: txtCustomerID.

    //Create an XmlNamespaceManager
    XmlNamespaceManager ns = this.NamespaceManager;

    //Create an XPathNavigator for the main data source
    XPathNavigator xnMain = this.MainDataSource.CreateNavigator();

    //Cast the SQL Secondary Data Connection to an AdoQueryConnection.
    //This will allow modification of the SQL query
    AdoQueryConnection dsSQL = (AdoQueryConnection) this.DataConnections["Orders"];

    //Store the original SQL query (what it is before modification)
    string strOrigSQL = dsSQL.Command;

    //Get the CustomerID filter value to be used to limit the data
    string strCustomerFilter = xnMain.SelectSingleNode("/my:myFields/my:txtCustomerID", ns).Value;

    //Modify the SQL query
    dsSQL.Command = dsSQL.Command + " WHERE CustomerID = '" + strCustomerFilter + "'";

    //Now execute the connection
    dsSQL.Execute();

    //Reset the SQL query
    dsSQL.Command = strOrigSQL;

    //Clean up
    ns = null;
    xnMain = null;
    dsSQL = null;

    Let me know if this helps!

    Scott


    Scott Heim - Microsoft Office InfoPath and SharePoint Designer Online Community Support

    Tuesday, January 15, 2013 6:57 PM