Infopath: Preface and simple reading of SQL or list data

Preface:

Infopath is an epoch product that designs forms from the originalCodeThe design is independent, making the form a data form insteadProgram.

The infopath advanced series is an important part of my design of infopath forms, these include reading SQL or list data, using SQL statements to read SQL data, using WebService to read list data, writing duplicate table data to SQL, and reading SQL to duplicate table.

This advanced series allows readers to use several excellent articlesArticleYou can freely control the interoperability between infopath, Sharepoint list, and SQL Server.

Required skills:

To read this series of articles, you must have a basic understanding of infopath and use infopath to create a simple form template.

Required software:

Vsta, which is not installed when the Office is installed by default.

However, this can be found in the installation package of Office 2007, open the installation program of Office 2007, and find the relevant installer in the section infopath, which will not be repeated here.

To facilitate program debugging, you can install an Express version of SQL server. If you have installed SharePoint 2007, it will automatically install an Express version of SQL. You can download an SQL Server Management studio on Microsoft's official website to connect to and manage databases.

1. Simple SQL or List Data Reading

Example: How to Use infopath to retrieve all data in a Sharepoint list and all data in an SQL table.

The purpose of this section is not to allow readers to retrieve a column of data from a Sharepoint list or an SQL table as a drop-down box. Instead, we will teach readers more concepts so that they can freely define them to help them understand more concepts.

Editing recommendations read the following articles

• No related articles

 

Important concepts:

Data Source and Data Connection

In the infopath form template, we can define two important concepts: data source and data connection.

Data source:

By default, if there is no external connection, the infopath template will customize a primary data source called maindatasource, which starts with the prefix "my. If you save the form data, it means that only the data of this primary data source is saved. Any other defined data source is called a secondary data source, which means that other data sources only have data during the form "run". Once the form is closed and saved, it disappears into the memory.

Data Connection:

You can define a lot of data connections, such as from an XML file, SQL, and list. Once this data connection is executed, it will generate a data source that corresponds to this data connection.

Ps important note: This example runs on the local machine. However, if you publish a template to a website and use browser-based forms service, pay attention to permission issues, make sure that your IIS application account can read, update, and write data to your local SQL database.

1 define a form, such:

View the source image (large image)

The Application Department must retrieve data from a Sharepoint list named: department personnel.

2. Create a department list for the example

For example, create a custom list with only two columns, name and department. Fill in the following sample data. The name column is changed from "title.

Editing recommendations read the following articles

• No related articles

 

3. Create a data connection to the list:

1) Click "manage data connection" on the "Data Source" Panel and click "add", as shown in the following figure:

2) then, for example, select the Sharepoint list:

3) enter a Sharepoint website URL to go to the list selection page. For example, select a department staff:

View the source image (large image)

4) Select a domain:

If you only select a department,

5) Click "Next". Click "select" as shown in the following figure, because we want to fill the Department in when opening the form.

Editing recommendations read the following articles

• No related articles

 

4. Set the properties of the drop-down box:

For example, set the properties of the drop-down box:

Only entries with unique display names must be checked. Because the Department values are repeated, duplicate values are generated without being checked.

5. runtime interface:

View the source image (large image)

6. Create an SQL table and try to read data from SQL. If you create a table, you can copy the data from the Sharepoint list.

1) first create a database. This database can be called demo.

2) create a table Named Department personnel. The table has two fields, including the name and department, for example:

3) Fill in the sample data, and the data can be copied from the Sharepoint list.

7. Create an SQL data connection in infopath

1) set a "Accept data only" connection

Editing recommendations read the following articles

• No related articles

 

2) "Where to accept data", such:

3) configure the data connection file: click "select database" to configure a connection file

View the source image (large image)

4) in the configuration source, select "New sqlserver" connection

View the source image (large image)

Enter the server name of the local machine. This is OSS. Replace it with your own server name.

Select the database, and select the demo we just created.

 

5) then, you can see in the following dialog box that we selected both fields at this time:

6) Click Next directly until the task is completed. The data connection is automatically Named Department staff 2 because a data connection is established as follows.

7) reset the drop-down box to set the SQL data source:

View the source image (large image)

8) preview, with the same effect: