Retrieving Data and Creating Records in the Data Interop Developer Sample Form [InfoPath 2003 SDK Documentation]
Applies to:
Microsoft Office InfoPath 2003
For information on working with master/detail controls, see "About master/detail controls" in the InfoPath 2003 Help.
In the Data Interop developer sample form, Query view, which was automatically generated by the Data Source Setup Wizard, allows a user to retrieve data from the data source based on a specified employee. After the data is retrieved, Microsoft Office InfoPath 2003 opens Orders view and populates the fields with the data.
The primary features of Query view in the Data Interop developer sample form are the action associated with the Query button and the secondary data source used for the drop-down list box.
Filling lists with secondary data sources
A common scenario for InfoPath forms that are connected to a database is to populate drop-down list boxes from which users can choose data-entry options or specify query criteria. In the Data Interop developer sample form, a drop-down list box in Query view allow users to select the employee for whom they wish to view order information. The data that the drop-down list box contains is retrieved from the Microsoft SQL Server database that is used as the data source for the form.
When you create a drop-down list box that is based on data contained in a database, you must create a secondary data source that will be used to retrieve the data. Secondary data sources can get data from the same data source that is used for the form, or they can get data from a different source of data. To create a secondary data source, right-click the drop-down list box control while in design mode, and then click Drop-Down List Box Properties to open the control's Properties dialog box. In the List box entries section on the Data tab, select Look up in a database, Web service, or file, and then click Secondary Data Source to open the Secondary Data Sources dialog box.
You can use the Secondary Data Sources dialog box to add, modify, or remove secondary data sources. When you add or modify a secondary data source, InfoPath opens the Data Source Setup Wizard, which allows you to specify the data source and SQL query statement that will be used to retrieve the data. This process is the same as the process for creating a primary data source for a form.
After you have created a secondary data source and returned to the Drop-Down List Box Properties dialog box, you click the Select XPath button next to the Entries box to specify the XPath expression that is used to associate the drop-down list box with a field in the secondary data source.
When you create a secondary data source, InfoPath stores information about it in the form definition (.xsf) file using the dataObjects element, which is a child element of the xDocumentClass root element. The name attribute of the dataObject element, which is a child element of the dataObjects element, contains the name of the secondary data source, and the schema attribute contains the name of the XML Schema that is created by InfoPath in the Data Source Setup Wizard. The query element of the dataObject element is used to specify the data adapter used for the secondary data source, which, in the Data Interop sample form, is specified with the adoAdapter element. The adoAdapter element contains information about the data source, including its connectionString attribute and the commandText attribute used for the SQL query statement.
The following is a section of the .xsf file for the Data Interop sample form that defines the EmployeeNames secondary data source:
<xsf:dataObjects>
<xsf:dataObject name="Employees" schema="Employees.xsd" initOnLoad="no">
<xsf:query>
<xsf:adoAdapter connectionString="Provider=SQLOLEDB;Persist Security Info=True;Initial Catalog=Northwind;Data Source=YourSQLServer;Integrated Security=SSPI",
commandText="select "EmployeeID","LastName","FirstName" from "dbo"."Employees" as "Employees" order by "FirstName", "LastName"",
queryAllowed="yes">
</xsf:adoAdapter>
</xsf:query>
</xsf:dataObject>
. . .
</xsf:dataObjects>
After you create your secondary data source, you can use the DataObjects collection to programmatically access the properties and methods of the DataObject objects in the InfoPath object model. For example, in the Data Interop developer sample form, the LoadAuxiliaryDOMs method is used to populate the secondary data sources during the XDocument::OnLoad event:
XDocument.DataObjects.Item("Employees").Query();
XDocument.DataObjects.Item("Customers").Query();
XDocument.DataObjects.Item("Products").Query();
After you have called the Query method to update the data in the drop-down list box, you can use the ForceUpdate method of the View object, accessed through the XDocument object, to update the data in the view:
XDocument.View.ForceUpdate()
In addition to the Query method, the Data Interop developer sample form also makes use of the Command property of the QueryAdapter object associated with the DataObject to modify the query that populates the secondary data source. The following lines in the LoadSalesSummaryData function set the Ending Date parameter of the SQL query for the Sales Summary by Year view:
dtEnd = new Date();
dtEnd.setDate(dtEnd.getDate() + 1);
strSqlCommand =
XDocument.DataObjects.Item("SalesSummary").QueryAdapter.Command;
strSqlCommand = strSqlCommand.replace(/@Ending_Date='[^']+'/, "@Ending_Date='"
+ SqlDateTime(dtEnd) + "'");
XDocument.DataObjects.Item("SalesSummary").QueryAdapter.Command =
strSqlCommand;
XDocument.DataObjects.Item("SalesSummary").Query();
Going offline
People often need to work while disconnected from their corporate networks and data sources. The Data Interop developer sample form supports offline use through its ability to cache database data. A salesperson, for example, who is about to visit a customer, launches the Data Interop solution, loads his or her orders, and clicks Online/Offline on the toolbar. Part of the database is cached in memory on the local computer, so the salesperson can enter new orders and list, search, or edit existing orders that he or she placed while at the customer site.
When switching to Offline use, the GoOnlineOffline custom function calls the CacheAuxiliaryDoms function to store Employees, Products, and Customers lists locally, then downloads report data by calling the LoadSalesSummaryData function.
The following lines in the CacheAuxiliaryDoms function cache the current data from the Employees secondary data source.
objOfflineDataNode = XDocument.DOM.selectSingleNode("/dfs:myFields/my:OfflineData");
objNewOfflineDataNode = objOfflineDataNode.cloneNode(false);
objNode = XDocument.DOM.createNode(1, "Employees",
"http://schemas.microsoft.com/office/infopath/2003/myXSD/2003-02-22T00:54:56");
objNode.appendChild(XDocument.GetDOM("Employees").documentElement.cloneNode(true));
objNewOfflineDataNode.appendChild(objNode);
Note The GoOnlineOffline function contains a conditional logic flaw that may result in loss of data. When you click the Online/Offline button on the toolbar, an alert displays the following warning: "You must save your work in order to work offline." After clicking OK, however, the form switches you to offline mode and any previous work in memory is lost.
When switching from offline to online use, the user is prompted to upload all local changes to the database by calling the Submit method of the XDocument object.