[Translation] implement SQL Server-based multi-level linkage drop-down list in infopath forms with browser Enabled

Translation: Implement Multi-level linkage drop-down list

Original article: http://blogs.msdn.com/infopath/archive/2006/10/12/cascading-dropdowns-in-browser-forms.aspx

 

If you use the office infopath client to create a solution, you can easily use the "filter" function to filter the values in the drop-down list box, however, the "filter" function cannot be used in browser-based templates. How can we implement the same function?

By creating a Web service with parameters, we can complete this impossible task. You can add these web service-based methods to the template in the form of "data connection, these queries are executed when the value of a drop-down list box changes. After the query is set, you can simply execute the "Data Connection" query to obtain the relevant data. In this example, you need to connect to the SQL server's northwind sample database and Visual Studio. First, let's create this web service, which includes two methods. Step 1: Open the website

1. Open
2. From the File menu, select open and then select Web Site)
3. Select "File System" and browseC: \ Program Files \ common files \ microsoft shared \ Web Server Extensions \ 12 \ template \ layouts

Note: In this example, the layouts folder is selected, which means that this Web Service is visible to all websites. If you want your web service to be visible to a specific website, you can open this directory. C: \ Inetpub \ wwwroot \ WSS \ virtualdirectories \ 80

4. Click Open)
5. In Solution Explorer, right-click the website and choose create a new folder.

6. Rename this folder namedWebServices
7. Considering that there may be other Web Services in this folder in the future, to facilitate management, we will create another sub-Folder:
   1. InWebServicesRight-click and select the new folder (new folder)
   2. Rename this folderNorthwindtables

Step 2: create a Web Service

1. Right-clickNorthwindtablesSelect Add new item)
2. In the template dialog box, select Web Service)
3. In the Name box, name the project as follows:Northwindtable. asmx

4. CancelCodePut it in a separate file "and click" OK.

Step 3: Add a web method Note: In this example, assume that the SQL Server database is installed on the same machine as Microsoft Office Sharepoint Server.

1. Add "using" to the Code as follows:

Using system. Data; using system. Data. sqlclient;

2. To add a web interface, read the customerid value from the northmers table of the northwind database:

[Webmethod] public dataset getmers MERs () {// create a connection to the northwind database sqlconnection Cn = new sqlconnection ("Data Source = (local); Integrated Security = sspi; initial catalog = northwind "); // create a data adapter object to execute SQL // statement to retrieve the customer ID values sqldataadapter da = new sqldataadapter (" select MERs. customerid from customers order by customerid ", CN); // create a dataset to store dataset DS = new dataset (); // open the connection to CN. open (); // fill data da. fill (DS, "MERs"); // close the connection CN. close (); Cn = NULL; da = NULL; return Ds ;}

3. Add the following web method, read the order information from the selected customer:

[Webmethod] public dataset getordersforselectedcustomer (string strcustid) {// create a connection to the database sqlconnection Cn = new sqlconnection ("Data Source = (local); Integrated Security = sspi; initial catalog = northwind "); // create a string variable to store the modified SQL statement string strorderssql = ""; // create a default SQL string strordersorigsql = "select * from orders"; // some customer ID values include single quotation marks. We need to // replace it with two single quotation marks, so that // The system can correctly identify strcustid = strcustid. replace ("'", "'' "); // Replace the default SQL statement with a query parameter. // And sort strorderssql = strordersorigsql + "where customerid like '%" + strcustid + "% 'order by orderid"; // create an adapter object to obtain the value of orderid. Sqldataadapter daorders = new sqldataadapter (strorderssql, CN); // create dataset to store data dataset DS = new dataset (); // open the connection to CN. open (); // fill in the Data daorders. fill (DS, "orders"); // clears the memory CN. close (); Cn = NULL; daorders = NULL; return Ds ;}

4. Build and save the project.

Step 4: test the Web Service Note: Application of this website Program The account that identifies the pool. You must have access to the SQL Server database.

1. Open a browser and accessHttp: // % 3 cserver % 3E/_ layouts/WebServices/northwindtables. asmx(Replace <Server> with your server name)
2. You can see the two methods we just created, and the defaultHelloworld:

3. ClickGetcustomersThen click invoke-it returns the value of a group of customer IDs.
4. ClickGetordersforselectedcustomer,InStrcustidEnter in the text box:BergsThen click query-it will return all the order numbers of the bergs.

Step 5: Create an infopath form

1. Design a new, empty, browser-compatible infopath form.
2. Add a drop-down list box and name it:Selectcustomer
3. Add another drop-down list box and name it:Selectorder

4. Add two "Accept data only" data connections and connect them to the Web Service of northwindtables.
   1. getcustomers :
      • enable the option" automatically retrieve data when a form is opened "(" automatically retrieve data when the form is opened ")
   2. getordersforselectedcustomer :
      • when you need to use the sample value, enter alfki as the strcustid parameter
      • cancel the option" automatically retrieve data when a form is opened "(" automatically retrieve data when the form is opened ")
6. Set the properties of the selectcustomer drop-down box. Its list items are from the external data source.GetcustomersSearch value, FieldCustomeridThe value and display name.
8. SetSelectorderThis drop-down box property, its list items fromGetordersforselectedcustomerSearch for the data source and useOrderidField as its value and display name.
10. In the selectcustomer drop-down box, create a rule to apply the following operations:
1. Set Domain value: SetSelectorderThe Domain value is null.
2. Set Domain value: Set the data sourceGetordersforselectedcustomerParameter Field (Strcustid) As the primary data sourceSelectcustomerThe value of the domain.
3. Use data connection for queryGetordersforselectedcustomer

8. Save the template to your local device. The file name isFilteredDrop-downs_IPFS.XSN

Step 6: publish this template

1. Publish this template to the server running infopath form services.
2. Use the browser to create a file library
3. InSelectcustomerSelectBergs
4. ClickSelectorder-Only the bergs order is displayed.
5. Select different customer IDs and check whether the order drop-down box is changed.

Translated by dosboy