Question Exporting SQL data to Word

[cooxy]

Hello,

My aim is to export data from my SQL database into a word document. I have managed to export data into the word document from my database however if I was to go back onto the word form and type another service ID from the database and click export to word document it would export the same ID as before. However if I re-close my program and type another service ID then it will use that service ID. I believe that the problem lies with the SQL command and the command not resetting or updating every time it is exported into word. I have also ran the debugger which shows me that the ID's are changing to the correct value. Below is my code:

Public Sub Word_Click(sender As Object, e As EventArgs) Handles Word.Click

Dim oPara1 As Word.Paragraph, oPara2 As Word.Paragraph
Dim tbuser As String
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oTable As Word.Table
tbuser = TBuserID.Text

Const wdOrientLandscape = 1


oWord = CreateObject("Word.Application")
oWord.Visible = True
oDoc = oWord.Documents.Add
oDoc.PageSetup.Orientation = wdOrientLandscape


oPara1 = oDoc.Content.Paragraphs.Add
oPara1.Range.Text = "ITIL Services"
oPara1.Format.SpaceAfter = 24 '24 pt spacing after paragraph.
oPara1.Range.InsertParagraphAfter()

oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
oPara2.Range.InsertParagraphBefore()
oPara2.Range.Text = "Application Portfolio"
oPara2.Format.SpaceAfter = 5
oPara2.Range.InsertParagraphAfter()


oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 3, 7)
oTable.Range.ParagraphFormat.SpaceAfter = 6

oTable.Rows(1).Range.Font.Bold = True
oTable.Cell(1, 1).Range.Text = "ID"
oTable.Cell(1, 2).Range.Text = "Service Name"
oTable.Cell(1, 3).Range.Text = "Application Name"
oTable.Cell(1, 4).Range.Text = "Application Type"
oTable.Cell(1, 5).Range.Text = "Cost"
oTable.Cell(1, 6).Range.Text = "Year Released"
oTable.Cell(1, 7).Range.Text = "Version"
oTable.Cell(2, 1).Range.Text = getdatatest(tbuser, 1)
oTable.Cell(2, 2).Range.Text = getdatatest(tbuser, 2)
oTable.Cell(2, 3).Range.Text = getdatatest(tbuser, 3)
oTable.Cell(2, 4).Range.Text = getdatatest(tbuser, 4)
oTable.Cell(2, 5).Range.Text = getdatatest(tbuser, 5)
oTable.Cell(2, 6).Range.Text = getdatatest(tbuser, 6)
oTable.Cell(2, 7).Range.Text = getdatatest(tbuser, 7)
oTable.Rows.Item(1).Range.Font.Italic = True
closecon()
End Sub
End Class

I have also used a function which holds the dataset.

Function getdatatest(ByVal recordnum As Integer, ByVal fieldnum As Integer)
Dim recordnum2 As String
recordnum2 = Wordexport.TBuserID.Text


Dim dbtable
dbtable = "application_portfolio"


Dim sql As MySqlCommand
sql = New MySqlCommand("Select * from application_portfolio where service_name = '" & Wordexport.tbservicename.Text & "' ", dbcon)


Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
DataAdapter1.SelectCommand = sql


DataAdapter1.Fill(ds, dbtable)


dbcon.Close()
Return ds.Tables(dbtable).rows(recordnum - recordnum2).Item(fieldnum - 1)


End Function

Thank you in advance for your help.

 

[cooxy]

I have had another go at tackling my problem and I now believe the problem lies where I have created the word.application object.

oWord = CreateObject("Word.Application")

This code is creating an object but the object is not emptying after it has been used. I have read up on emptying objects created and most of them say set the object to nothing or dispose the object. However I have tried this and it still doesn't work. Another interesting point is that when I enter an ID it will display that ID the first time and then if I enter something random which doesn't belong to my database it will still be cached and display the first ID I used.

If someone could please assist me with some guidance as this project is for my Final Year Project at University and this solution will clear up a few bugs in my program. Thanks in advance.

 

[cooxy]

I have resolved the problem It wasn't the word application, oword which had to be reset. The problem lied with the dataset having to be reset. DS which is used in the function is a global dataset this is declared. At the start of that function I reset the dataset and now my problem is resolved. I knew something had to be reset and finally got there.