How to export data into a word document, not the SQL Query? VB.NET

Question

I have been having a difficult time trying to figure this out. I wrote a SQL query to select certain data that has a relationship to a particular institution. Now the SQL query works perfectly fine as I tested it in MySQL Workbench, however, when I try to export that data from VB.NET onto a word document, it literally prints out the SQL.

Below is my code:

    Dim sqlAdapter As New MySqlDataAdapter
    Dim sqlCommand As New MySqlCommand
    Dim sqlTable As New DataTable

    Dim sqlFundText As String = "select mutual_Fund_name, concat(contact_first_name,' ',contact_last_name) from mutual_fund mf, contact c, mutual_fund_has_contact mfhc, institution i, institution_has_mutual_Fund ihmf where mf.mutual_fund_id = mfhc.mutual_fund_id and c.contact_id = mfhc.contact_id and ihmf.mutual_fund_id = mf.mutual_fund_id and i.institution_id = ihmf.institution_id and i.institution_name ='" & InstitutionNameTextBox.Text & "' order by mutual_fund_name;"

    With sqlCommand
        .CommandText = sqlFundText
        .Connection = sConnection
    End With

    With sqlAdapter
        .SelectCommand = sqlCommand
        .Fill(sqlTable)
    End With

    oPara9 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
    With oPara9
        .Range.Font.Bold = False
        .Range.Text = sqlFundText
        .Range.Font.Size = 10
        .Format.SpaceAfter = 5
        .Range.InsertParagraphAfter()
    End With

And the result is:

As you can see it prints out the SQL statement.

I know it has to do with the

.Range.Text = sqlFundText

I just do not know how to fix it. Can anyone direct me the right way in fixing this?

Answer 1

Here is a Sub() that accepts SQL and returns CSV. It isn't bullet proof but it works for my utility code.

In your case you could use tab as delimiter so that once the data is present in Word it can easily be converted to a table manually.

Yu could also use the code to create/populate a table in Word.

   Function CSVReportFromSQL(SQL As String, Optional ToFilePath As String = "") As String
    ' note return differences if ToFilePath is provided
    '   If ToFilePath: check for 'OK' on return
    '   Else check return length = 0 for error 
    Try
        Dim sOut As String = ""

        Using con As New SqlConnection(g.OISConnectString)
            Dim command As New SqlCommand(SQL, con)
            con.Open()

            ' column headers
            Dim rdr As SqlDataReader = command.ExecuteReader(CommandBehavior.SchemaOnly)
            For i As Integer = 0 To rdr.FieldCount - 1
                sOut &= "," & rdr.GetName(i)
            Next
            sOut = sOut.Substring(1) & vbCrLf
            rdr.Close()

            rdr = command.ExecuteReader()
            While rdr.Read()
                For i As Integer = 0 To rdr.FieldCount - 1
                    'Debug.Print(rdr.GetFieldType(i).Name & " " & rdr.GetName(i))
                    'http://msdn.microsoft.com/en-us/library/4e5xt97a(v=vs.80).aspx
                    Select Case rdr.GetFieldType(i).Name
                        Case "String", "DateTime"
                            sOut &= """" & rdr(i).ToString.Replace("""", """""") & ""","
                        Case Else
                            sOut &= rdr(i).ToString & ","
                    End Select
                Next
                sOut &= vbCrLf
            End While
            rdr.Close()
        End Using

        If ToFilePath.Length > 0 Then
            My.Computer.FileSystem.WriteAllText(ToFilePath, sOut, False)
            Return "OK"
        Else
            Return sOut
        End If

    Catch ex As Exception
        If ToFilePath.Length > 0 Then
            Return ex.Message
        Else
            MsgBox("Problem creating CSV Report" & vbCrLf & ex.Message)
            Return ""
        End If
    End Try
End Function

Answer 2

The data from your query is in sqlTable. You'll need to extract the data from the data table and add that to your document instead of sqlFundText.

After your With sqlAdapter ... End With block you'll need to do something like:

Dim fundName as String
Dim contactName as String
For Each row in sqlTable.Rows
    fundName = row[0].ToString()
    contactName = row[1].ToString()
    ' Do something to put fundName and contactName into the document
Next