Creating a Search Application with Word 2003 and Visual Basic .NET
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
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)
Frank Rice, Microsoft Corporation
Applies to: Microsoft Office Word 2003, Microsoft Office Access 2003, Microsoft Visual Basic .NET
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.
The event procedures in the sample files in the download sample assume that your project is located at C:\Visual Studio Project\PopulateAWordDocument. You need to change the path to point to your own project.
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
The first step is to create a Windows Form.
To create the project and form
Start Microsoft Visual Studio .NET.
From the File menu, point to New, and then choose Project. The New Project dialog box is displayed.
In the Project Types pane, choose Visual Basic Projects.
In the Templates pane, choose Windows Application for Visual Basic projects.
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.
After you assign a name, click OK to create the project.
Visual Studio displays a new form in the Windows Form Designer.
In the Solution Explorer, right-click Form1.vb and rename it SelectRecords.vb.
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.
In the Properties window, rename the Text and Name properties to SelectRecords.
In the Solution Explorer, right-click the project you named in step 5 (PopulateAWordDocument, in this case), and select Properties.
In the <project name> Property Pages dialog box, select SelectRecords in the Start up object drop-down box, and click OK.
In the Solution Explorer, right-click SelectRecords.vb and click View Code.
Add the following namespace statements before the Public Class SelectRecords line:
Imports System.Data.OleDb Imports Word = Microsoft.Office.Interop.Word Imports System.Xml
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
Select References in the Solution Explorer window.
Open the Project menu and then click Add Reference. The Add Reference dialog box appears.
Click the COM tab.
Select Microsoft Word 11.0 Object Library in the Component Name list and then click Select.
Click OK to add the reference to your project.
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
Add the following controls to the form, naming them as indicated:
Allows users to enter search criteria for Employee ID and display the results
Allows users to enter search criteria for the employee's first name and display the results
Allows users to enter search criteria for the employee's last name and display the results
Allows users to enter search criteria for the employee's job title and display the results
Runs the search query
Starts Word and populates the form letter using XML tags
Write with XML
Starts Word and populates the form letter using bookmarks
Write with bookmarks
Clears the text boxes on the Windows Form
Closes the Windows Form
Add labels in front of the text boxes to indicate their function, if desired.
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.
Make sure that the connection string in the procedure points to the location of your 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.
You should always be cautious when using SQL statements directly in your code. These statements can expose SQL injection vulnerabilities that persons with malicious intent can use to exploit your application.
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
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.
You should always be cautious when using SQL statements directly in your code. These statements can expose SQL injection vulnerabilities that persons with malicious intent can use to exploit your application.
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
You must specify where to put the data in the document. For this article, an XML schema is used.
Open a word processor, such as Notepad, to a blank document.
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>
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.
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.
Create the Word template containing XML tags. The Word template acts as a form letter to hold the data retrieved during the search.
Start Word and create a document.
Next, attach a schema to the template.
On the File menu, click New to attach a schema to the template. In the New Document task pane, click XML document.
In the XML Structure task pane, click Templates and Add-Ins, and then click the XML Schema tab.
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.
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.
Save the document as PopulateAWordDocumentXML.dot in the project folder.
Type or paste the following example text into the template:
123 Americas Ave
Seattle, WA 98001
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.
V.P., Product Sales and Support
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
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.
In the Choose an element to apply to your current selection list, click Date.
Click Apply to Selection Only when the dialog box prompts you.
An empty Date element appears in the template around the insertion point.
Click in the template to place the insertion point after the word Attention: and then add a space.
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.
Move the insertion point to one space after FirstName and add the LastName element.
Move the insertion point to one space after Employee ID: and add the EmployeeID element.
Move the insertion point to one space after Dear and add the FirstName element.
Move the insertion point to one space after promotion to and add the Title element.
Save the template as PopulateAWordDocument.dot in the project folder.
Close the template. It should look like Figure 2.
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.
Start Word 2003 and create a new document.
Save the document as PopulateAWordDocumentBookmarks.dot to the project folder.
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.
On the Insert menu, click Bookmark.
In the Bookmark dialog box, type Date in the Bookmark name text box.
If you do not see the bookmark placeholders, click Tools, click Options, click the View tab. and then in Show options, click Bookmarks.
Move the insertion point to one space after Attention: and add a bookmark named FirstName.
After the FirstName bookmark, add a space, and then add a bookmark named LastName.
Move the insertion point to one space after Employee ID: and add a bookmark named EmployeeID.
Move the insertion point to one space after Dear and add a bookmark named FirstName1.
Move the insertion point to one space after promotion to and add a bookmark named Title.
Save and close the template.
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
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 the following:
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
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
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
Press F5 to run the form.
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.
Click the Write with XML button to start Word and display the form letter with selected XML elements filled in.
Close Word without saving the document.
Click the Write with bookmarks button to start Word and display the form letter with selected bookmarked fields filled in.
Close Word without saving the document.
Click the Reset button and notice that the text boxes in the Windows Form are cleared.
Click the Close Form button. The form is closed.
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.
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.
You can find out more information about automation and data access at the following locations:
Working with Office Applications (http://msdn.microsoft.com/library/en-us/modcore/html/deovrWorkingWithOfficeApplications.asp)