Adding a Numbered List to Word Document

[WMXTeam]

I have been working on a program over the last several days that that opens up an existing Word Document, goes to a certain place in the document, and creates a numbered list from a SQL Query. Everything is working great except the list is created in the document in the reverse order that it is retrieved from the database. For example: the SQL query retrieves the records in the following order:

Record 1
Record 2
Record 3

The list in the word document displays as:

1) Record 3
2) Record 2
3) Record 1

I have been unable to determine what I am doing wrong. Here is my code. Thank you for your help.

Public Sub addTableVendorClar(ByRef oWord As Word.Application, ByRef oWordDoc As Word.Document)
Dim bolFound As Boolean = False
Dim rngRange As Word.Range = oWordDoc.Range(0, 0)

With rngRange.Find
.ClearFormatting()
.Replacement.ClearFormatting()
.Text = "[tableVendorClar]"
.Wrap = Word.WdFindWrap.wdFindContinue
.MatchCase = False
End With
bolFound = rngRange.Find.Execute()

If bolFound Then
rngRange.Delete(Unit:=Word.WdUnits.wdCharacter, Count:=1)
' create outline
With oWord.ListGalleries(2).ListTemplates(1).ListLevels(1)
.NumberFormat = "%1)"
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With
oWord.ListGalleries(2).ListTemplates(1).Name = ""
rngRange.ListFormat.ApplyListTemplateWithLevel(ListTemplate:= _
oWord.ListGalleries(2).ListTemplates(1))

strSql = strSQLVenClar
If connStr.State <> ConnectionState.Open Then
connStr.Open()
End If
Dim cmd1 As New System.Data.SqlClient.SqlCommand(strSql, connStr)
cmd1.CommandTimeout = CommandTimeoutV
dr = cmd1.ExecuteReader

Dim i As Integer = 1
Dim strText As String
While dr.Read()
If i <> 1 Then
rngRange.FormattedText.Text = Chr(10)
End If
strText = dr.Item("ClarificationText").ToString
rngRange.FormattedText.Text = strText

i += 1
End While

cmd1.Connection.Close()
connStr.Close()
dr.Close()

End If
End Sub

 

[jebenson]

Maybe instead of a DataReader use a DataTable and iterate through the data in reverse order.

For r as Integer = DataTable1.Rows.Count - 1 to 0 Step -1

'your code here

Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[WMXTeam]

Thanks for your help. I was able to figure it out. The issue was "rngRange.FormattedText.Text". I changed this to "rngRange.Text" and it worked. I had to make some other changes to get it to worked exactly as needed. My updated code is below.

Public Sub addTableVendorClar(ByRef oWord As Word.Application, ByRef oWordDoc As Word.Document)
Dim bolFound As Boolean = False
Dim rngRange As Word.Range = oWordDoc.Range(0, 0)
Dim dataSet As New DataSet()
Dim dataRow As DataRow

With rngRange.Find
.ClearFormatting()
.Replacement.ClearFormatting()
.Text = "[tableVendorClar]"
.Wrap = Word.WdFindWrap.wdFindContinue
.MatchCase = False
End With
bolFound = rngRange.Find.Execute()

If bolFound Then
rngRange.Delete(Unit:=Word.WdUnits.wdCharacter, Count:=1)
' create outline
With oWord.ListGalleries(2).ListTemplates(1).ListLevels(1)
.NumberFormat = "%1)"
.ResetOnHigher = 0
.StartAt = 1
.LinkedStyle = ""
End With
oWord.ListGalleries(2).ListTemplates(1).Name = ""
rngRange.ListFormat.ApplyListTemplateWithLevel(ListTemplate:= _
oWord.ListGalleries(2).ListTemplates(1))

strSql = strSQLVenClar
If connStr.State <> ConnectionState.Open Then
connStr.Open()
End If
Dim cmd1 As New System.Data.SqlClient.SqlCommand(strSql, connStr)
cmd1.CommandType = CommandType.Text

Dim sqlDA As New SqlDataAdapter()
sqlDA.SelectCommand = cmd1
sqlDA.Fill(dataSet)

Dim i As Integer = 1
Dim strText As String
Dim intCnt As Integer = dataSet.Tables(0).Rows.Count

If dataSet.Tables(0).Rows.Count > 0 Then
intCnt = dataSet.Tables(0).Rows.Count
For Each dataRow In dataSet.Tables(0).Rows

strText = dataRow("ClarificationText").ToString
If i = dataSet.Tables(0).Rows.Count Then
rngRange.Text = strText
Else
rngRange.Text = strText & vbCr
End If
rngRange.Collapse(Word.WdCollapseDirection.wdCollapseEnd)

i += 1
Next
End If

dataSet.Dispose()

cmd1.Connection.Close()
connStr.Close()

End If
End Sub