Create a Search Application with Word 2003 and Visual Basic .NET

Frank Rice
Microsoft Corporation

May 2005

Applies to:
    Microsoft Office Word 2003
    Microsoft Office Access 2003
    Microsoft Visual Basic .NET

Summary: Create a simple application in Visual Basic .NET that searches an Access database and use the results to populate a Word document using both XML tags and bookmarks. The article also provides some background on data access technologies in .NET. (17 printed pages)

Download Word2003CreateSearchApplication.exe.

Contents

Microsoft .NET Data Access and Manipulation Technologies
Creating the Project and Windows Form
Adding Controls to the Form
Adding Code to Access and Query the Database
Including Code to Build the Search String
Creating the Schema
Creating the Word XML Template
Adding Bookmarks to the Word Template
Merging the Data with the XML Elements
Merging Data with the Bookmarks
Adding Code for the Final Tasks
Testing the Form
Conclusion
Additional Resources

Microsoft .NET Data Access and Manipulation Technologies

Face it. Without data, we as developers would probably be broke, and at the very least, bored. Data access, data manipulation, and data display tasks constitute many, if not most, of the applications built by developers. One of the more common requests that application developers get from their customers is for the ability to search a data source for records that match a certain criteria. For example, your customer may want to find a vendor, based on the type of service they provide, or find an employee record by job title. Once found, users usually want some way to display or save this information.

The combination of Microsoft Visual Basic .NET and the Microsoft Office System provides a relatively easy way, to combine the strengths of both products to create applications that deliver this and other types of functionality. One technique used is through a Component Object Model (COM) technology known as automation. Automation enables you to create and control one application from another application; in this case, controlling Microsoft Office Word 2003 from within a Visual Basic .NET application. Another technique is through the ubiquitous data transfer and data description capabilities of XML.

Microsoft Visual Studio .NET provides a development environment with a number of objects and constructs to make your data access and data manipulation work easier. For example, using the OleDbDataReader object you can retrieve a read-only, forward-only stream of data from a database, which results in a fast, efficient way to expose data. Results are returned as the query executes, and are stored in a buffer on the client until you request them. Using the OleDbDataReader object can increase application performance both by retrieving data as soon as it is available, rather than waiting for the entire results of the query to return, and (by default) storing all returned rows in memory, reducing system overhead.

The underlying structures supporting this functionality are Microsoft OLE DB and Microsoft ActiveX Data Object (ADO).NET technologies. OLE DB provides the programming interface that allows applications to connect to data. It serves as the underlying technology for ADO as well as a source of data for ADO.NET. Additionally, OLE DB is an open standard for accessing all kinds of data—both relational and non-relational data.

ADO.NET, on the other hand, provides consistent data retrieval and manipulation services for data sources such as Microsoft SQL Server, as well as disparate data sources exposed through OLE DB and XML.

This article illustrates a simple but complete example using automation, OLE DB, and XML to search a database for records matching specific criteria, display the data in a Microsoft Windows Form, and merge it into a Word document–based templates. The reader is given the choice of using a template containing XML tags or a template containing bookmarks. The example highlights the capabilities of the Microsoft .NET Framework to work with unmanaged code from Office applications within managed code in Microsoft Visual Basic .NET. Figure 1 illustrates the form that you will create in this sample.

To complete this exercise, you need access to a server or file share with the Northwind sample database. Northwind is available with Microsoft Office Access and Microsoft SQL Server.

The project is split into a number of smaller pieces:

• Creating the project and Windows Form
• Adding controls to the form to display the search results
• Creating and configuring the database connection and SQL query
• Building an incremental search string
• Creating the schema
• Creating a Word template with XML tags
• Creating a Word template with bookmarks
• Writing code that automates an instance of Word
• Adding code that populates the XML template
• Adding code that populates the bookmark template
• Testing the project

Creating the Project and Windows Form

The first step is to create a Windows Form.

To create the project and form

1. Start Microsoft Visual Studio .NET.
2. From the File menu, point to New, and then choose Project. The New Project dialog box is displayed.
3. In the Project Types pane, choose Visual Basic Projects.
4. In the Templates pane, choose Windows Application for Visual Basic projects.
5. Assign a name to the project that is unique and conforms to the naming conventions you use. For example, you might name this project PopulateAWordDocument.
6. After you assign a name, click OK to create the project.

   Visual Studio displays a new form in the Windows Form Designer.

7. In the Solution Explorer, right-click Form1.vb and rename it SelectRecords.vb.
8. Click the SelectRecords.vb [Design] tab and then click the form to display the form's Properties window. If you still do not see the Properties window, click the View menu and then click Properties window.
9. In the Properties window, rename the Text and Name properties to SelectRecords.
10. In the Solution Explorer, right-click the project you named in step 5 (PopulateAWordDocument, in this case), and select Properties.
11. In the <project name> Property Pages dialog box, select SelectRecords in the Start up object drop-down box, and click OK.
12. In the Solution Explorer, right-click SelectRecords.vb and click View Code.
13. Add the following namespace statements before the Public Class SelectRecords line:

    Imports System.Data.OleDb
    Imports Word = Microsoft.Office.Interop.Word
    Imports System.Xml
    

14. Add the following variables to the class, just before the Windows Form Designer generated code region, so that they are scoped for the class:

    Friend WithEvents ThisDocument As Word.Document
    Friend WithEvents ThisApplication As Word.Application
    

After completing the procedure, you also need to add a reference to the Word 11.0 Object Model.

To add a reference to Microsoft Word

1. Select References in the Solution Explorer window.
2. Open the Project menu and then click Add Reference. The Add Reference dialog box appears.
3. Click the COM tab.
4. Select Microsoft Word 11.0 Object Library in the Component Name list and then click Select.
5. Click OK to add the reference to your project.

Adding Controls to the Form

For this sample, you need a way for users to execute the query to retrieve the data, and a way to display the information (see Figure 1).

To add controls to the form

1. Add the following controls to the form, naming them as indicated:

   Table 1. Form controls

   Control	Purpose	Name	Text
   TextBox	Allows users to enter search criteria for Employee ID and display the results	txtEmployeeID	(Empty string)
   TextBox	Allows users to enter search criteria for the employee's first name and display the results	txtFirstName	(Empty string)
   TextBox	Allows users to enter search criteria for the employee's last name and display the results	txtLastName	(Empty string)
   TextBox	Allows users to enter search criteria for the employee's job title and display the results	txtTitle	(Empty string)
   Button	Runs the search query	btnSearch	Search
   Button	Starts Word and populates the form letter using XML tags	btnDisplaywithXML	Write with XML
   Button	Starts Word and populates the form letter using bookmarks	btnDisplaywithBookmarks	Write with bookmarks
   Button	Clears the text boxes on the Windows Form	btnReset	Reset
   Button	Closes the Windows Form	btnClose	Close form

2. Add labels in front of the text boxes to indicate their function, if desired.

Adding Code to Access and Query the Database

In this example, each time the users clicks the Search button, the search string builds, a connection is made to the database, the search query executes using an OleDbDataReader object, and the results display in the form. As you will see, you execute the query in code by calling the ExecuteReader method of the OleDbDataReader.

To write code to populate the dataset

Double-click the Search button to create a method for the Click event. Add code to the handler to:

• Set the initial value of the query string.
• Create a connection string to the Northwind sample database.
• Call the BuildSearchString function to build the query string based on user selections in the text boxes.
• Open the database connection.
• Call the ExecuteReader method of the OleDbDataReader object to execute the SQL string and fill the reader.
• Check to see whether the query returned multiple records and, if so, suggest that the user refine the search criteria.
• Check to make sure that the query returned a record.
• Populate the text boxes with the search results.

The following example shows what the code for the Search button's Click event handler looks like:

Private Sub btnSearch_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnSearch.Click
    ' The OleDbDataReader is a forward-only data reader used
    ' for speed and efficiency. 
    Dim SQLReader As OleDbDataReader
    Dim searchStr As String = ""
    Dim cnt As Integer = 0

    ' Set up the initial SQL command string.
    Dim sqlStr As String = "SELECT Employees.EmployeeID, " & _
    "Employees.FirstName, Employees.LastName, " & _
    "Employees.Title FROM Employees WHERE "

    ' Set up the connection to Northwind.
    Dim connectStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\Office11\" & _
    "Samples\Northwind.mdb;"
    Dim cnn1 As New OleDbConnection(connectStr)

    ' Build the search string.
    searchStr = BuildSearchString(sqlStr)

    ' Notice that the connection isn't opened  
    ' until it is needed in order to persist it 
    ' for as short a time as possible.
    cnn1.Open()

    ' Set up the command object.
    Dim sqlCmd As New OleDbCommand(searchStr, cnn1)

    ' Fill the SQLReader.
    If Me.txtEmployeeID.Text = "" And Me.txtEmployeeID.Text = "" _
        And Me.txtFirstName.Text = "" And Me.txtLastName.Text = "" _
        And Me.txtTitle.Text = "" Then
        MsgBox("You must use at least one search criteria.")
        Exit Sub
    Else
        SQLReader = sqlCmd.ExecuteReader()
    End If

    ' Determine if the search returned more than one 
    ' matching row.
    While SQLReader.Read
        cnt += 1
    End While
    If cnt > 1 Then
        MsgBox("The criteria matched more than one " & _
           "record. Please refine your search and try again.")
        Exit Sub
    End If

    ' Because the OleDbDataReader is a forward-only reader, 
    ' we need to refill the reader in order to use it a
    ' second time. First, we need to delete the reference
    ' to the current object.
    SQLReader.Close()
    SQLReader = sqlCmd.ExecuteReader()

    ' If records were returned, display them on the form.
    If SQLReader.HasRows Then
       While SQLReader.Read
            txtEmployeeID.Text = SQLReader.Item("EmployeeID")
            txtFirstName.Text = SQLReader.Item("FirstName")
            txtLastName.Text = SQLReader.Item("LastName")
            txtTitle.Text = SQLReader.Item("Title")
       End While
    Else
        MsgBox("There were no matches. Please try again.")
    End If

    ' Clean up.
    SQLReader.Close()
    sqlCmd.Dispose()
    cnn1.Close()

End Sub

Including Code to Build the Search String

The BuildSearchString function is a simple procedure that builds the search string from the entries in the text boxes. Then it returns the string to the btnSearch_Click event handler where it executes against the database.

Add code to do the following:

• Check each text box to retrieve the user search criteria
• Add the search information to the search string
• Return the string to the calling procedure

The following procedure shows what the code for the function looks like:

Private Function BuildSearchString(ByVal sqlStr As String) As String
    ' This is used to determine if we need to 
    ' allow for concatenating SQL strings.
    Dim Append As Boolean = False

    ' Check for additional search criteria.
    If Not txtEmployeeID.Text = "" Then
        sqlStr = sqlStr & " (((Employees.EmployeeID) Like """ & txtEmployeeID.Text & """)"
        Append = True
    End If
    If Not txtFirstName.Text = "" Then
        If Append Then
            sqlStr = sqlStr & " AND "
            sqlStr = sqlStr & " (((Employees.FirstName) Like """ & txtFirstName.Text & """)"
        Else
            sqlStr = sqlStr & " (((Employees.FirstName) Like """ & txtFirstName.Text & """)"
            Append = True
        End If
    End If
    If Not txtLastName.Text = "" Then
        If Append Then
            sqlStr = sqlStr & " AND "
            sqlStr = sqlStr & " ((Employees.LastName) Like """ & txtLastName.Text & """)"
        Else
            sqlStr = sqlStr & " (((Employees.LastName) Like """ & txtLastName.Text & """)"
            Append = True
        End If
    End If
    If Not txtTitle.Text = "" Then
        If Append Then
            sqlStr = sqlStr & " AND "
            sqlStr = sqlStr & " ((Employees.Title) Like """ & txtTitle.Text & """)"
        Else
            sqlStr = sqlStr & " (((Employees.Title) Like """ & txtTitle.Text & """)"
        End If
    End If

    ' Finished concatenating search clauses so add closing ')'.
    sqlStr = sqlStr & ")"

    Return sqlStr

End Function

Creating the Schema

You must specify where to put the data in the document. For this article, an XML schema is used.

1. Open a word processor, such as Notepad, to a blank document.
2. Type or paste the following statements into the document:

   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
       xmlns = "urn:schemas-microsoft-com.office.demos.populate"
       targetNamespace = "urn:schemas-microsoft-com.office.demos.populate"
       elementFormDefault="qualified"> 
     <xsd:element name="Date" type="xsd:string"/>  
     <xsd:element name="FirstName" type="xsd:string"/> 
     <xsd:element name="LastName" type="xsd:string"/>  
     <xsd:element name="Title" type="xsd:string"/> 
     <xsd:element name="EmployeeID" type="xsd:string"/> 
   </xsd:schema>

3. Save the schema to the project folder as PopulateWordDocument.xsd.

In the following sections, you create two templates; one containing XML tags and one using bookmarks.

Note   The recommended method for importing data into a Word document is to use XML tags rather than bookmarks because tags are less prone to data entry errors if the text is moved or modified.

Creating the Word XML Template

Create the Word template containing XML tags. The Word template acts as a form letter to hold the data retrieved during the search.

1. Start Word and create a document.
2. Save the document as PopulateAWordDocumentXML.dot in the project folder.
3. Type or paste the following example text into the template:

   Northwind Traders

   123 Americas Ave

   Seattle, WA 98001

   Attention:

   Employee ID:

   Dear ,

      I just wanted to congratulate you on your promotion to          . We are sure that you will continue to exceed our expectations in your new position.

      Should you have any questions, please feel free to contact me at (555) 123-4567.

   Yours,

   Andrew Fuller

   V.P., Product Sales and Support

4. Next, attach a schema to the template.
5. On the File menu, click New to attach a schema to the template. In the New Document task pane, click XML document.
6. In the XML Structure task pane, click Templates and Add-Ins, and then click the XML Schema tab.
7. In the Checked schemas are currently attached box, select the XML schema you want to attach to the document. In this example, select PopulateWordDocument.xsd.

   Note   The list of schemas represents all the schemas that are available in the Schema Library. If the PopulateWordDocument.xsd schema does not appear in the list, add it by clicking Add Schema, browsing to the project folder containing the XSD file, highlighting the file, and clicking Open. In the Schema Settings dialog box in the Alias text box, type Populate a word document.

8. Click OK to close the Templates and Add-ins dialog box.

The XML Structure task pane opens.

The elements in the attached schema are listed in the Choose an element to apply to your current selection pane. As this is a relatively simple schema, all of the elements are at the same level and there is no top-level "containing" node.

Next, add elements to the template (see Figure 2).

To assign schema elements to locations in the template

1. First, add an element to the document to contain the day's date. Click in the template to place the insertion point one line below the sender's address.
2. In the Choose an element to apply to your current selection list, click Date.
3. Click Apply Only to Selection when the dialog box prompts you.

   An empty Date element appears in the template around the insertion point.

4. Click in the template to place the insertion point after the word Attention: and then add a space.
5. In the Choose an element to apply to your current selection list, click FirstName.

   An empty FirstName element appears in the template around the insertion point.

6. Move the insertion point to one space after FirstName and add the LastName element.
7. Move the insertion point to one space after Employee ID: and add the EmployeeID element.
8. Move the insertion point to one space after Dear and add the FirstName element.
9. Move the insertion point to one space after promotion to and add the Title element.
10. Save the template as PopulateAWordDocument.dot in the project folder.
11. Close the template. It should look like this:

    

    Figure 2. The XML template

Adding Bookmarks to the Word Template

Just as you can use XML tags to place data into a document, you can also use bookmarks to specify where in the template to put the data.

1. Start Word 2003 and create a new document.
2. Save the document as PopulateAWordDocumentBookmarks.dot to the project folder.
3. Type or paste the same example text you used in the previous template into this template (Creating the Word XML Template) and then move the insertion point so that it is one line below the sender's address.
4. On the Insert menu, click Bookmark.
5. In the Bookmark dialog box, type Date in the Bookmark name text box.
6. Click Add.
7. Move the insertion point to one space after Attention: and add a bookmark named FirstName.
8. After the FirstName bookmark, add a space, and then add a bookmark named LastName.
9. Move the insertion point to one space after Employee ID: and add a bookmark named EmployeeID.
10. Move the insertion point to one space after Dear and add a bookmark named FirstName1.
11. Move the insertion point to one space after promotion to and add a bookmark named Title.
12. Save and close the template.

Merging the Data with the XML Elements

The btnDisplayXM_Click event handler opens the PopulateAWordDocumentXML.dot template, which creates a new document. The code then moves through the XMLNodes in the document, testing each to determine its name, and then setting its value to the value of its corresponding text box on the form.

Private Sub btnDisplaywithXML_Click(ByVal sender As System.Object, 
ByVal e As System.EventArgs) Handles btnDisplaywithXML.Click
    Dim wdApp As New Word.Application
    Dim wdDoc As New Word.Document
    Dim cnt As Integer

    wdApp.Visible = True

    ' Modify the path to your own template as needed. The 
    ' default location is C:\Documents and Settings\
    ' <username>\Application Data\Microsoft\Templates.
    Dim wdTemp As String = "C:\Visual Studio Projects\PopulateAWordDocument\" & _
    "PopulateAWordDocumentXML.dot"

    ' Create a new document based on the template.
    wdDoc = wdApp.Documents.Add(wdTemp)

    ' Walk the XMLNodes and populate the tags with the search results.
    For cnt = 1 To wdDoc.XMLNodes.Count
        Select Case wdDoc.XMLNodes.Item(cnt).BaseName
            Case "Date"
                wdDoc.XMLNodes.Item(cnt).Text = Format(Now(), "d").ToString
            Case "EmployeeID"
                wdDoc.XMLNodes.Item(cnt).Text = Me.txtEmployeeID.Text
            Case "FirstName"
                wdDoc.XMLNodes.Item(cnt).Text = Me.txtFirstName.Text
            Case "LastName"
                wdDoc.XMLNodes.Item(cnt).Text = Me.txtLastName.Text
            Case "Title"
                 wdDoc.XMLNodes.Item(cnt).Text = Me.txtTitle.Text
            Case Else
                MsgBox("You have an XML tag in the document that " & _
                "is not mapped in the btnDisplay_Click event handler. " & _
                "Make sure that you have accounted for all tags " & _
                "in your procedure.")
        End Select
    Next

    ' Clean up.
    wdDoc = Nothing
    wdApp = Nothing

End Sub

Merging Data with the Bookmarks

When the user clicks the Write with bookmarks button, an instance of Word is started and made visible. A form letter is created based on the PopulateAWordDocumentBookmark.dot template. The search results displayed in each text box are then assigned to bookmarks in the document. Clicking the button does the following:

• Automates an instance of Word and make it visible
• Creates the file path to the template, which is used as a basis for the form letter
• Adds the new document
• Copies the fields from the text boxes into bookmarks in the document

The code for the event handler looks like this:

Private Sub btnDisplaywithBookmarks_Click(ByVal sender As System.Object, 
ByVal e As System.EventArgs) Handles btnDisplaywithBookmarks.Click
    Dim wdApp As New Word.Application
    Dim wdDoc As New Word.Document

    wdApp.Visible = True

    ' Modify the path to your own template as needed. The 
    ' default location is C:\Documents and Settings\
    ' <username>\Application Data\Microsoft\Templates.
    Dim wdTemp As String = "C:\Visual Studio Projects\PopulateAWordDocument\" & _
    "PopulateAWordDocumentBookmarks.dot"

    ' Add a new document based on EmployeeSearchBookmarks.dot template.
    wdDoc = wdApp.Documents.Add(wdTemp)

    Dim bookmarks As Word.Bookmarks = wdDoc.Bookmarks
    Dim bookmark As Word.Bookmark

    ' Copy the form fields to the document's bookmarks.
    wdDoc.Bookmarks.Item("Date").Range.Text() = Format(Now(), "d")
    wdDoc.Bookmarks.Item("EmployeeID").Range.Text() = txtEmployeeID.Text
    wdDoc.Bookmarks.Item("FirstName").Range.Text() = txtFirstName.Text
    wdDoc.Bookmarks.Item("LastName").Range.Text() = txtLastName.Text
    wdDoc.Bookmarks.Item("FirstName1").Range.Text() = txtFirstName.Text
    wdDoc.Bookmarks.Item("Title").Range.Text() = txtTitle.Text

    ' Clean up.
    wdDoc = Nothing
    wdApp = Nothing

End Sub

Adding Code for the Final Tasks

The final two procedures clear the text boxes on the Windows Form (so that the user can enter new search criteria) and close the form.

    Private Sub btnReset_Click(ByVal sender As System.Object, 
    ByVal e As System.EventArgs) Handles btnReset.Click
        txtEmployeeID.Clear()
        txtFirstName.Clear()
        txtLastName.Clear()
        txtTitle.Clear()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, 
    ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

Testing the Form

You can now test the form to make sure it displays data correctly based on the parameter you enter. You can also test to make sure that the data is merged correctly in the form letter.

To test the form

1. Press F5 to run the form.
2. When the form is displayed, type Davolio into the Last Name text box, and then click Search.

   The record containing the data for Nancy Davolio is displayed.

3. Click the Write with XML button to start Word and display the form letter with selected XML elements filled in.
4. Close Word without saving the document.
5. Click the Write with bookmarks button to start Word and display the form letter with selected bookmarked fields filled in.
6. Close Word without saving the document.
7. Click the Reset button and notice that the text boxes in the Windows Form are cleared.
8. Click the Close Form button. The form is closed.
9. Click the Build Solution item on the Build menu to compile your project and create an executable (EXE) file. This file is stored in the \bin directory in the project folder.

In an actual production environment, you should add comprehensive error handling code and conduct more extensive testing to ensure that the project compiles without error. After you do that, you also need to configure the project as a Release solution (in the article, we compiled the project as a Debug solution) before deploying the project. For more information, see Builds During Application Development.

Conclusion

When you finish with this project, you have a simple form that can be used to search a data source and display the results. You also have a form letter that displays the returned record. While this is a simple solution designed to illustrate a few data access and display techniques, it can also be the basis for a more detailed application. For example, you could develop an application that allows multiple records to be returned where the user can select which one to merge with the form letter. You could also develop an application to display employee IDs or names in a drop-down list box rather than having users enter them. In this scenario, the user can search for additional information about that employee and the developer can package the data search code into a class object, which can then be used by multiple forms.