How to submit data from an InfoPath form to a MySQL database using a Web Service

This article shows you how to use a Microsoft .NET Web Service to submit data from an InfoPath form to a MySQL database.

ADVERTISEMENTS

Problem

You would like to use InfoPath to submit data to a MySQL database, but InfoPath does not allow you to connect to a MySQL database.

Solution

Use a Microsoft .NET Web Service to connect to and insert data into a table of the MySQL database and then use the web service as a data connection in InfoPath to submit data to the MySQL database.

Discussion

This article uses a MySQL database named InfoPathDB, which contains a table named users. The table has 3 varchar columns: firstname, lastname, and email.

To retrieve data from a MySQL database and display it in an InfoPath form, see How to get data from a MySQL database into InfoPath using a Web Service.

Before you begin, you should:

  1. Have downloaded MySQL and installed it.
  2. Have downloaded the ODBC connector for MySQL and installed it.
  3. Have created a MySQL database, created a MySQL table, and populated a MySQL table with data.
  4. Know how to create a .NET web service that retrieves data in a database.

Create a DSN to connect to the MySQL database

To create a DSN that can be called from within the web service:

  1. On the Start menu, click Run.
  2. On the Run dialog box, type odbcad32, and click OK.
  3. On the ODBC Data Source Administrator dialog box, click the System DSN tab, and then click Add.
  4. On the Create New Data Source dialog box, select the MySQL ODBC x.x Driver, and click Finish.
  5. On the MySQL Connector/ODBC Data Source Configuration dialog box, enter the required information to connect to your MySQL database, click Test to test whether the connection is working, and then click OK.

MySQL database connection settings on the MySQL Connector ODBC data source configuration dialog box.
Figure 1. MySQL database connection settings on the MySQL Connector ODBC data source configuration dialog box.

Create a .NET web service to submit data to the MySQL database

  1. In Visual Studio, create a new Web Service project.
  2. Add a reference to the System.Data .NET assembly.
  3. Add a using statement for the System.Data and System.Data.Odbc namespaces to the code file of the Web Service.
  4. Add the following Web Method to the code file of the Web Service:

    [WebMethod]
    public void AddUser(string FirstName, string LastName, string Email)
    {
      using (OdbcConnection conn =
        new OdbcConnection("DSN=InfoPathMySQLDSN"))
      {
        // Open a connection to the MySQL database.
        conn.Open();

        // Add the user.
        using (OdbcCommand cmd = new OdbcCommand())
        {
          // Initialize the command object.
          cmd.Connection = conn;
          cmd.CommandText =
            "INSERT INTO USERS " +
            "(firstname, lastname, email) VALUES (?, ?, ?)";

          // Set the parameters for the command object.
          OdbcParameter param = new OdbcParameter("0", FirstName);
          cmd.Parameters.Add(param);
          param = new OdbcParameter("1", LastName);
          cmd.Parameters.Add(param);
          param = new OdbcParameter("2", Email);
          cmd.Parameters.Add(param);

          // Add the record to the database.
          cmd.ExecuteNonQuery();
        }

        // Close the connection.
        conn.Close();
      }

      return;
    }

    The previous web method inserts the data for a user into the MySQL users database table.

  5. Build the project and deploy the web service.

Create an InfoPath form template to submit data to the MySQL database

  1. In InfoPath, create a form template with 3 text boxes (firstname, lastname, and email) and a button control as shown in Figure 2.
    The InfoPath form template in Design mode.
    Figure 2. The InfoPath form template in Design mode.
  2. On the Tools menu, click Data Connections.
  3. On the Data Connections dialog box, click Add.
  4. On the Data Connection Wizard, select Create connection to, select Submit data, and click Next.
  5. On the Data Connection Wizard, select To a Web service, and click Next.
  6. On the Data Connection Wizard, type in the URL to the web service that inserts data into the MySQL database, and click Next.
  7. On the Data Connection Wizard, select the AddUser method from the list of operations, and click Next.
  8. On the Data Connection Wizard, select tns:FirstName from the list of parameters, and click the button behind the Field or group text box.
  9. On the Select a Field or Group dialog box, select firstname, and click OK.
  10. On the Data Connection Wizard, select tns:LastName from the list of parameters, and click the button behind the Field or group text box.
  11. On the Select a Field or Group dialog box, select lastname, and click OK.
  12. On the Data Connection Wizard, select tns:Email from the list of parameters, and click the button behind the Field or group text box.
  13. On the Select a Field or Group dialog box, select email, and click OK.
  14. On the Data Connection Wizard, click Next.
  15. On the Data Connection Wizard, click Finish.
  16. On the Data Connections dialog box, click Close.
  17. On the InfoPath form template, double-click the button control.
  18. On the Button Properties dialog box, click Rules.
  19. On the Rules dialog box, click Add.
  20. On the Rule dialog box, click Add Action.
  21. On the Action dialog box, select Submit using a data connection from the Action drop-down list box, select the data connection to submit to a web service from the Data connection drop-down list box, and click OK.
  22. Click OK on all open dialog boxes.
  23. Save your work and Preview the InfoPath form.

You should now have a fully functional InfoPath form so that when you enter data for a user and then click the button, the web service will be called to insert the data into the MySQL database.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services