How To: Use the Estimates Sample When the Database and the Web Server Are on Different Computers

The Estimates sample that is included with Microsoft Visual Studio Tools for the Microsoft Office System describes how to use managed code extensions and how those managed code extensions can work together with Microsoft SQL Server databases and Web services. The instructions for the Estimates sample assume that both your installation of SQL Server and your installation of Web server are local to your development computer. This article describes the steps and the code changes that you can use for the Estimates sample when your SQL Server and your Web Server are on computers other than the development computer.

The steps in this article assume the following:

• Microsoft SQL Server 2000 or Microsoft SQL Server Desktop Engine (MSDE) 2000 is installed on the computer that you will use for the sample database.
• Internet Information Services (IIS) is installed on the computer that you will use for the sample Web service.
• Microsoft Visual Studio .NET 2003, Visual Studio Tools for Office, and Microsoft Office 2003 System are installed on the development computer where you will build the solution and then run the Office managed code extension.

Before you complete the steps in this article, you must first copy all files for the Estimates sample from the Visual Studio Tools for Office documentation to the development computer. The default location for the files for the Estimates sample is:

%userprofile%\My Documents\Samples\Office Projects\Estimates\Database

Note The steps and the code that are illustrated in this article use the SQL Server name MySQLServer and the Web server name MyWebServer. You must replace MySQLServer and MyWebServer with the names of your servers.

Set up the SQL Server Database

1. Start a command prompt.
2. At the command prompt, change to the directory that contains the Database subfolder. An example follows:
   
   cd %userprofile%\My Documents\Samples\Office Projects\Estimates\Database
3. Run Setup.bat by using the name of your computer that is running SQL Server as the parameter. An example follows:
   
   setup.bat MySQLServer
4. Close the command prompt window.

Build and Deploy the ContosoQuote Web Service

1. Create a new Microsoft ASP.NET Web service in Microsoft Visual Basic .NET as follows:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the Project Types list, select Visual Basic Project. In the Templates list, select ASP.NET Web Service.
   4. In the Location box, type http://localhost/ContosoQuote, and then click OK.
   5. On the File menu, click Close Solution.
2. Copy all the files that are in ..\Samples\Office Projects\Estimates\ContosoQuote to the folder that contains your new Web service project.
   
   Note The default location for your new Web service project is C:\Inetpub\wwwroot\ContosoQuote.
3. Double-click C:\Inetpub\wwwroot\ContosoQuote\ContosoQuote.sln to open the solution in Visual Studio .NET.
4. In Microsoft Solution Explorer, right-click ContosoQuote.asmx, and then click View Code.
5. Locate the following connection string in the InitializeConnection method in ContosoQuote.asmx:

   	Private Sub InitializeConnection()
   		Me.dbConnection = New SqlConnection("Integrated Security=SSPI;" + _
   				"Initial Catalog=EstimatesSample;Data Source=localhost;")
   	End Sub

   Replace localhost in the connection string with the name of the database server where you created the EstimatesSample database:

   	Private Sub InitializeConnection()
       		Me.dbConnection = New SqlConnection("Integrated Security=SSPI;" + _
       				"Initial Catalog=EstimatesSample;Data Source=MySQLServer;")
   	Sub

6. On the Build menu, click Rebuild Solution.
7. Deploy the Web service to your Web server:
   1. In Solution Explorer, click the ContosoQuote project.
   2. On the Project menu, click Copy Project.
      
      Provide the URL to your Web server as the destination. For example, type http://MyWebServer/ContosoQuote, and then click OK.
8. On the File menu, click Close Solution.

Build the Managed Code Extension

1. On the File menu, click Open Solution to open the Estimates solution.
   
   The default location is ..\Samples\Office Projects\Estimates\Estimates.sln.
2. In Solution Explorer, right-click the ContosoQuote project, and then click Remove. Click OK when you are prompted to confirm the deletion.
3. Update the Web References for each of the projects in the Estimates solution. For each project in the solution, follow these steps:
   1. In Solution Explorer, double-click the project.
   2. Double-click Web References, and then select Contoso.
   3. In the Properties window, change Web Reference URL to http://MyWebServer/ContosoQuote/ContosoQuote.asmx.
      
      Note If the Properties window does not appear, press F4 to make the window appear.
4. In the ThisWorkbook.vb module of the Estimates project, locate the connection string in the InitializeConnections method:

   	Private Sub InitializeConnections()
   		Me.dbConnection = New SqlConnection("Integrated Security=SSPI;" + _
   				"Initial Catalog=EstimatesSample;Data Source=localhost;")
   	End Sub

   Replace localhost with the name of the database server where you created the EstimatesSample database:

   	Private Sub InitializeConnections()
       		Me.dbConnection = New SqlConnection("Integrated Security=SSPI;" + _
       				"Initial Catalog=EstimatesSample;Data Source=MySQLServer;")
   	Sub

5. In Solution Explorer, right-click QuoteSelector.vb, and then click View Code.
6. Locate the connection string for OleDbConnection1 in the InitializeComponent method:

   Me.OleDbConnection1.ConnectionString = _
           "Provider=SQLOLEDB;Integrated Security=SSPI;" + _
           "Initial Catalog=EstimatesSample;Data Source=(local);"
   

   Change the name for the database server as follows:

   Me.OleDbConnection1.ConnectionString = _
           "Provider=SQLOLEDB;Integrated Security=SSPI;" + _
           "Initial Catalog=EstimatesSample;Data Source=MySQLServer;"
   

7. Grant full trust to the directory that is named ..\Samples\Office Projects\Estimates\* by adding the directory to the OfficeProjects code group.
   
   For additional information, see the "How to: Grant Permissions to Folders and Assemblies" topic in the Visual Studio Tools for Office documentation.
8. Change the Start Action property of the project to start Microsoft Excel, and then change the command line arguments to point to the Estimates.xls workbook.
   
   For additional information, see the "A Project with an Output Type of Class Library Cannot Be Started Directly" topic in the Visual Studio Tools for Office documentation.
9. In Microsoft Windows Explorer, mark Estimates.xls as read-only so that it acts as a template.

Try It Out

1. Press F5 to build and to run the solution.
2. To test the solution, follow the steps in the "How the Document Works" section of the Estimates topic.