locked
How to design an InfoPath 2010 form to query and submit data to SQL Server 2008 database RRS feed

  • Question

  • I am new to InfoPath. I need help on how to design a form that can query data based on 3 tables in an SQL 2008 database. The tables all have unique primary keys and can be connected using secondary keys. One table is the main table and the other 2 are linked to it in a one-to-main relationship. I would like to query table 2 and if data exists, it will populate the form with information from both table 2 and table 3. If data does not exist, then it should open a blank form to input new data and submit to the database. This form will be published to a SharePoint 2007 and SharePoint 2010 sites.

    Thanks for any help

    Nee

    Tuesday, April 10, 2012 7:20 PM

Answers

  • Hi Nee Okai,

    Microsoft InfoPath Form support secondary data connection that you can query the data that stored in external data source (i.e. table 2 and table 3) to the from. InfoPath also support you query data directly from database (Sql server only). But in your case, you need to submit some data to table 2 and table 3 occasionally, so you have to write a web service for the data connection in order to add new data and submit it to the database. You can accomplish your requirement as below:

    1. Write web services for the table 1,2,3. Then the InfoPath Form can query and submit data from database through the web services. You can create web service for InfoPath per this article.
    2. Create data connection according to the web services. Create the Form, bind certain control to the data connection.
    3. Use InfoPath code behind to prompt user to add new data when there’s no data in table 2 or table 3.

    Hope that helps.

    Thanks,


    Lambda Zhao

    TechNet Community Support

    Thursday, April 12, 2012 8:44 AM

All replies

  • Hi Nee Okai,

    Microsoft InfoPath Form support secondary data connection that you can query the data that stored in external data source (i.e. table 2 and table 3) to the from. InfoPath also support you query data directly from database (Sql server only). But in your case, you need to submit some data to table 2 and table 3 occasionally, so you have to write a web service for the data connection in order to add new data and submit it to the database. You can accomplish your requirement as below:

    1. Write web services for the table 1,2,3. Then the InfoPath Form can query and submit data from database through the web services. You can create web service for InfoPath per this article.
    2. Create data connection according to the web services. Create the Form, bind certain control to the data connection.
    3. Use InfoPath code behind to prompt user to add new data when there’s no data in table 2 or table 3.

    Hope that helps.

    Thanks,


    Lambda Zhao

    TechNet Community Support

    Thursday, April 12, 2012 8:44 AM
  • Lambda,

    Thanks for the suggestion. I will try it and update you later. I really appreciate your help

    Nee

    Wednesday, April 18, 2012 2:34 PM