Strange Problem!


#1

Using the code below, I am creating multiple documents based on the records in the dranimals datareader. When I step through the code, it is picking up the correct records and when I look at the array of field values in the command window, these also have the correct values in - ie different values for each record in the datareader. It even produces the correct number of correctly named documents. However, all 3 documents are identical and do not reflect the different records.

Am I doing something wrong?

Many thanks

Shellie

Public Sub CreateCertificate()

Try

Dim NewDoc As New Aspose.Word.Document(Server.MapPath("SICCert.DOC"))

SQLString = "Select * from staapplication where appno = '" & NewAppNo & "'"

cmdApplication.CommandText = SQLString

cmdApplication.Connection = conSTC

daApplication.SelectCommand = cmdApplication

daApplication.Fill(dtApplication)

SQLString = "Select * from staapplicant where appno = '" & NewAppNo & "'"

cmdApplicant.CommandText = SQLString

cmdApplicant.Connection = conSTC

daApplicant.SelectCommand = cmdApplicant

daApplicant.Fill(dtApplicant)

SQLString = "Select * from staallergen where appno = '" & NewAppNo & "'"

cmdAllergen.CommandText = SQLString

cmdAllergen.Connection = conSTC

daAllergen.SelectCommand = cmdAllergen

daAllergen.Fill(dtAllergen)

SQLString = "Select * from staanimal where appno = '" & NewAppNo & "'"

cmdanimal.CommandText = SQLString

cmdanimal.Connection = conSTC

daAnimal.SelectCommand = cmdanimal

dtAnimal = cmdanimal.ExecuteReader

IssueDate = Today

ExpiryDate = Today.AddDays(364)

Dim i As Integer

i = 1

While dtAnimal.Read

' Create an array of the field names

'NB Field Names are case sensitive - have to be exactly the same as in word doc

Dim fieldNames() As String = {"AuthNo", "VetName", "PracticeName", "Address1", "Address2", "City", "County", "Postcode", "VMDVetNo", "ExpiryDate", "IssueDate", "RCVSNo", "SVetName", "SVetAddress1", "SVetAddress2", "SVetCity", "SVetCounty", "SVetPostcode", "ImporterName", "ImporterAddress1", "ImporterAddress2", "ImporterCity", "ImporterCounty", "ImporterPostcode", "CountryOrigin", "PrevSIC", "OwnerName", "OwnerAddress1", "AnimalName", "Species", "Breed", "Weight", "NoAnimals", "Dosage", "HoldingAddress1", "HoldingAddress2", "HoldingCity", "HoldingCounty", "HoldingPostcode", "FoodProducing", "Justification", "ProductName", "MaNo", "Manufacturer", "Active1", "Active2", "Active3", "PharmForm", "Indications", "Strength", "StrengthUnits", "TotalAmount", "AmountUnits"}

' Create an array of the field values

Dim fieldValues() As Object = {dtApplication.Rows(0).Item("Appno"), dtApplicant.Rows(0).Item("VetName"), dtApplicant.Rows(0).Item("Practice"), dtApplicant.Rows(0).Item("Address1"), dtApplicant.Rows(0).Item("Address2"), dtApplicant.Rows(0).Item("City"), dtApplicant.Rows(0).Item("County"), dtApplicant.Rows(0).Item("Postcode"), dtApplicant.Rows(0).Item("VetNo"), ExpiryDate, IssueDate, dtApplicant.Rows(0).Item("RCVSNo"), dtApplicant.Rows(0).Item("SVetName"), dtApplicant.Rows(0).Item("SVetAddress1"), dtApplicant.Rows(0).Item("SVetAddress2"), dtApplicant.Rows(0).Item("SVetCity"), dtApplicant.Rows(0).Item("SVetCounty"), dtApplicant.Rows(0).Item("SVetPostcode"), dtApplicant.Rows(0).Item("ImporterName"), dtApplicant.Rows(0).Item("ImporterAddress1"), dtApplicant.Rows(0).Item("ImporterAddress2"), dtApplicant.Rows(0).Item("ImporterCity"), dtApplicant.Rows(0).Item("ImporterCounty"), dtApplicant.Rows(0).Item("ImporterPostcode"), dtApplication.Rows(0).Item("staCountryOrigin"), dtApplication.Rows(0).Item("PrevSTANo"), dtAnimal.Item("animalOwner"), dtAnimal.Item("animalAddress"), dtAnimal.Item("AnimalName"), dtApplication.Rows(0).Item("staSpecies"), dtAnimal.Item("AnimalBreed"), dtAnimal.Item("animalWeight"), dtApplication.Rows(0).Item("staNoAnimals"), dtAnimal.Item("animalDosage"), dtApplicant.Rows(0).Item("HoldingAddress1"), dtApplicant.Rows(0).Item("HoldingAddress2"), dtApplicant.Rows(0).Item("HoldingCity"), dtApplicant.Rows(0).Item("HoldingCounty"), dtApplicant.Rows(0).Item("HoldingPostcode"), dtApplication.Rows(0).Item("staFoodProducing"), dtApplication.Rows(0).Item("staJustification"), dtApplication.Rows(0).Item("staProduct"), dtApplication.Rows(0).Item("staMAorigin"), dtApplication.Rows(0).Item("staManufacturer"), dtApplication.Rows(0).Item("staActive1"), dtApplication.Rows(0).Item("staActive2"), dtApplication.Rows(0).Item("staActive3"), dtApplication.Rows(0).Item("staPharmForm"), dtApplication.Rows(0).Item("staIndications"), dtApplication.Rows(0).Item("staStrength"), dtApplication.Rows(0).Item("staStrengthUnits"), dtApplication.Rows(0).Item("staTotalAmount"), dtApplication.Rows(0).Item("staAmountUnits")}

'Fill the fields in the document with user data.

NewDoc.MailMerge.Execute(fieldNames, fieldValues)

'Save the document

DocName = Right(NewAppNo, 5) & i & ".doc"

' NewDoc.Save(DocName, Aspose.Word.SaveFormat.FormatDocument, Aspose.Word.SaveType.OpenInWord, Response)

NewDoc.Save(Server.MapPath(DocName), Aspose.Word.SaveFormat.FormatDocument)

i = i + 1

End While


#2

Mail merge actually replaces the merge fields with values. So if the first mail merge was successful and replaced all merge fields with values, you can't really mail merge into the same Document object anymore because there are no more merge fields in it. The proper way to do this is to open a new Document object before every mail merge. If the performance is critical a slightly faster option could be to open a document once but Document.Clone before every mail merge.


#3

Works perfectly!

Thanks

Shellie