You are currently offline, waiting for your internet to reconnect

How to use the Office Web Components with XML data generated by an XML Web service created using ASP.NET

This article was previously published under Q315695
SUMMARY
This step-by-step article describes how to use ASP.NET to build a simple XML Web service that retrieves data from the Microsoft Access sample Northwind database and returns the data as XML to a client when the client calls a method of the service. On the client side, the data is presented with the Office PivotTable and Chart components. This article also demonstrates how to build a client for the XML Web service by using either Visual Basic .NET or Visual Basic 6.0.

back to the top

Create a simple XML Web service using ASP.NET

  1. Start Visual Studio. NET.
  2. On the File menu, click New and then click Project. Under Project types click Visual Basic Projects, then click ASP.NET Web Service under
  3. Templates. Form1 is created by default.
  4. Change the Location to http://localhost/MyDataService and click OK. The XML Web service project is created on the local computer with the name MyDataService. Class Service1, which inherits from System.Web.Services.WebService, is created by default in the Service1.asmx file.
  5. In Solution Explorer, right-click Service1.asmx, and then select View Code. This displays the code-behind file for the .asmx page.
  6. Add a reference to the Microsoft ActiveX Data Objects library. To do this, follow these steps:
    1. On the Project menu, click Add Reference.
    2. Click the COM tab, select Microsoft ActiveX Data Objects 2.7 Library, click Select, and then click OK.
  7. Add the following method to Class Service1:
    <WebMethod()> Public Function GetResultsAsAdoXML() As String    Dim myAdoRs As ADODB.Recordset    Dim myAdoConnection As New ADODB.Connection()    Dim mypersiststream As New ADODB.Stream()    Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _       "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"    Dim mySelect As String = "SELECT * from [Category Sales for 1997]"    myAdoConnection.ConnectionString = myConnectionString    myAdoConnection.Open()    myAdoRs = myAdoConnection.Execute(mySelect)    myAdoRs.Save(mypersiststream, ADODB.PersistFormatEnum.adPersistXML)    Return mypersiststream.ReadTextEnd Function					
    Note If you did not install Office XP to the default folder (C:\Program Files\Microsoft Office), modify the connection string in the code to reflect the correct path for your Office installation. Also, if you have installed Office 2003, you may have to change the path accordingly.

  8. On the Build menu, click Build Solution to build the XML Web service.
back to the top

Test Your XML Web Service

  1. On the Debug menu, click Start. Microsoft Internet Explorer starts and browses to the Service1.asmx file.
  2. Click the GetResultsAsAdoXML hyperlink.
  3. Click Invoke to test the method call. A new window displays the results of the Category Sales for 1997 query in the XML rowset schema format. The rowset XML document is enclosed in the string element.
  4. On the Debug menu in Visual Studio .NET, click Stop Debugging.
back to the top

Build a Client for Your XML Web Service

This section describes how to create a client to consume the MyDataService XML Web service. You can create your client with either Visual Basic 6.0 or Visual Basic .NET.

For demonstration purposes, the steps describe how to build the test client on the same computer as the XML Web service. However, you can build your test clients on any computer that can browse to the Web Services Description Language (WSDL) file that the Web service exposes.

back to the top

Use Visual Basic .NET

  1. Create a new Visual Basic .NET Windows Application project. Form1 is created by default.
  2. Press CTRL+ALT+X to display the Toolbox.
  3. On the Tools menu, click Customize Toolbox. Select the following items in the components list, and then click OK:
    • Microsoft Office Chart 10.0
    • Microsoft Office Data Source Control 10.0
    • Microsoft Office PivotTable 10.0
    Icons for the components appear in the Toolbox.

  4. Add a Chart control, a PivotTable control, and a Data Source control to Form1. Size and position the form and controls as needed.
  5. Add a Button control to Form1, and then set the Text property of the control to Fill Data.
  6. Add a reference to the sample XML Web service. To do this, follow these steps:
    1. On the Project menu, click Add Web Reference. The Add Web Reference dialog box appears.
    2. In the Address text box, type http://localhost/MyDataService/Service1.asmx?wsdl and press ENTER. The WSDL file is displayed in the dialog box.

      Note: If MyDataService does not reside on the local computer, replace localhost in the address with the name of the Web server on which MyDataService resides.
    3. In the dialog box, click Add Reference.
  7. Double-click the Button control on Form1, and then replace the Button1_Click handler with the following code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click    'Reference to the service.    Dim objClient As New localhost.Service1()    'Result obtained from the Service method.    Dim strresult As String    'The results will be stored on this disk file.    Dim datafile As String    datafile = System.IO.Directory.GetCurrentDirectory() + "\\mydata.xml"    'Call the Web service method GetResultsAsAdoXML, which returns the recordset as XML.    strresult = objClient.GetResultsAsAdoXML()    Dim oxmldoc As New System.Xml.XmlDocument()    'Load the XML recordset into the XML Document object.    oxmldoc.LoadXml (strresult)    'Save it to disk file.    oxmldoc.Save (datafile)    'Get data in the DataSource component.    If Len(AxDataSourceControl1.ConnectionString) = 0 Then        AxDataSourceControl1.ConnectionString = "provider=mspersist"        AxDataSourceControl1.RecordsetDefs.AddNew( _              datafile, AxDataSourceControl1.Constants.dscCommandFile, "ChartData")    End If    'Get data in Chart component.    Dim c    Dim cht As OWC10.ChChart    Dim ser As OWC10.ChSeries    Dim ax As OWC10.ChAxis    c = AxChartSpace1.Constants    ' Clear the ChartSpace.    AxChartSpace1.Clear()    'DataSource to the Chart component.    AxChartSpace1.DataSource = AxDataSourceControl1.DefaultRecordset.DataSource    'Get the reference to Chart within the ChartSpace component.    cht = AxChartSpace1.Charts(0)    cht.Type = c.chChartTypeBarStacked    cht.HasLegend = True    cht.Legend.Position = c.chLegendPositionTop    cht.HasTitle = True    cht.Title.Caption = "Category Sales for 1997"    'Set the Chart data.    AxChartSpace1.SetData(OWC10.ChartDimensionsEnum.chDimCategories, 0, "CategoryName")    AxChartSpace1.SetData(OWC10.ChartDimensionsEnum.chDimValues, 0, "CategorySales")    ax = cht.Axes(c.chAxisPositionBottom)    'Set the series attributes.    ser = cht.SeriesCollection(0)    ser.Name = "Category Sales"    ser.Caption = ser.Name    ser.Marker.Size = 4    'Get Data in the PivotTable component.    Dim pview As OWC10.PivotView    AxPivotTable1.AutoFit = True    AxPivotTable1.ConnectionString = "provider=mspersist"    AxPivotTable1.CommandText = datafile    pview = AxPivotTable1.ActiveView    pview.AutoLayout()    pview.FilterAxis.Label.Visible = False    pview.RowAxis.Label.Visible = False    pview.ColumnAxis.Label.Visible = False    pview.TitleBar.Visible = FalseEnd Sub					
    Note If the XML Web service is on a separate computer, declare the objClient variable as follows:
     Dim objClient As New <servername>.Service1() 
    where servername is the name of the server on which the XML Web service resides.
  8. Note If you are using Office 2003, you may have to change the references accordingly.

  9. Add the following code to the top of Form1.vb:
    Imports OWC10 = Microsoft.Office.Interop.OWC					
  10. On the Debug menu, click Start to build and run the client program. Form1 appears.
  11. Click Fill Data to present data in the Chart and PivotTable controls.
back to the top

Use Visual Basic 6.0

XML Web services communicate with clients by using Simple Object Access Protocol (SOAP) messages. If the client application is SOAP-aware, it can call the methods that are exposed by the XML Web service. The client computers need not have Visual Studio .NET installed to call methods on the XML Web service. The client should have the ability to frame XML Web service method calls as SOAP requests and receive the results of the method calls as SOAP responses. The SOAP Toolkit allows you to do this.

For more information about the SOAP Toolkit, including download instructions, see the following Microsoft Developer Network (MSDN) Web site: To create a Visual Basic 6.0 test client to consume methods in the MyDataService XML Web service, follow these steps:

Note This test client requires that SOAP Toolkit 2.0 Service Pack 2 be installed on the computer.
  1. Start Visual Basic 6.0 and create a new Standard EXE project. Form1 is created by default.
  2. On the Project menu, click References. Add the following references to the project:
    • Microsoft Soap Type Library
    • Microsoft XML, v3.0
  3. On the Project menu, click Components. Select Microsoft Office XP Web Components, and then click OK.
  4. Add the following Office Web Components to Form1:
    • Microsoft Office Chart 10.0
    • Microsoft Office Data Source Control 10.0
    • Microsoft Office PivotTable 10.0
  5. Add a CommandButton control to Form1 and set the Caption property of the button to Fill Data.
  6. Double-click the CommandButton control and replace the Command1_Click handler with the following code:
    Private Sub Command1_Click()  Dim osoapClient As MSSOAPLib.SoapClient  'Initialize the SOAP client object.  Set osoapClient = CreateObject("MSSOAP.SoapClient")  osoapClient.mssoapinit "http://localhost/MyDataService/Service1.asmx?wsdl", "Service1", "Service1Soap"  'DOM object to load the results.  Dim oXMLDoc As New MSXML2.DOMDocument  Dim strdirname As String  'File to store XML recordset.  strdirname = FileSystem.CurDir$ + "\mydata.xml"  Dim strxmlrecordset As String  'Call the WebService method.  strxmlrecordset = osoapClient.GetResultsAsAdoXML()  'Load the XML in the DOM document.  oXMLDoc.loadXML strxmlrecordset  'Save it to the disk file.  oXMLDoc.Save (strdirname)  'Get the data into the DataSourceControl1 component.  If Len(DataSourceControl1.ConnectionString) = 0 Then     DataSourceControl1.ConnectionString = "provider=mspersist"     ' Add a RecordsetDef with name ChartData to the DataSourceControl1     DataSourceControl1.RecordsetDefs.AddNew strdirname, DataSourceControl1.Constants.dscCommandFile, "ChartData"  End If  'Get the Data into the Chart component.  Dim c  Dim cht As ChChart  Dim chart1 As ChChart  Dim ser As ChSeries  Dim ax As ChAxis  Set c = ChartSpace1.Constants  ChartSpace1.Clear  'DataSource to the Chart component.  ChartSpace1.DataSource = DataSourceControl1.DefaultRecordset.DataSource  ' Draw the chart.  Set cht = ChartSpace1.Charts(0)  cht.Type = c.chChartTypeBarStacked  cht.HasLegend = True  cht.Legend.Position = c.chLegendPositionTop  cht.HasTitle = True  cht.Title.Caption = "Category Sales for 1997"  'Set the chart data.  ChartSpace1.SetData chDimCategories, 0, "CategoryName"  ChartSpace1.SetData chDimValues, 0, "CategorySales"   ' Set the tick label spacing depending on the number of points plotted.  Set ax = cht.Axes(c.chAxisPositionBottom)  'Set the series attributes.  Set ser = cht.SeriesCollection(0)  ser.Name = "Category Sales"  ser.Caption = ser.Name  ser.Marker.Size = 4    'Get the data into the PivotTable.  PivotTable1.ConnectionString = "provider=mspersist"  PivotTable1.CommandText = strdirname  PivotTable1.AutoFit = True  Set pview = PivotTable1.ActiveView  pview.AutoLayout  pview.FilterAxis.Label.Visible = False  pview.RowAxis.Label.Visible = False  pview.ColumnAxis.Label.Visible = False  pview.TitleBar.Visible = FalseEnd Sub					
    Note If the XML Web service resides on a separate computer, replace localhost in the XML Web service address with the name of the server on which the XML Web service resides.

  7. Press F5 to build and run the program. Form1 appears.
  8. Click Fill Data to present data in the Chart and PivotTable components.
back to the top
REFERENCES
For more information about XML Web services that are created by using ASP.NET, click the following article number to view the article in the Microsoft Knowledge Base:
301273 How to write a simple Web service by using Visual Basic .NET
For more information about XML Web services that are created by using ASP.NET, see the "Programming the Web with Web Services" topic in the Visual Studio .NET Help or the "ASP.NET Web Services and ASP.NET Web Service Clients" topic in the Microsoft .NET Framework Developer's Guide.

For additional information about Web service essentials and XML Web services that are created by using ASP.NET, visit the following Microsoft Developer Network (MSDN) Web site:For additional information about Microsoft Office development with Visual Studio and Office XP Web Components, visit the following MSDN Web site:For more information about Office XP Web Components, click the following article numbers to view the articles in the Microsoft Knowledge Base:
286212 How to use an XML DataSource with the Office Chart component
235542 How to use the PivotTable Office Web component with VB
back to the top
ASP .NET OWC Chart VB Office webservice
Properties

Article ID: 315695 - Last Review: 03/29/2007 10:01:00 - Revision: 5.4

Microsoft Office 2003 Web Components, Microsoft Office XP Web Components, Microsoft Web Services (included with the .NET Framework) 1.0, Microsoft ASP.NET 1.0, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft .NET Framework Class Libraries 1.0

  • kbhowtomaster kbofficewebchart kbofficewebpivot KB315695
Feedback
html>html>