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