You cannot create a form from a Web service that returns an ADO.NET dataset in InfoPath 2003

When an InfoPath 2003 form is created from a Web service that returns an ADO.NET DataSet that is named System.Data.DataSet, you may receive the following error message:

InfoPath 2003 cannot directly use ADO.NET DataSets. InfoPath 2003 works with generic XML payloads. ADO.NET DataSets that are returned by Web services are serialized as a special XML format that prevents the ADO.NET DataSets from working directly with InfoPath 2003.

The problem may occur for any of the following reasons:

• In the Web Service Description Language file that defines the methods and the properties of a Web service, the DataSet type is represented by an open schema tag, <xsd:any />. InfoPath 2003 can infer the schema of an XML document that is returned by a Web service. However, InfoPath 2003 must make a sample call to the Web service to infer the schema of an XML document.
• An ADO.NET DataSet contains an inline schema that describes the data that is stored in the ADO.NET DataSet. InfoPath 2003 does not support inline schemas.
• The data in an ADO.NET DataSet is wrapped in a <diffgr:diffgram> XML element that is not described in the inline schema. The XML elements that contain the data of the ADO.NET DataSet also have additional attributes that are not described in the schema. Because the data does not match the schema, the data cannot be validated in the InfoPath 2003 form.
• InfoPath 2003 does not have built-in support for change tracking that is similar to ADO.NET DataSet change tracking. For example, an ADO.NET DataSet tracks the fields and the rows that have been added, changed, or deleted since changes were last accepted. This is the purpose of the diffgram element and of the diffgr namespace in the XML Stream. To support change tracking, InfoPath 2003 would have to do either of the following:
  • Include the .NET Framework and host a DataSet object to manage the XML data
  • Re-implement the functionality
  Neither of these options is available with InfoPath 2003.

This problem is resolved in Microsoft InfoPath 2003 Service Pack 1 (SP1). To resolve this problem, obtain the latest service pack for Microsoft Office 2003. InfoPath 2003 SP1 fully supports typed datasets and lets you create a form from a Web service that returns an ADO.NET DataSet in InfoPath 2003.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
To work around this problem, create a new Web service method that uses the ADO.NET DataSet that is returned from the original Web service method. Then, remove the inline schema, the diffgram elements, and the attributes. Return the cleaned XML to InfoPath 2003. The "Workaround" section describes two possible implementations of this workaround.

This behavior is by design.

Steps to reproduce the problem

1. Start Microsoft Visual Studio .NET. Create a new Microsoft Visual Basic ASP.NET Web service project. Change the name of this project to NorthwindDataSet.
   
   You can change the name of the NorthwindDataSet project by changing the location to http://localhost/NorthwindDataSet.
2. On the Project menu, click Add Web Service. Name the new Web service CustomerInfo.asmx, and then click Open.
3. Add the following code to the new CustomerInfo.asmx Web service:

     '************************************************************************
       '* GetCustomerInfo -- This method retrieves information from the 
       '* Customer table of the Northwind database and then returns the information in an
       '* ADO.NET DataSet.
       '* Parameters: CustomerID is the string that contains the CustomerID of the 
       '*                           customer to retrieve the information for.
       '* Returns: an ADO.NET DataSet that contains information about the customer
       '* **********************************************************************
       <WebMethod()> _
       Public Function GetCustomerInfo(ByVal CustomerID As String) As System.Data.DataSet
   
           Try
   
               'Create a Microsoft SQL Server connection to the local computer that is running SQL Server.
               Dim theConnection As System.Data.SqlClient.SqlConnection
               theConnection = New SqlConnection
               theConnection.ConnectionString = "Data Source=(local);" & _
                   "Integrated Security=SSPI;Initial Catalog=northwind"
   
               'Create an SQL command to query the data.
               Dim theCommand As System.Data.SqlClient.SqlCommand
               theCommand = New SqlCommand
               theCommand.Connection = theConnection
               theCommand.CommandText = "SELECT CustomerID,CompanyName," & _
                   "ContactName,ContactTitle,Address,City,Region," & _
                   "PostalCode,Country,Phone,Fax FROM Customers " & _
                   "WHERE CustomerID='" & CustomerID & "'"
   
               'Create an SQL DataAdapter to read the data.
               Dim theDataAdapter As System.Data.SqlClient.SqlDataAdapter
               theDataAdapter = New SqlDataAdapter
               theDataAdapter.SelectCommand = theCommand
   
               'Open the command and then read the data.
               theConnection.Open()
               Dim theCustomerInfo As System.Data.DataSet = New DataSet
               theCustomerInfo.DataSetName = "CustomerInfo"
               theCustomerInfo.Namespace = "http://localhost/NorthwindDataSet/CustomerInfo"
               theDataAdapter.Fill(theCustomerInfo, "Customers")
   
               'clean up
               theConnection.Close()
   
               'Return the result.
               Return theCustomerInfo
   
           Catch ex As Exception
   
               Return Nothing
   
           End Try
       End Function

   The sample code tries to connect to a computer that is running SQL Server on the same computer as the Web service. Alternatively, you can change the connection string that is used by the System.Data.SqlConnection object to connect to a different computer that is running SQL Server. You may have to configure the SQL Server permissions to permit the Web service to access the database.
   
   For additional information about how to configure the SQL Server permissions, click the following article number to view the article in the Microsoft Knowledge Base:
   815154 � How to configure SQL Server security for .NET applications
4. At the top of the CustomerInfo.asmx.vb page, add the following code to the Imports statements:

    Imports System.Data.SqlClient 

5. Compile the NorthwindDataSet project.
6. Start InfoPath 2003. On the File menu, click Design a Form.
7. In the Design a Form task pane, click New from a Data Source.
8. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
9. Click Receive Data, and then click Next.
10. Type the URL to the CustomerInfo Web service, and then click Next.
    
    The URL to the CustomerInfo Web service may be similar to the following:
    
    http://localhost/NorthwindDataSet/CustomerInfo.asmx
11. Select the GetCustomerInfo method for the operation, and then click Next.
12. Click the s0:CustomerID parameter, and then click Set Sample Value. In the Set Value dialog box, type ALFKI, and then click OK.
13. Click Next.
    
    You may receive the error message that is mentioned in the "Symptoms" section.

Workaround 1
Remove the inline schema and the incompatible XML attributes

Workaround 1 has an advantage. Workaround 1 is simple to implement. Additionally, the XML data that the original Web service returns may change. Workaround 1 requires no changes to work with the new XML data.

However, Workaround 1 also has a disadvantage. Workaround 1 does not describe the schema of the XML data. Therefore, InfoPath 2003 must infer the schema from the sample data. If the sample data does not contain all the possible elements and all the possible attributes that the Web service method can return, the schema that InfoPath 2003 infers will not contain those elements and those attributes. If you run a query that returns elements or attributes that are not in the sample call, InfoPath 2003 displays an error.

1. In Visual Studio .NET, open the NorthwindDataSet project.
2. On the Project menu, click Add Web Service. Name the new Web service IPCustomerInfo.asmx, and then click Open.
3. Add the following code to the new IPCustomerInfo.asmx Web service:

       '************************************************************************
       '* GetCustomerInfoNoSchema: This method calls the GetCustomerInfo
       '* method of the CustomerInfo.asmx Web service and then strips the 
       '* inline schema from the resulting DataSet.
       '* Parameters: CustomerID is the string that contains the CustomerID of the 
       '*                           customer to retrieve information for.
       '* Returns: an XML document with no inline schema
       '* **********************************************************************
       <WebMethod()> _
       Public Function GetCustomerInfoNoSchema(ByVal CustomerID As String) As System.Xml.XmlDocument
           'Get the core data.
           Dim theCustomerInfoService As CustomerInfo
           theCustomerInfoService = New CustomerInfo
   
           Dim theDataSet As System.Data.DataSet
           theDataSet = theCustomerInfoService.GetCustomerInfo(CustomerID)
   
           'Create a new System.Xml.XmlDocument from the data of the DataSet.
           Dim theDocument As System.Xml.XmlDocument
           theDocument = New System.Xml.XmlDocument
           theDocument.LoadXml(theDataSet.GetXml())
   
           'Return the result.
           Return theDocument
       End Function

   The sample code uses the original Web service to provide an ADO.NET DataSet and then creates a new XML document with no inline schema information.
4. Compile the NorthwindDataSet project.
5. Start InfoPath 2003. On the File menu, click Design a Form.
6. In the Design a Form task pane, click New from a Data Source.
7. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
8. Click Receive Data, and then click Next.
9. Type the URL to the IPCustomerInfo Web service, and then click Next.
   
   The URL to the IPCustomerInfo Web service may be similar to the following:
   
   http://localhost/NorthwindDataSet/IPCustomerInfo.asmx
10. Select the GetCustomerInfoNoSchema method for the operation, and then click Next.
11. Click the s0:CustomerID parameter, and then click Set Sample Value. In the Set Value dialog box, type ALFKI, and then click OK.
12. Click Next, and then click Finish.
13. Move the CustomerID field from the queryFields group in the Data Source task pane, and then add the CustomerID field to the Query view.
14. Move the Customers group from the dataFields group in the Data Source task pane, and then add the Customers group to the Data Entry view. Click Section with controls.
15. Preview the form. Test the form.
    
    Notice that for some customer IDs such as QUEEN, the query encounters errors because the sample data does not contain all the possible elements and all the possible attributes that the Web service can return.

Workaround 2
Create a strongly-typed wrapper class to serialize the XML data of the ADO.NET DataSet

Workaround 2 is more difficult to implement than Workaround 1. Additionally, Workaround 2 must be tailored to each Web service method that Workaround 2 is used with. The Web service method must not change the schema of the data that the Web service method returns. However, Workaround 2 describes the schema of the XML data that Workaround 2 returns. InfoPath 2003 does not have to infer the structure of the data. Therefore, InfoPath 2003 forms that are created by using Workaround 2 are not susceptible to errors that are caused by optional elements and optional attributes.

1. Start Microsoft Internet Explorer. Move to the URL of the CustomerInfo Web service test page. The URL of the CustomerInfo Web service test page may be similar to the following:
   http://localhost/NorthwindDataSet/CustomerInfo.asmx
2. Click GetCustomerInfo to move to the test page for that method.
3. In the CustomerID text box, type ALFKI, and then click Invoke.
4. From the XML that results, copy the <xs:schema> element and all the children of the <xs:schema> element. Paste the <xs:schema> element and all the children of the <xs:schema> element in Notepad.
5. Remove the hyphen (-) characters from the pasted text. Save the document as CustomerInfo.xsd.
6. Open a Visual Studio .NET command prompt. Move to the directory where you saved CustomerInfo.xsd.
7. Use the following code to create a wrapper class from the schema file:

   xsd.exe CustomerInfo.xsd /c /l:vb

8. In Visual Studio .NET, open the NorthwindDataSet project.
9. On the Project menu, click Add Existing Item.
10. Move to the CustomerInfo.vb file that you created with the Xsd.exe tool, and then click Open.
11. Add the CustomerInfoWrapper namespace around the CustomerInfo class and around the CustomerInfoCustomers class.
12. Add the following code to the IPCustomerInfo.asmx Web service. The following code uses the original Web service to provide an ADO.NET DataSet and then creates a new XML document with no inline schema information.

         '************************************************************************
        '* GetCustomerInfoWrapper: This method calls the GetCustomerInfo
        '* method of the CustomerInfo.asmx Web service and then strips the 
        '* inline schema from the resulting DataSet.
        '* Parameters: CustomerID is the string that contains the CustomerID of the 
        '*                           customer to retrieve information for.
        '*                        The wrapper class is exampleData. You fill the wrapper class with the data from the
        '*                           ADO.NET DataSet.
        '* Returns: none
        '* **********************************************************************
        <WebMethod()> _
        Public Sub GetCustomerInfoWrapper(ByVal CustomerID As String, _
            ByRef exampleData As CustomerInfoWrapper.CustomerInfo)
    
            'Get the core data.
            Dim theCustomerInfoService As CustomerInfo = New CustomerInfo
            Dim theDataSet As System.Data.DataSet = theCustomerInfoService.GetCustomerInfo(CustomerID)
            theDataSet.Namespace = "http://localhost/NorthwindDataSet/CustomerInfo"
    
            'Create an in-memory stream to write the DataSet to.
            Dim theStream As System.IO.MemoryStream = New System.IO.MemoryStream
    
            'Write the DataSet to the stream.
            theDataSet.WriteXml(theStream, XmlWriteMode.IgnoreSchema)
    
            'Move the seek pointer of the stream back to the beginning, or 
            'deserialization may fail.
            theStream.Seek(0, System.IO.SeekOrigin.Begin)
    
            'Create an XML Serializer to read the DataSet.
            Dim ser As System.Xml.Serialization.XmlSerializer
            ser = New System.Xml.Serialization.XmlSerializer(exampleData.GetType)
    
            'Deserialize a CustomerInfo wrapper from the stream.
            exampleData = ser.Deserialize(theStream)
            'Return
    
        End Sub

13. Compile the NorthwindDataSet project.
14. Start InfoPath 2003. On the File menu, click Design a Form.
15. In the Design a Form task pane, click New from a Data Source.
16. In the Data Source Setup Wizard dialog box, select Web Service as the type of data source, and then click Next.
17. Click Receive Data, and then click Next.
18. Type the URL to the IPCustomerInfo Web service, and then click Next.
    
    The URL to the IPCustomerInfo Web service may be similar to the following:
    
    http://localhost/NorthwindDataSet/IPCustomerInfo.asmx
19. Select the operation that is used by the Web service to provide the XML data to the form, click the GetCustomerInfoWrapper method, and then click Next.
    
    Notice that InfoPath 2003 does not prompt you to specify sample values for the Web service method.
20. Click Finish.
21. In the Data Source task pane, select the CustomerID field from the queryFields group, and then move CustomerID to the Query view of the form.
22. In the Data Source task pane, select the Customers group from the dataFields group, and then move Customers to the Data Entry view of the form. Click Repeating Section with controls.
23. Preview the form. Test the form.
    
    Notice that any valid customer ID works in this form.