' Insert TC field to build TOC. Insert TC only if this is the first time when City occur.

'If mCity <> currentCity Then

' Get current cell and move builder to the beginning of it.

' Dim cell As Cell = DirectCast(builder.CurrentParagraph.GetAncestor(NodeType.Cell), Cell)

' builder.MoveTo(cell.FirstParagraph.FirstChild)

' builder.InsertField(String.Format("TC ""{0}"" \l 1", args.FieldValue))

'End If

Dim doc As New Document("d:\hart11\templates\canvassing.doc")

Dim adapterTemplate1 As New SQLDataAdapter("SELECT '' AS SalesAmount, ViewContact.CompanyTxt AS Company, '' AS EmploymentThisSite, case when len(ltrim(rtrim(ViewContact.FaxDirectTxt)))='10' then ''+SUBSTRING(ViewContact.FaxDirectTxt,1,3)+'.'+''+SUBSTRING(ViewContact.FaxDirectTxt,4,3)+'.'+SUBSTRING(ViewContact.FaxDirectTxt,7,4) else ViewContact.FaxDirectTxt end AS Fax, '' AS Ownership, case when len(ltrim(rtrim(ViewContact.PhoneDirectTxt)))='10' then ''+SUBSTRING(ViewContact.PhoneDirectTxt,1,3)+'.'+''+SUBSTRING(ViewContact.PhoneDirectTxt,4,3)+'.'+SUBSTRING(ViewContact.PhoneDirectTxt,7,4) else ViewContact.PhoneDirectTxt end AS Phone, '' AS [Plant/FacilitySize], case when len(ltrim(rtrim(ViewContact.PhoneGeneralTxt)))='10' then ''+SUBSTRING(ViewContact.PhoneGeneralTxt,1,3)+'.'+''+SUBSTRING(ViewContact.PhoneGeneralTxt,4,3)+'.'+SUBSTRING(ViewContact.PhoneGeneralTxt,7,4) else ViewContact.PhoneGeneralTxt end AS Phone2, ViewContact.WebURL AS WebAddress, '' AS YearEstablished, '' AS OwnsRents, ViewContact.Address1Txt AS Address1, ViewContact.Address2Txt AS Address2, ViewContact.CityTxt As City, ViewContact.StateCode AS State, ViewContact.PostalCodeTxt As Zip, ViewContact.ShippingAddress1Txt AS AltAddress, ViewContact.ShippingCityTxt As AltCity, ViewContact.ShippingStateCode AS AltState, ViewContact.ShippingPostalCodeTxt AS AltZip, ViewContact.CountyTxt AS County, ViewContact.ContactSIC1Code AS SIC1, '' AS SIC1Description, ViewContact.ContactSIC2Code AS SIC2, '' AS Sic2Description, ViewContact.ContactSIC3Code AS SIC3, '' AS Sic3Description, ViewContact.ContactParentCompanyTxt AS Parent, ViewContact.PrefixTxt AS Prefix, ViewContact.FirstTxt AS First, ViewContact.LastTxt As Last, ViewContact.SuffixTxt AS Suffix, ViewContact.TitleTxt AS Title, ViewContact.DivisionTxt AS Division, Contact.CanvassingTemplate1 FROM ViewContact INNER JOIN Contact ON Contact.ContactID = ViewContact.ParentContactID INNER JOIN ExportDetail ON ExportDetail.ExportPrimaryRecordID = ViewContact.ContactID INNER JOIN ExportMaster ON ExportMaster.ExportID = ExportDetail.ExportID WHERE ExportDetail.ExportID='" & Request.QueryString("ID") & "' UNION SELECT [Sales Amount] AS SalesAmount, Company, EmploymentThisSite, case when len(ltrim(rtrim(REPLACE(Fax,'-',''))))='10' then ''+SUBSTRING(REPLACE(Fax,'-',''),1,3)+'.'+''+SUBSTRING(REPLACE(Fax,'-',''),4,3)+'.'+SUBSTRING(REPLACE(Fax,'-',''),7,4) else Fax end AS Fax, Ownership, case when len(ltrim(rtrim(REPLACE(Phone,'-',''))))='10' then ''+SUBSTRING(REPLACE(Phone,'-',''),1,3)+'.'+''+SUBSTRING(REPLACE(Phone,'-',''),4,3)+'.'+SUBSTRING(REPLACE(Phone,'-',''),7,4) else Phone end AS Phone, [Plant/FacilitySize], case when len(ltrim(rtrim(REPLACE(TollFree,'-',''))))='10' then ''+SUBSTRING(REPLACE(TollFree,'-',''),1,3)+'.'+''+SUBSTRING(REPLACE(TollFree,'-',''),4,3)+'.'+SUBSTRING(REPLACE(TollFree,'-',''),7,4) else TollFree end AS Phone2, WebAddress, YearEstablished, OwnsRents, MailAddress AS Address1, '' AS Address2, MailCity AS City, MailState AS State, MailZipPlusExtension AS Zip, StreetAddress AS AltAddress, StreetCity AS AltCity, StreetState AS AltState, StreetZip AS AltZip, StreetCounty AS County, PrimarySic AS SIC1, PrimarySicDescription AS SIC1Description, Sic2 AS SIC2, Sic2Description, Sic3 AS SIC3, Sic3Description, ParentName AS Parent, RankedExecutive1Prefix AS Prefix, RankedExecutive1FirstName AS First, RankedExecutive1LastName AS Last, RankedExecutive1Suffix AS Suffix, RankedExecutive1TitleDescription AS Title, '' AS Division, '' AS CanvassingTemplate1 FROM ContactHarris INNER JOIN ExportMaster ON ExportMaster.ExportID = ContactHarris.ExportID WHERE ContactHarris.ExportID='" & Request.QueryString("ID") & "' AND ContactHarris.Duplicate='' ORDER BY City ASC", myConnection)

Dim dtTemplate1 As New DataTable()

adapterTemplate1.Fill(dtTemplate1)

dtTemplate1.TableName = "Data"

doc.MailMerge.FieldMergingCallback = New HandleMergeFieldCanvassing()

doc.MailMerge.ExecuteWithRegions(dtTemplate1)

doc.MailMerge.Execute(New String() {"Canvassed", "Salesperson"}, New Object() {rcbExportNameTxt.Text, rcbPreparedFor.Text})

doc.UpdatePageLayout())

doc.Save(SavePath)

Private Class HandleMergeFieldCanvassing

Implements IFieldMergingCallback

Private Sub FieldMerging(args As FieldMergingArgs) Implements IFieldMergingCallback.FieldMerging

Dim builder As New DocumentBuilder(args.Document)

If args.FieldName = "City" Then

Dim currentCity As String = args.FieldValue.ToString()

builder.MoveToField(args.Field, False)

mRecordIndex += 1

If (Not String.IsNullOrEmpty(mCity) AndAlso mCity <> currentCity) OrElse mRecordIndex > 2 Then

mRecordIndex = 0

builder.CurrentParagraph.ParagraphFormat.PageBreakBefore = True

End If

mCity = currentCity

End If

If args.FieldName = "Company" Then

builder.MoveToField(args.Field, False)

builder.InsertField(String.Format("XE ""{0}"" \b", args.FieldValue))

End If

End Sub

Private Sub ImageFieldMerging(e As ImageFieldMergingArgs) Implements IFieldMergingCallback.ImageFieldMerging

' Do nothing.

End Sub

Private mCity As String

Private mRecordIndex As Integer

End Class