In-depth introduction to InfoPath-how to reference data in SQL SERVER in InfoPath forms

1. Why do we need to reference background data?

When you open a browser-based form, some drop-down controls in the form, such as "product type" and "material type", you may not want the reader to input them by yourself, instead, you want to directly read the "item type table" from the system's original SQL SERVER.

Alternatively, you want to complete some "auto-fill" operations. For example, after entering the "customer name", the "customer address" column will be automatically filled in based on the original system data.

 

2 What should I do if I reference background databases based on InfoPath Form Service?

Based on Web forms, according to the SDK documentation, you can only reference "read-only" instead of submitted data. That is to say, if you want to directly backfill the form to the database, you cannot directly use Infopath's code-free design. You must do Event Handler, workflow, or use InfoPath instead.

3. What does InfoPath Form Service do in this process?

When you open this Form, the InfoPath Form Service ServerYourselfCurrent user identity,Log on to the SQL Server and use SQL to retrieve the data. Then, it is presented to the user through a browser.

Yourself:This is important because it indicates that when the InfoPath form is opened, the server itself reads the data instead of using the IE client.

Current user identity:This is more important because you need to create a user with the "user name" as the login name in "security" and "user" of SQL SERVER and database, and give the user the architecture and permissions of Sys_DataReader.

*I didn't realize this problem. After debugging with Admin, I handed it to the user and failed to use it. It wasted a morning.

4. An additional task:

You need to adjust the website structure, "All website content"-> "new"-> "Data Connection Library"

As mentioned above, go to SQL SERVER and add the users who will use this form to the database and grant the read permission.

This is mainly used to store SQL connection files.

5 main steps:

"Tools"-> "Data Connection"-> "add"-> select "Accept data only" under "New Connection" in the Data Connection Wizard and click "Next"-> select "Database "(SQL Server only) "-> click" select database "->" Select data source "will pop up. You can" Create an SQL connection "and a data connection wizard will pop up, if you are prompted to create an SQL connection and "connect to the specified table" (the step is omitted), after completing these steps, your "Data Source" will have one "secondary data source" besides "primary ", the name is the name set for the connection you just created.

ImportantStep 1. After completing these steps, go to the "Data Connection database" (the name can be any name when you create it ), there is a "my data source" under "My Documents" on your computer. The data connection file you just created through InfoPath is generally an ODC extension. Upload this file to the library.
Then, choose tools> data connection> select your SQL data source and click convert... ", you can specify the data connection file in the dialog box. Click" Browse "and select the uploaded file in your SharePoint Data Connection Library.
ImportantStep 1: Go to the Data Connection Library and set all files in the database with an Odc and a udcx extension (generated during conversion) to "approve"
6. How to connect data to the control:

Take the list as an example: double-click the list control. On the "data" tab, select "Search for data from external data sources". Select the following as prompted. Click OK.

You can also use the "Rules". For example, you can use the "value assignment" rule in the "user number" column to automatically fill in the "user name:

When the user number is changed,

Assign the "user name" field value of the auxiliary data source to the "user name" text box.

The filtering condition is that the "user number" field value of the secondary data source is the "user number" text box.

7. Press the normal program to publish it to the InfoPath form library and pull it.

 

Whether your InfoPath form is connected to the database through Web services or directly to the database, this data connection file is stored in the xsn template.

Not all of them need to convert this Data Connection file to a UDC file (Universal Data Connection). Only when your InfoPath form needs to be opened in a browser on a SharePoint site, we need to convert the data connection file into UDC to ensure that the data can be submitted successfully.