Export (0) Print
Expand All

Visual Studio 2005 Tools for Office Hands-on Labs for Word 2003 

Office 2003
 

Microsoft Corporation

June 2005
Updated February 2006

Applies to:
     Microsoft Visual Studio 2005 Tools for the Microsoft Office System
     Microsoft Visual Basic 2005
     Microsoft Office Word 2003

Summary: Use these Hands-on Labs to gain experience building a customer survey application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System and Microsoft Office Word 2003. (45 printed pages)

Download OfficeVSTO2005WordLabs.msi.

Download the OfficeVSTO2005WordLabs.msi sample file. (315 KB)

Contents

Setting Up the Labs
Lab 1-Creating Actions Panes in Word
Lab 2-Using Controls in Word Documents
Lab 3-Working with Data in Word Documents
Lab 4-Caching Data in Word Documents
Conclusion
Additional Resources

Setting Up the Labs

To work through the lab exercises, you must install:

  • Microsoft Windows 2000 or later
  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System
  • Microsoft Office Professional Edition 2003 with Service Pack 1
    Note   Microsoft Office Word and the primary interop assemblies (PIAs) are required for these labs. When prompted during installation, choose the option to run the PIAs from your computer.
    Note   You must set macro security in Word to medium in order to complete these labs.
  • Microsoft SQL Server 2005 Express Edition (included with Visual Studio 2005 Tools for Office)
Note   When applicable, instructions in the lab manual refer to files by a full path; it is assumed that the download files are extracted to \My Documents\Visual Studio 2005\Projects. The files needed to complete the labs are in a subfolder named VSTO 2005 Word Labs\Starter Files. A completed version of the labs can be found in the VSTO 2005 Word Labs\Completed Labs subfolder.
When working with controls on the Word document in this lab, if you find that you cannot change the value of a control, you may be in Design mode. To exit Design mode, on the Control Toolbox toolbar, click Exit Design Mode.
Also, if you receive a message stating "Windows Forms controls have been disabled because the document has been scaled. When zoom is returned to 100%, controls will reactivate," enter Design mode by clicking Design Mode on the Control Toolbox toolbar, resize the control that gave the error, and then exit Design mode again.

Lab 1—Creating Actions Panes in Word

The objective of this lab is to introduce you to creating Word projects with Visual Studio 2005 Tools for Office and to demonstrate working with an actions pane in Word. In this lab, you create an actions pane for your Word solution and manage layout of controls contained in the actions pane.

Estimated time to complete:

  • Exercise 1: Create Actions Panes—10 minutes

Exercise 1: Creating Actions Panes

To create a Word Document project

  1. On the File menu, click New, and then click Project.
  2. In the list of Project Types, expand Visual Basic (or Visual C#) and click Office.
  3. In the Templates list, select Word Document.
  4. In the Name box, type CustomerSurvey, and in the Location box type a location, such as C:\VSTO2005\Labs. The New Project dialog box looks like Figure 1.

    Create a Word Document project (click to see larger image)

    Figure 1. Create a Word Document project (click picture to see larger image)

  5. Click OK. The Visual Studio Tools for Office Project Wizard appears.
  6. In the wizard, select Copy an existing document.
  7. Click Browse.
  8. Navigate to the following directory and then click Open:

    \My Documents\Visual Studio 2005\Projects\VSTO 2005 Word Labs\Starter Files\Customer Satisfaction Survey.doc

  9. In the wizard, click OK. You may receive the warning shown in Figure 2.

    Access to the Visual Basic for Applications project system is disabled by default (click to see larger image)

    Figure 2. Access to the Visual Basic for Applications project system is disabled by default (click picture to see larger image)

    If you do receive the security message, click OK to allow access to the Visual Basic for Applications project system. You need to do this only once.

  10. On the File menu, click Save All.

To add a user control to the Word actions pane

  1. In Solution Explorer, right-click the CustomerSurvey project; on the shortcut menu, point to Add and then click Existing Item. The Add Existing Item dialog box appears.
  2. Navigate to one of the following directories, and then click Add:

    \My Documents\Visual Studio 2005\Projects\VSTO 2005 Word Labs\Starter Files\ SubmitPaneVB\SubmitPane.vb

    or

    \My Documents\Visual Studio 2005\Projects\VSTO 2005 Word Labs\Starter Files\SubmitPaneC#\SubmitPane.vb

  3. In Solution Explorer, right-click SubmitPane.vb (or SubmitPane.cs); on the shortcut menu, click View Designer.

    The user control SubmitPane appears as shown in Figure 3. It is a simple user control with only a GroupBox, a Label, and a Button.

    SubmitPane is ready to be added to the actions pane

    Figure 3. SubmitPane is ready to be added to the actions pane

  4. In Solution Explorer, right-click SubmitPane.vb (or SubmitPane.cs); on the shortcut menu, click View Code.
  5. Add code to the SubmitPane class to raise an event named OnSubmit when the Button is clicked and expose public write-only properties for setting the text of the Label and Button controls. Start by adding an Event statement to the SubmitPane class.
    ' Visual Basic
    Public Class SubmitPane
    
        Public Event OnSubmit()
    
    End Class
    
    // C#
    public partial class SubmitPane : UserControl
    {
        public SubmitPane()
        {
            InitializeComponent();
        }
    
        public delegate void OnSubmitEventDelegate();
        public event OnSubmitEventDelegate OnSubmit;
    }
    
  6. Add code to define the Message and ButtonText properties.
    ' Visual Basic
    Public WriteOnly Property Message() As String
        Set(ByVal value As String)
            Me.lblStatus.Text = value
        End Set
    End Property
    
    Public WriteOnly Property ButtonText() As String
        Set(ByVal value As String)
            Me.btnSubmit.Text = value
        End Set
    End Property
    
    // C#
    public string Message
    {
        set { this.lblStatus.Text = value; }
    }
    
    public string ButtonText
    {
        set { this.btnSubmit.Text = value;  }
    }
    
  7. On the View menu, click Designer.
  8. Double-click the button to display the button's Click event handler in the Code Editor.
  9. Add code to raise the OnSubmit event.
    ' Visual Basic
    Private Sub btnSubmit_Click(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles btnSubmit.Click
        RaiseEvent OnSubmit()
    End Sub
    
    // C#
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        OnSubmitEventDelegate temp = OnSubmit;
        if (temp != null) { temp(); }
    }
    
  10. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code. The code module for the document appears; notice that the document code-behind is encapsulated in a class named ThisDocument.
  11. Add a private member variable to the ThisDocument class for an instance of the SubmitPane user control.
    ' Visual Basic
    Private WithEvents ucSubmitPane As SubmitPane
    
    // C#
    private SubmitPane ucSubmitPane;
    
  12. Add a new private method named CreateActionsPane to the ThisDocument class that adds the SubmitPane control to the Word actions pane.
    ' Visual Basic
    Private Sub CreateActionsPane()
        Me.SmartDocument.SolutionID = ""
    
        'Add a user control to the actions pane.
        ucSubmitPane = New SubmitPane
        Me.ActionsPane.Controls.Add(ucSubmitPane)
    End Sub
    
    // C#
    private void CreateActionsPane()
    {
        this.SmartDocument.SolutionID = "";
    
        //Add the user control to the actions pane
        ucSubmitPane = new SubmitPane();
        ucSubmitPane.OnSubmit+=
            new SubmitPane.OnSubmitEventDelegate(ucSubmitPane_OnSubmit);
        ActionsPane.Controls.Add(ucSubmitPane);
    }
    
  13. Add a call to CreateActionsPane in the Startup event of the ThisDocument class.
    ' Visual Basic
    Private Sub ThisDocument_Startup(ByVal sender As Object, ByVal e As System.EventArgs) 
    Handles Me.Startup
        Me.CreateActionsPane()
    End Sub
    
    // C#
    private void ThisDocument_Startup(object sender, 
        System.EventArgs e)
    {
        CreateActionsPane();
    }
    
  14. Add code to handle the OnSubmit event that is raised by the SubmitPane control when the button is clicked.
  15. Add the following code to the procedure stub.
    ' Visual Basic
    Private Sub ucSubmitPane_OnSubmit() Handles ucSubmitPane.OnSubmit
        MessageBox.Show("TO DO. Submit")
    End Sub
    
    // C#
    private void ucSubmitPane_OnSubmit()
    {
        MessageBox.Show("TO DO: Submit");
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Debugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. The actions pane now contains a SubmitPane user control, as shown in Figure 4.

    The actions pane with the SubmitPane user control (click to see larger image)

    Figure 4. The actions pane with the SubmitPane user control (click picture to see larger image)

  4. Click Submit. You receive the message "TO DO: Submit".
  5. Close the document without saving changes and exit Word.

Lab 2—Using Controls in Word Documents

The objective of this lab is to demonstrate the types of controls that you can use in a Word document and how you can handle document events and control events.

Estimated time to complete:

  • Exercise 1: Using Bookmark Controls and Handling Events—15 minutes
  • Exercise 2: Using Windows Forms Controls—15 minutes

This lab uses the CustomerSurvey project you created in Lab 1.

Exercise 1: Using Bookmark Controls and Handling Events

To handle an event for a bookmark control in your document

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Designer.
  2. If the Properties window is not visible, on the View menu, click Properties Window.
  3. Click the arrow next to the list at the top of the Properties window (the Control list) to see the list of controls on the document; notice that several bookmark controls already exist. The designer automatically created bookmark controls for bookmarks that existed in the original Customer Satisfaction Survey.doc when you created the project.
  4. In the Control list of the Properties window, select bkMenu to select the bookmark bkMenu. This selects the Menu bookmark in the upper-right corner of the document.
  5. Double-click inside the bkMenu bookmark. The Code window for ThisDocument.vb (or ThisDocument.cs) opens. The bkMenu_SelectionChange event is automatically created for you.
  6. Add the following code to bkMenu_SelectionChange to change the Word status bar text when the bookmark is selected.
    ' Visual Basic
    Private Sub bkMenu_SelectionChange(ByVal sender As System.Object, _
      ByVal e As Microsoft.Office.Tools.Word.SelectionEventArgs) _
      Handles bkMenu.SelectionChange
        Application.StatusBar = "Right-click for more options"
    End Sub
    
    // C#
    private void bkMenu_SelectionChange(object sender, 
        Microsoft.Office.Tools.Word.SelectionEventArgs e)
    {
        Application.StatusBar = "Right-click for more options";
    }
    

To create a command bar during document initialization

  1. Add private member variables for CommandBar and CommandBarButton references to the ThisDocument class.
    ' Visual Basic
    'CommandBar pop-up and CommandBar controls
    Private cbPopup As Office.CommandBar
    Private cbctlPrevious As Office.CommandBarButton
    Private cbctlNext As Office.CommandBarButton
    Private cbctlShowByCustID As Office.CommandBarButton
    Private cbctlShowAll As Office.CommandBarButton
    Private cbctlShowCompleted As Office.CommandBarButton
    
    // C#
    //CommandBar pop-up and CommandBar controls
    private Office.CommandBar cbPopup;
    private Office.CommandBarButton cbctlPrevious;
    private Office.CommandBarButton cbctlNext;
    private Office.CommandBarButton cbctlShowByCustID;
    private Office.CommandBarButton cbctlShowAll;
    private Office.CommandBarButton cbctlShowCompleted;
    
  2. Add a private method to the ThisDocument class named cbctlClick that is called when any one of the command bar buttons on the custom pop-up is clicked.
    ' Visual Basic
    Private Sub cbctlClick(ByVal Ctrl As Office.CommandBarButton, _
      ByRef CancelDefault As Boolean)
        MessageBox.Show("To do: " & Ctrl.Tag)
    End Sub
    
    // C#
    private void cbctlClick(Office.CommandBarButton Ctrl, ref bool CancelDefault)
    {
        MessageBox.Show("To do: " + Ctrl.Tag);
    }
    
  3. Add a private method to the ThisDocument class named AddPopupButton that is called to create command bar buttons, and set up the event delegates for the buttons.
    ' Visual Basic
    Private Sub AddPopupButton(ByVal Caption As String, _
      ByVal Tag As String, ByRef Btn As Office.CommandBarButton)
    
        'Add the button with the given Caption and Tag properties.
        Btn = cbPopup.Controls.Add(Office.MsoControlType.msoControlButton)
        Btn.Caption = Caption
        Btn.Tag = Tag
    
        'Set up the event handler for the button when it is clicked.
        AddHandler Btn.Click, AddressOf Me.cbctlClick
    
    End Sub
    
    // C#
    private void AddPopupButton(string Caption, string Tag,
        ref Office.CommandBarButton Btn)
    {
        //Add the button with the given Caption and Tag properties.
        Btn = (Office.CommandBarButton)(cbPopup.Controls.Add(
            Office.MsoControlType.msoControlButton, 
            missing, missing, missing, missing));
        Btn.Caption = Caption;
        Btn.Tag = Tag;
    
        //Set up the event handler for the button when it is clicked.
        Btn.Click += new Office._CommandBarButtonEvents_ClickEventHandler(
            cbctlClick);
    }
    
  4. Add a private method to the ThisDocument class named AddPopupMenu that creates a pop-up command bar named Survey Filter Popup.
    ' Visual Basic
    Private Sub AddPopupMenu()
        Try
            //Set the customization context to this document so that 
            //command bar changes are reflected in this document and not 
            //stored in Normal.dot (which is the default).
            Me.Application.CustomizationContext = _
                Me.Application.ActiveDocument
    
            'Add the command bar to the document.
            cbPopup = Me.CommandBars.Add("Survey Filter Popup", _
                Office.MsoBarPosition.msoBarPopup, , True)
    
            'Add command bar buttons to the popup.
            Me.AddPopupButton("Previous Order", "MOVE_PREVIOUS", _
                cbctlPrevious)
            Me.AddPopupButton("Next Order", "MOVE_NEXT", cbctlNext)
            Me.AddPopupButton("", "SHOW_BY_CUSTID", cbctlShowByCustID)
            cbctlShowByCustID.BeginGroup = True
            Me.AddPopupButton("Show Only Completed Surveys Ready to Submit", _
                "SHOW_COMPLETED", cbctlShowCompleted)
            Me.AddPopupButton("Show All Orders", "SHOW_ALL", cbctlShowAll)
    
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error adding Survey Filter Popup")
        End Try
    End Sub
    
    // C#
    private void AddPopupMenu()
    {
        try
        {
            //Set the customization context to this document so that command
            //bar changes are reflected in this document and not stored
            //in Normal.dot (which is the default).
            Application.CustomizationContext = this.InnerObject;
    
            //Add the command bar to the document.
            cbPopup = this.CommandBars.Add("Survey Filter Popup", 
                Office.MsoBarPosition.msoBarPopup, missing, true);
    
            //Add command bar buttons to the pop-up.
            this.AddPopupButton("Previous Order", "MOVE_PREVIOUS", 
                ref cbctlPrevious);
            this.AddPopupButton("Next Order", "MOVE_NEXT", ref cbctlNext);
            this.AddPopupButton("", "SHOW_BY_CUSTID", ref cbctlShowByCustID);
            cbctlShowByCustID.BeginGroup = true;
            this.AddPopupButton( 
                "Show Only Completed Surveys Ready to Submit", 
                "SHOW_COMPLETED", ref cbctlShowCompleted);
            this.AddPopupButton("Show All Orders", "SHOW_ALL", ref cbctlShowAll);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error adding Survey Filter Popup");
        }
    }
    
  5. Append the following line of code to ThisDocument_Startup to call the AddPopupMenu method.
    ' Visual Basic
    AddPopupMenu()
    
    // C#
    AddPopupMenu();
    

To show a command bar pop-up when you right-click a bookmark

  1. On the View menu, click Designer.
  2. Select the bkMenu bookmark on the document.
  3. In the Properties window, click Events.
  4. In the Properties window, double-click the BeforeRightClick event to show the event handler in the Code Editor.
  5. Add code to bkMenu_BeforeRightClick that shows the Survey Filter Popup command bar when you right-click the bkMenu bookmark.
    ' Visual Basic
    Private Sub bkMenu_BeforeRightClick(ByVal sender As Object, _
      ByVal e As Microsoft.Office.Tools.Word.ClickEventArgs) _
      Handles bkMenu.BeforeRightClick
    
        'Update command bar button properties based on the text of the
        'current Customer ID.
        cbctlShowByCustID.Caption = "Show Only Orders for [TO DO]"
    
        'Show the pop-up and then cancel the default action. 
        cbPopup.ShowPopup()
        e.Cancel = True
    
    End Sub
    
    // C#
    private void bkMenu_BeforeRightClick(object sender, 
        Microsoft.Office.Tools.Word.ClickEventArgs e)
    {
        //Update command bar button properties based on the text of the
        //current Customer ID.
        cbctlShowByCustID.Caption = "Show Only Orders for [TO DO]";
    
        //Show the pop-up and then cancel the default action. 
        cbPopup.ShowPopup(missing, missing);
        e.Cancel = true;
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Dubugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. Click the document to place the insertion mark inside the bookmark bkMenu. Notice that the status bar text changes to Right-click for more options, as shown in Figure 5.

    Click the bookmark to see the status message; right-click to see the pop-up menu (click to see larger image)

    Figure 5. Click the bookmark to see the status message; right-click to see the pop-up menu (click picture to see larger image)

  4. Right-click the bookmark bkMenu to display the Survey Filter Popup command bar.
  5. Click any one of the controls on the pop-up menu to display a message.
  6. Close the document without saving changes and exit Word.

Exercise 2: Using Windows Forms Controls

To add Windows Forms controls to your document

Throughout this portion of the exercise, refer to Figure 6 for an illustration of how to position the controls on the document.

The document looks like this when the controls have all been placed (click to see larger image)

Figure 6. The document looks like this when the controls have all been placed (click picture to see larger image)

  1. In Solution Explorer, right-click ThisDocument. vb (or ThisDocument.cs); on the shortcut menu, click View Designer.
  2. If the Toolbox window is not visible, on the View menu, click Toolbox.
  3. Select a NumericUpDown control and drag it to the last cell of the first row in the survey table.
  4. Select a CheckBox control and drag it to the last cell of the second row in the survey table.
  5. Select a CheckBox control and drag it to the last cell of the third row in the survey table.
  6. Select a NumericUpDown control and drag it to the last cell of the fourth row in the survey table.
  7. Select a CheckBox control and drag it to the last cell of the fifth row in the survey table.
  8. Set the properties for the Windows Forms controls on the document according to Table 1.

    Table 1. Property values for controls in Customer Satisfaction Survey.doc

    Control Property Value
    NumericUpDown1 (Name) nupdnSalesRep
      Maximum 5
      Minimum 1
      Value 5
      Width 140
    CheckBox1 (Name) chkCourteous
      Text No
      Width 140
    CheckBox2 (Name) chkEfficiency
      Text No
      Width 140
    NumericUpDown2 (Name) nupdnOverall
      Maximum 5
      Minimum 1
      Value 5
      Width 140
    CheckBox3 (Name) chkSurveyed
      Text Completed
      Width 140

To initialize the Windows Forms controls on your document

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code.
  2. Add a private read-only property named NormalFont to the ThisDocument class that returns a Font similar to the Normal font in the document. You use NormalFont to set the Font properties of the Windows Forms controls so that the controls use a font similar to the Normal style font in the document.
    ' Visual Basic
    Private ReadOnly Property NormalFont() As System.Drawing.Font
         Get
             Dim ftNormal As Word.Font = Me.Styles("Normal").Font
             Dim ft As System.Drawing.Font
             ft = New Font(ftNormal.Name, ftNormal.Size, FontStyle.Regular)
             Return ft
         End Get
    End Property
    
    // C#
    private System.Drawing.Font NormalFont
    {
        get
        {
             object styleName = "Normal";
             Word.Font ftNormal = this.Styles.get_Item(ref styleName).Font;
             System.Drawing.Font ft;
             ft = new Font(ftNormal.Name, ftNormal.Size, FontStyle.Regular);
             return ft;
        }
    }
    
  3. Append the following code to ThisDocument_Startup to initialize the fonts of the controls.
    ' Visual Basic
    'Format the fonts of the controls to match the Normal style.
    Dim ft As System.Drawing.Font = Me.NormalFont
    Me.nupdnSalesRep.Font = ft
    Me.chkCourteous.Font = ft
    Me.chkEfficiency.Font = ft
    Me.nupdnOverall.Font = ft
    Me.chkSurveyed.Font = ft
    
    // C#
    //Format the fonts of the controls to match the Normal style.
    System.Drawing.Font ft = this.NormalFont;
    nupdnSalesRep.Font = ft;
    chkCourteous.Font = ft;
    chkEfficiency.Font = ft;
    nupdnOverall.Font = ft;
    chkSurveyed.Font = ft;
    
  4. On the View menu, click Designer.
  5. Double-click chkCourteous to display the CheckedChanged event handler in the Code Editor.
  6. Add code to the chkCourteous_CheckedChanged event handler that changes the text of the control to Yes or No based on the current value.
    ' Visual Basic
    Private Sub chkCourteous_CheckedChanged(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles chkCourteous.CheckedChanged
        If chkCourteous.Checked Then
            chkCourteous.Text = "Yes"
        Else
            chkCourteous.Text = "No"
        End If
    End Sub
    
    // C#
    private void chkCourteous_CheckedChanged(object sender, EventArgs e)
    {
            if(chkCourteous.Checked)
                chkCourteous.Text = "Yes";
            else
                chkCourteous.Text = "No";
    }
    
  7. Use the same technique to create a similar event handler for the CheckChanged event of the chkEfficiency check box control.
    ' Visual Basic
    Private Sub chkEfficiency_CheckedChanged(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles chkEfficiency.CheckedChanged
        If chkEfficiency.Checked Then
            chkEfficiency.Text = "Yes"
        Else
            chkEfficiency.Text = "No"
        End If
    End Sub
    
    // C#
    private void chkEfficiency_CheckedChanged(object sender, EventArgs e)
    {
        if (chkEfficiency.Checked)
            chkEfficiency.Text = "Yes";
        else
            chkEfficiency.Text = "No";
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Dubugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. The Windows Forms controls appear on the document, as illustrated in Figure 7.

    The Word document now includes Windows Forms controls (click to see larger image)

    Figure 7. The Word document now includes Windows Forms controls (click picture to see larger image)

  4. Click one of the check boxes and notice that the text changes to match the state of the check box.
  5. Change the value in one of the NumericUpDown controls.
  6. Close the document without saving changes and exit Word.

Lab 3—Working with Data in Word Documents

The objective of this lab is to illustrate how you can work with data in a Word document. In this lab, you connect to a SQL Server data source, bind bookmarks and Windows Forms controls to database objects, and navigate records in the data source.

Estimated time to complete:

  • Exercise 1: Setting Up Connections and Adding Data Sources—10 minutes
  • Exercise 2: Connecting, Binding, and Navigating to Data—30 minutes
  • Exercise 3: Filtering Data—10 minutes
  • Exercise 4: Inserting and Updating Data—10 minutes

This lab uses the CustomerSurvey project you created in Lab 2.

Exercise 1: Setting Up Connections and Adding Data Sources

To create the SurveysVSTO2005 SQL Express database

  1. On the taskbar, click Start, and then click Run. Type cmd and click OK. A command window opens.
  2. Go to the folder where the lab files reside:

    \My Documents\Visual Studio 2005\Projects\VSTO 2005 Word Labs\Starter Files.

  3. Execute the batch procedure.
    osql.exe –n -E -S .\SQLExpress -i SurveysVSTO2005.sql
    

    You receive the message "1 row affected" for each new entry in the database.

  4. Close the command window.

To add a data source to your project

  1. In Visual Studio, on the Data menu, click Show Data Sources. The Data Sources window appears.
  2. In the Data Sources window, click Add New Data Source. The Data Source Configuration Wizard appears.
  3. Select Database for the data source type and click Next.
  4. Click New Connection. If you created a data connection previously, you see the Add Connection dialog box (shown in Figure 9) and you can skip to the next step. If you did not create a data connection previously, the Choose Data Source dialog box appears:
    • In the Choose Data Source dialog box, select Microsoft SQL Server, as shown in Figure 8.

      Choose Data Source dialog box

      Figure 8. Choose Data Source dialog box

    • Click Continue. You see the Add Connection dialog box, as shown in Figure 9.

      The Add Connection dialog box (click to see larger image)

      Figure 9. The Add Connection dialog box (click picture to see larger image)

  5. Type .\SQLExpress in the Server name list; the Connect to a database section becomes available.
  6. In the Log on to the server section, select the Use Windows Authentication option.
  7. In the Select or enter a database name list, choose SurveysVSTO2005.
  8. Click Test Connection. If you do not receive the confirmation message shown in Figure 10, return to the Add Connection dialog box and verify that the selections you made are appropriate for your server.

    After you make appropriate selections, the test connection succeeds

    Figure 10. After you make appropriate selections, the test connection succeeds

  9. Click OK to close the message box, and then click OK to close the Add Connection dialog box. The Data Source Configuration Wizard now includes the connection you just created, as shown in Figure 11.

    The name of your server precedes the name of the database (click to see larger image)

    Figure 11. The name of your server precedes the name of the database (click picture to see larger image)

  10. Click Next. You see the next step of the wizard, as shown in Figure 12.

    Save the connection (click to see larger image)

    Figure 12. Save the connection (click picture to see larger image)

  11. Leave the Yes, save the connection as check box selected, name the connection NorthwindConnectionString and click Next. You see the Choose your database objects page of the wizard, as shown in Figure 13.

    Use this page to select which objects you need for your application (click to see larger image)

    Figure 13. Use this page to select which objects you need for your application (click picture to see larger image)

  12. Expand Tables in the list of database objects.
  13. Select the Customers and Surveys tables.
  14. Name the DataSet NorthwindDataSet, and then click Finish.

To configure the data source

  1. In the Data Sources window, select NorthwindDataSet and then click Edit DataSet with Designer at the top of the Data Sources window.
  2. Right-click the Surveys table in the designer window and, on the shortcut menu, click Configure. The TableAdapter Configuration Wizard appears.
  3. Append a WHERE clause to the SQL statement so that newly filled DataSets contain only records where the Surveyed field is 0.
    SELECT OrderID, CustomerID, RateSalesRep, RateCourteous, RateEfficiency, RateOverall, Surveyed 
    FROM dbo.Surveys
    WHERE Surveyed=0
    

    The completed dialog box looks like Figure 14.

    Add a WHERE clause to the SQL statement (click to see larger image)

    Figure 14. Add a WHERE clause to the SQL statement (click picture to see larger image)

  4. Click Finish.

To add a relation to the data source

  1. On the Data menu, point to Add and then click Relation. The Relation dialog box appears.
  2. Set the following fields for the new relation:

    Table 2. Relation dialog box fields

    Field Value
    Name FK_Survey_Customers
    Parent Table Surveys
    Child Table Customers
    Key Column CustomerID
    Foreign Key Column CustomerID

    When complete, the Relation dialog box looks like Figure 15.

    The Relation dialog box (click to see larger image)

    Figure 15. The Relation dialog box (click picture to see larger image)

  3. Click OK to add the new relation.
  4. The NorthwindDataSet designer window appears, as illustrated in Figure 16.

    Design view of the new relation

    Figure 16. Design view of the new relation

  5. On the File menu, click Save All to save your changes.

Exercise 2: Connecting, Binding, and Navigating to Data

To bind a bookmark to a field in the Surveys table (the parent table)

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Designer.
  2. In the Data Sources window, expand Surveys.

    Select OrderID under Surveys and drag it onto the bkOrderID bookmark in the document.

    Note   The bkOrderID bookmark is now bound to the SurveysBindingSource object. The designer automatically adds the SurveysBindingSource object to the component tray when you drag the OrderID field to the bookmark.

To bind the Windows Forms controls on the document to fields in SurveysBindingSource

  1. Select the nupdnSalesRep control in the document.
  2. In the Properties window, expand the DataBindings property and select the (Advanced) property.
  3. Click the ellipsis (...) next to the (Advanced) property. You see the Formatting and Advanced Binding dialog box, as shown in Figure 17.

    Formatting and Advanced Binding dialog box (click to see larger image)

    Figure 17. Formatting and Advanced Binding dialog box (click picture to see larger image)

  4. In the Property list, select Value.
  5. Drop down the Binding list.
  6. Expand SurveysBindingSource and select RateSalesRep.
  7. Click OK.
  8. Repeat Steps 1 through 7 for each of the other controls in the document. Use Table 3 to determine which property and field to bind for each control.

    Table 3. Binding instructions for each control

    Control Property to Bind Field to Bind
    nupdnSalesRep Value SurveysBindingSource—RateSalesRep
    chkCourteous Checked SurveysBindingSource—RateCourteous
    chkEfficiency Checked SurveysBindingSource—RateEfficiency
    nupdnOverall Value SurveysBindingSource—RateOverall
    chkSurveyed Checked SurveysBindingSource—Surveyed

To bind controls to fields in the Customers table (the child table) using the foreign key

  1. In the Data Sources window, notice that there is a Customers node in the Surveys table. This Customers node represents the relationship between the Surveys table and the Customers table.
  2. Expand the Customers node in the Surveys table.
  3. Select CustomerID under Customers and drag it onto the bkCustomerID bookmark in the document.
    Note   The bkCustomerID bookmark is now bound to the CustomersBindingSource object. The designer automatically adds the CustomersBindingSource object to the component tray when you drag the CustomerID field from the Data Sources window.
  4. Select CompanyName under Customers and drag it onto the bkCompanyName bookmark in the document.
  5. Select ContactName under Customers and drag it onto the bkContactName bookmark in the document.
  6. Select Phone under Customers and drag it onto the bkPhone bookmark in the document.
  7. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code.
  8. Add a new private method named GetSurveys to the ThisDocument class.
    ' Visual Basic
    Private Sub GetSurveys()
         Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
         Me.SurveysTableAdapter.Fill(Me.NorthwindDataSet.Surveys)
    End Sub
    
    // C#
    private void GetSurveys()
    {
        this.customersTableAdapter.Fill(this.northwindDataSet.Customers);
        this.surveysTableAdapter.Fill(this.northwindDataSet.Surveys);
    }
    
  9. Delete the lines of code that the designer added to ThisDocument_Startup to automatically fill the table adapters.
    ' Visual Basic
    'TODO: Delete this line of code to remove the default AutoFill for 
    'NorthwindDataSet.Customers.
    If Me.NeedsFill("NorthwindDataSet") Then
        Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
    End If
    'TODO: Delete this line of code to remove the default AutoFill for
    'NorthwindDataSet.Surveys.
    If Me.NeedsFill("NorthwindDataSet") Then
         Me.SurveysTableAdapter.Fill(Me.NorthwindDataSet.Surveys)
    End If
    
    // C#
    // TODO: Delete this line of code to remove the default AutoFill for
    // northwindDataSet.Customers.
    if (this.NeedsFill("northwindDataSet"))
    {
        this.customersTableAdapter.Fill(this.northwindDataSet.Customers);
    }
    // TODO: Delete this line of code to remove the default AutoFill for 
    // northwindDataSet.Surveys.
    if (this.NeedsFill("northwindDataSet"))
    {
        this.surveysTableAdapter.Fill(this.northwindDataSet.Surveys);
    }
    
  10. Add a call to GetSurveys to ThisDocument_Startup.
    ' Visual Basic
    Private Sub ThisDocument_Startup(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Startup
        Me.CreateActionsPane()
        Me.AddPopupMenu()
        'Format the controls' fonts to match the Normal style.
        Dim ft As System.Drawing.Font = Me.NormalFont
        Me.nupdnSalesRep.Font = ft
        Me.chkCourteous.Font = ft
        Me.chkEfficiency.Font = ft
        Me.nupdnOverall.Font = ft
        Me.chkSurveyed.Font = ft
    
        Me.GetSurveys()
    End Sub
    
    // C#
    private void ThisDocument_Startup(object sender, 
        System.EventArgs e)
    {
        CreateActionsPane();
    
        AddPopupMenu();
    
        //Format the controls' fonts to match the Normal style.
        System.Drawing.Font ft = this.NormalFont;
        nupdnSalesRep.Font = ft;
        chkCourteous.Font = ft;
        chkEfficiency.Font = ft;
        nupdnOverall.Font = ft;
        chkSurveyed.Font = ft;
    
        this.GetSurveys();
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Dubugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. Notice that the values in the first record of the data source (Order 10248 for VINET) appear in the bookmark controls and Windows Forms controls, as illustrated in Figure 18.

    The document now includes data-bound controls (click to see larger image)

    Figure 18. The document now includes data-bound controls (click picture to see larger image)

  4. Close the document without saving changes and exit Word.

To navigate to records in the view

  1. Replace the code in cbctlClick with the following code, which executes MovePrevious or MoveNext on the binding source based on the command bar button that triggered the event.
    ' Visual Basic
    Private Sub cbctlClick(ByVal Ctrl As Office.CommandBarButton, _
        ByRef CancelDefault As Boolean)
    
        Me.Application.ScreenUpdating = False
    
        Try
            'Perform action based on the tag of the button 
            'that was clicked. 
            Select Case Ctrl.Tag
                Case "MOVE_PREVIOUS"
                    Me.SurveysBindingSource.MovePrevious()
                Case "MOVE_NEXT"
                    Me.SurveysBindingSource.MoveNext()
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            Me.Application.ScreenUpdating = True
        End Try
    
    End Sub
    
    // C#
    private void cbctlClick(Office.CommandBarButton Ctrl, 
        ref bool CancelDefault)
    {
        Application.ScreenUpdating = false;
        try
        {
            //Perform action based on the tag of the button 
            //that was clicked. 
            switch (Ctrl.Tag)
            {
                case "MOVE_PREVIOUS":
                    surveysBindingSource.MovePrevious();
                    break;
                case "MOVE_NEXT":
                    surveysBindingSource.MoveNext();
                    break;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            Application.ScreenUpdating = true;
        }
    }
    
  2. Modify bkMenu_BeforeRightClick to enable or disable the navigation command bar buttons based on the current position in the view.
    ' Visual Basic
    Private Sub bkMenu_BeforeRightClick(ByVal sender As Object, _
        ByVal e As Microsoft.Office.Tools.Word.ClickEventArgs) _
        Handles bkMenu.BeforeRightClick
    
        'Update command bar button properties based on the text of the
        'current Customer ID and the current position in the data source.
        cbctlShowByCustID.Caption = _
            "Show Only Orders for " & Me.bkCustomerID.Text
        If Me.bkCustomerID.Text = "" Then
            cbctlShowByCustID.Enabled = False
        Else
            cbctlShowByCustID.Enabled = True
        End If
        Dim nPos As Integer = Me.SurveysBindingSource.Position
        cbctlPrevious.Enabled = (nPos > 0)
        cbctlNext.Enabled = ( _
            nPos + 1 < Me.SurveysBindingSource.Count)
    
        'Show the pop-up and then cancel the default action. 
        cbPopup.ShowPopup()
        e.Cancel = True
    End Sub
    
    // C#
    private void bkMenu_BeforeRightClick(object sender, 
        Microsoft.Office.Tools.Word.ClickEventArgs e)
    {
        // Update command bar button properties based on the text of the
        // current Customer ID and the current position in the data source.
        cbctlShowByCustID.Caption = 
            "Show Only Orders for " + this.bkCustomerID.Text;
        if (this.bkCustomerID.Text == "")
            cbctlShowByCustID.Enabled = false;
        else
            cbctlShowByCustID.Enabled = true;
        int nPos = this.surveysBindingSource.Position;
        cbctlPrevious.Enabled = (nPos > 0);
        cbctlNext.Enabled = ( 
            nPos + 1 < surveysBindingSource.Count);
    
        //Show the pop-up and then cancel the default action. 
        cbPopup.ShowPopup(missing,missing);
        e.Cancel = true;
    }
    

To display the current position and record count in the view

  1. Add a private method to the ThisDocument class named UpdatePane that updates the label in the SubmitPane control in the actions pane to display the current position and record count in the document.
    ' Visual Basic
    Private Sub UpdatePane()
        'Update the SubmitPane control to display the current position in
        'the data source and the count of records in the data source.
        If Not (Me.ucSubmitPane Is Nothing) Then
            Dim sCaption As String
            Dim nCurrentRecord As Integer = Me.SurveysBindingSource.Position + 1
            Dim nRecords As Integer = Me.SurveysBindingSource.Count
            sCaption = nCurrentRecord & " of " & nRecords
            Me.ucSubmitPane.Message = sCaption
        End If
    End Sub
    
    // C#
    private void UpdatePane()
    {
        //Update the SubmitPane control to display the current position in
        //the data source and the count of records in the data source.
        if (ucSubmitPane!=null)
        {
            string sCaption;
            int nCurrentRecord = surveysBindingSource.Position + 1;
            int nRecords = surveysBindingSource.Count;
            sCaption = nCurrentRecord + " of " + nRecords;
            ucSubmitPane.Message = sCaption;
        }
    }
    
  2. On the View menu, click Designer.
  3. Select SurveysBindingSource.
  4. In the Properties window, click Events. Double-click the ListChanged event to display the event handler in the Code Editor.
  5. Add code to SurveysBindingSource_ListChanged that updates the window caption when the list changes.
    ' Visual Basic
    Private Sub SurveysBindingSource_ListChanged(ByVal sender As Object, _
      ByVal e As System.ComponentModel.ListChangedEventArgs) _
      Handles SurveysBindingSource.ListChanged
        Me.UpdatePane()
    End Sub
    
    // C#
    private void surveysBindingSource_ListChanged(object sender, System.ComponentModel.ListChangedEventArgs e)
    {
        this.UpdatePane();
    }
    
  6. Use a similar technique for adding code to the PositionChanged event that updates the window caption when the position within the existing list changes.
    ' Visual Basic
    Private Sub SurveysBindingSource_PositionChanged(ByVal sender As Object, _
      ByVal e As System.EventArgs) _
      Handles SurveysBindingSource.PositionChanged
        Me.UpdatePane()
    End Sub
    
    // C#
    private void surveysBindingSource_PositionChanged(object sender, EventArgs e)
    {
        this.UpdatePane();
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Dubugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word. Notice that the actions pane displays the current position in the data source (1 of 100) and that the first order in the view is for Order 10248.
  3. Fill out the controls in the survey.
  4. Right-click the bookmark bkMenu, and click Next Order on the pop-up menu to move to the next record in the data source. Notice that the actions pane changes to reflect the current position in the data source (2 of 100) and that Order 10249 now appears in the view.
  5. Right-click the bookmark bkMenu, and click Previous Order on the pop-up menu to move back to the first record in the data source.
  6. Notice that your survey selections (stored in the in-memory dataset, not in the actual data source) are restored in the view for Order 10248.
  7. Close the document without saving changes and exit Word.

Exercise 3: Filtering Data

To filter data in your data source

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code.
  2. Add three additional cases to the Select Case (or switch) statement in cbctlClick that filter the data source to show all data, show data only for a given customer, or show data only for completed surveys.
    ' Visual Basic
    Private Sub cbctlClick(ByVal Ctrl As Office.CommandBarButton, _
        ByRef CancelDefault As Boolean)
    
        Me.Application.ScreenUpdating = False
    
        Try
            'Perform action based on the tag of the button 
            'that was clicked. 
            Select Case Ctrl.Tag
                Case "MOVE_PREVIOUS"
                    Me.SurveysBindingSource.MovePrevious()
                Case "MOVE_NEXT"
                    Me.SurveysBindingSource.MoveNext()
                Case "SHOW_ALL"
                    Me.SurveysBindingSource.EndEdit()
                    Me.SurveysBindingSource.RemoveFilter()
                Case "SHOW_BY_CUSTID"
                    Me.SurveysBindingSource.EndEdit()
                    Me.SurveysBindingSource.Filter = _
                        String.Format(" CustomerID = '{0}' ", _
                        Me.bkCustomerID.Text)
                Case "SHOW_COMPLETED"
                    Me.SurveysBindingSource.EndEdit()
                    Me.SurveysBindingSource.Filter = "Surveyed=1"
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            Me.Application.ScreenUpdating = True
        End Try
    End Sub
    
    // C#
    private void cbctlClick(Office.CommandBarButton Ctrl, 
        ref bool CancelDefault)
    {
        Application.ScreenUpdating = false;
    
        try
        {
            //Perform action based on the tag of the button 
            //that was clicked. 
            switch (Ctrl.Tag)
            {
                case "MOVE_PREVIOUS":
                    surveysBindingSource.MovePrevious();
                    break;
                case "MOVE_NEXT":
                    surveysBindingSource.MoveNext();
                    break;
                case "SHOW_ALL":
                    surveysBindingSource.EndEdit();
                    surveysBindingSource.RemoveFilter();
                    break;
                case "SHOW_BY_CUSTID":
                    surveysBindingSource.EndEdit();
                    surveysBindingSource.Filter = 
                        String.Format(" CustomerID = '{0}' ", 
                        this.bkCustomerID.Text);
                    break;
                case "SHOW_COMPLETED":
                    surveysBindingSource.EndEdit();
                    surveysBindingSource.Filter = "Surveyed=1";
                    break;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            Application.ScreenUpdating = true;
        }
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Dubugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. Notice the actions pane; currently, the view contains 100 records in the table and you are viewing Record 1.
  4. Right-click the bkMenu bookmark; on the shortcut menu, click Show Only Orders for VINET.
  5. Notice the actions pane message, which indicates that the view now represents only the three records in the Surveys table that are associated with the Customer ID VINET.
  6. Right-click the bkMenu bookmark, and then click Previous Order or Next Order to navigate the records for VINET.
  7. Right-click the bkMenu bookmark, and then click Show All Orders to show all records.
  8. Complete questionnaires for one or more records. After you complete a survey for a record, check Completed.
  9. Right-click the bkMenu bookmark, and then click Show Only Completed Surveys Ready to Submit to display only those surveys that you marked as completed.
  10. Close the document without saving changes and exit Word.

Exercise 4: Inserting and Updating Data

To update data

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code.
  2. Replace the code in the OnSubmit event that is raised by the SubmitPane control when the button is clicked to update the survey data in the database.
    ' Visual Basic
    Private Sub ucSubmitPane_OnSubmit() Handles ucSubmitPane.OnSubmit
    
        'End the current edit.
        Me.SurveysBindingSource.EndEdit()
    
        'Select those rows that have the Surveyed field set to 1.
        Dim rows() As CustomerSurvey.NorthwindDataSet.SurveysRow
        rows = Me.northwindDataSet.Surveys.Select("Surveyed=1")
    
        'If there is at least one row selected, then proceed to update
        'the database; otherwise, display a message.
        If rows.Length > 0 Then
            Try
                Dim nCount As Integer
    
                'Perform the update and display a message about the
                'rows affected.
                nCount = Me.surveysTableAdapter.Update(rows)
                MessageBox.Show("Number of surveys submitted: " & nCount)
                GetSurveys()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error updating records")
            End Try
        Else
            MessageBox.Show("None of the records have been marked as " & _
                "complete. No updates to the database have been made.")
        End If
    End Sub
    
    // C#
    private void ucSubmitPane_OnSubmit()
    {
        // End the current edit.
        surveysBindingSource.EndEdit();
    
        //Select those rows that have the Surveyed field set to 1.
        CustomerSurvey.NorthwindDataSet.SurveysRow[] rows;
        rows = (CustomerSurvey.NorthwindDataSet.SurveysRow[])(
            northwindDataSet.Surveys.Select("Surveyed=1"));
    
        //If there is at least one row selected, then proceed to update
        //the database; otherwise, display a message.
        if (rows.Length > 0)
        {
            try
            {
                int nCount;
    
                //Perform the update and display a message about the
                //rows affected.
                nCount = surveysTableAdapter.Update(rows);
                MessageBox.Show("Number of surveys submitted: " + nCount);
                GetSurveys();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error updating records");
            }
        }
        else
        {
            MessageBox.Show("None of the records have been marked as " +
            "complete. No updates to the database have been made.");
        }
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Debugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. Complete the survey for Order 10248 (VINET) by setting values in the controls and checking the Completed check box.
  4. Click Submit in the actions pane. You receive a message that one survey was submitted. The next record appears.
  5. Complete the survey for Order 10249 (TOMSP) by setting values in the controls and checking the Completed check box, but, in the actions pane, do not click Submit to submit the survey yet.
  6. Right-click the bookmark bkMenu; on the shortcut menu, click Next Order to show the next record.
  7. Complete the survey for Order 10250 (HANAR) by setting values in the controls and checking the Completed check box.
  8. Click Submit. You receive a message that two surveys were submitted.
  9. Close the document without saving changes and exit Word.

Lab 4—Caching Data in Word Documents

This lab illustrates how you can cache data in a Word document for offline use. After you cache some data in a document, you read from and write to the document's data cache.

Estimated time to complete:

  • Exercise 1: Caching Data in Word Documents—20 minutes

This lab uses the CustomerSurvey project you created in Lab 3.

Exercise 1: Caching Data in Word Documents

To add a cached dataset to the document

  1. In Solution Explorer, right-click ThisDocument.vb (or ThisDocument.cs); on the shortcut menu, click View Code.
  2. Add a public variable to the ThisDocument class to store the DataSet in the document cache.
    ' Visual Basic
    <Cached()> Public dsCached As CustomerSurvey.NorthwindDataSet
    
    // C#
    [Cached()]
    public CustomerSurvey.NorthwindDataSet dsCached;
    
  3. Add a private member variable to the ThisDocument class that you use to indicate whether you are using a cached dataset.
    ' Visual Basic
    Private bCached As Boolean
    
    // C#
    private bool bCached;
    
  4. On the View menu, click Designer.
  5. In the Properties window, select ThisDocument, if it is not already selected.
  6. In the Properties window, click Events to show the events available to ThisDocument.
  7. Double-click the BeforeSave event to show the event handler in the Code Editor.
  8. Add code to ThisDocument_BeforeSave that caches the data when the document is saved.
    ' Visual Basic
    Private Sub ThisDocument_BeforeSave(ByVal sender As Object, _
      ByVal e As Microsoft.Office.Tools.Word.SaveEventArgs) _
      Handles Me.BeforeSave
        Me.SurveysBindingSource.EndEdit()
        dsCached = Me.NorthwindDataSet
    End Sub
    
    // C#
    private void ThisDocument_BeforeSave(object sender, 
       Microsoft.Office.Tools.Word.SaveEventArgs e)
    {
        surveysBindingSource.EndEdit();
        dsCached = northwindDataSet;
    }
    
  9. Replace the code in the GetSurveys method so that it uses data from the cache when data is present in the document.
    ' Visual Basic
    Private Sub GetSurveys()
        bCached = Not (dsCached Is Nothing)
    
        Try
            Me.Application.ScreenUpdating = False
            If Not bCached Then
                'If there is no data cached in this document, then fill 
                'the dataset from the live data source.
                Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers)
                Me.SurveysTableAdapter.Fill(Me.NorthwindDataSet.Surveys)
            Else
                'Load the dataset from the cache.
                Me.NorthwindDataSet = dsCached
            End If
            'Set up the data connectors.
            Me.SurveysBindingSource.DataMember = "Surveys"
            Me.SurveysBindingSource.DataSource = Me.NorthwindDataSet
            Me.CustomersBindingSource.DataMember = "FK_Survey_Customers"
            Me.CustomersBindingSource.DataSource = Me.SurveysBindingSource
            Me.Application.ScreenUpdating = True
        Catch ex As Exception
            Me.Application.ScreenUpdating = True
            MessageBox.Show(ex.Message, "Error retrieving data")
        End Try
    End Sub
    
    // C#
    private void GetSurveys()
    {
        bCached = (dsCached!=null);
        try
        {
            Application.ScreenUpdating = false;
            if (!bCached)
            {
                //If there is no data cached in this document, then fill 
                //the dataset from the live data source.
                customersTableAdapter.Fill( northwindDataSet.Customers);
                surveysTableAdapter.Fill(northwindDataSet.Surveys);
            }
            else
            {
                //Load the dataset from the cache.
                northwindDataSet = dsCached;
            }
    
            //Set up the binding sources.
            surveysBindingSource.DataMember = "Surveys";
            surveysBindingSource.DataSource = northwindDataSet;
            customersBindingSource.DataMember = "Surveys_Customers";
            customersBindingSource.DataSource = surveysBindingSource;
            Application.ScreenUpdating = true;
        }
        catch (Exception ex)
        {
            Application.ScreenUpdating = true;
            MessageBox.Show(ex.Message, "Error retrieving data");
        }
    }
    
  10. Modify the UpdatePane method so that, when the view represents cached data, the SubmitPane control includes a message containing the text "[Cached]" and the button displays the text "Connect and Submit".
    ' Visual Basic
    Private Sub UpdatePane()
        'Update the SubmitPane control to display the current position in
        'the data source and the count of records in the data source.
        If Not (Me.ucSubmitPane Is Nothing) Then
            Dim sCaption As String
            Dim nCurrentRecord As Integer = Me.SurveysBindingSource.Position + 1
            Dim nRecords As Integer = Me.SurveysBindingSource.Count
            sCaption = nCurrentRecord & " of " & nRecords
            If bCached Then sCaption &= vbCrLf & "[Cached]"
            Me.ucSubmitPane.Message = sCaption
            If bCached Then 
               Me.ucSubmitPane.ButtonText = "Connect and Submit"
            Else
               Me.ucSubmitPane.ButtonText = "Submit"
            End If
        End If
    End Sub
    
    // C#
    private void UpdatePane()
    {
        //Update the SubmitPane control to display the current position in
        //the data source and the count of records in the data source.
        if (ucSubmitPane!=null)
        {
            string sCaption;
            int nCurrentRecord = surveysBindingSource.Position + 1;
            int nRecords = surveysBindingSource.Count;
            sCaption = nCurrentRecord + " of " + nRecords;
            if(bCached) sCaption += "\n[Cached]";
            ucSubmitPane.Message = sCaption;
            if (bCached)
                ucSubmitPane.ButtonText = "Connect and Submit";
            else
                ucSubmitPane.ButtonText = "Submit";
        }
    }
    
  11. Modify the OnSubmit event of the SubmitPane control to handle updates originating from the cached dataset.
    ' Visual Basic
    Private Sub ucSubmitPane_OnSubmit() Handles ucSubmitPane.OnSubmit
        'End the current edit.
        Me.SurveysBindingSource.EndEdit()
    
        'Select those rows that have the Surveyed field set to 1.
        Dim rows() As CustomerSurvey.NorthwindDataSet.SurveysRow
        rows = Me.northwindDataSet.Surveys.Select("Surveyed=1")
    
        'If there is at least one row selected, then proceed to update
        'the database; otherwise, display a message.
        If rows.Length > 0 Then
            Try
                Dim nCount As Integer
                'Perform the update and display a message about the
                'rows affected.
                nCount = Me.surveysTableAdapter.Update(rows)
                MessageBox.Show("Number of surveys submitted: " & nCount)
                If bCached Then
                    dsCached.Clear()
                    dsCached = Nothing
                End If
                GetSurveys()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error updating records")
            End Try
        Else
            MessageBox.Show("None of the records have been marked as " & _
                "complete. No updates to the database have been made.")
        End If
    End Sub
    
    // C#
    private void ucSubmitPane_OnSubmit()
    {
        // End the current edit.
        surveysBindingSource.EndEdit();
    
        //Select those rows that have the Surveyed field set to 1.
        CustomerSurvey.NorthwindDataSet.SurveysRow[] rows;
        rows = (CustomerSurvey.NorthwindDataSet.SurveysRow[])(
            northwindDataSet.Surveys.Select("Surveyed=1"));
    
        //If there is at least one row selected, then proceed to update
        //the database; otherwise, display a message.
        if (rows.Length > 0)
        {
            try
            {
                int nCount;
    
                //Perform the update and display a message about the
                //rows affected.
                nCount = surveysTableAdapter.Update(rows);
                if(bCached)
                {
                    dsCached.Clear();
                    dsCached = null;
                }
                MessageBox.Show("Number of surveys submitted: " + nCount);
                GetSurveys();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error updating records");
            }
        }
        else
        {
            MessageBox.Show("None of the records have been marked as " +
            "complete. No updates to the database have been made.");
        }
    }
    

Checkpoint

  1. On the File menu, click Save All.
  2. On the Debug menu, click Start Debugging to build and run the project. The document Customer Satisfaction Survey.doc opens in Word.
  3. Currently the data source represents 97 survey records, and the current record is Order 10251 for VICTE.
  4. Complete the surveys for Orders 10251 and 10252, but do not submit them.
  5. On the File menu, click Save As. The Save As dialog box appears.
  6. Save the file with the name Cached.doc.
  7. Close Cached.doc.
  8. Open Cached.doc.
  9. Notice that the actions pane indicates that you are viewing cached data, as illustrated in Figure 19. Also, notice that the survey selections you made for Order 10251 and Order 10252 are stored.

    The actions pane shows that you are working with cached data (click to see larger image)

    Figure 19. The actions pane shows that you are working with cached data (click picture to see larger image)

  10. Move to Order 10253 and complete the survey.
  11. Click Connect and Submit in the actions pane. You receive a message that three surveys are submitted. After the surveys are submitted, the actions pane indicates that you are no longer working with cached data. The view now contains 94 uncompleted surveys.
  12. Close the document and exit Word.

Conclusion

In these labs, you learned how to create a customer survey application using Microsoft Visual Studio 2005 Tools for the Microsoft Office System and Microsoft Office Word 2003. You learned how to use Windows Forms controls, host controls, the actions pane, and data binding.

Additional Resources

For more information, see the following resources:

Show:
© 2014 Microsoft