StringBuilder problem

  • Thread starter Thread starter kevininstructor
  • Start date Start date
K

kevininstructor

I found code on the web which takes data from an MS-Access table (my test
table has three rows by five fields) copies it to the Clipboard then into a
range within MS-Excel. Data is stored in a StringBuilder object and should
paste into Excel via Clipboard.SetDataObject. When it comes time to paste
the StringBuilder object is empty. If I strip all database code out and
place the code in another project it works fine. Any ideas? Thanks for your
time, Kevin


Problem code
Private Sub ConvertIt(ByVal aWorksheet As Excel.Worksheet, ByVal aRange As
Excel.Range)
Dim sbExcelData As New System.Text.StringBuilder
Dim Cmd As OleDbCommand
Dim Reader As OleDbDataReader
Dim Con As OleDbConnection

sbExcelData.Length = 2000
Try
Con = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data
source=" & GetAppPath() & "\test.mdb")
Cmd = New OleDbCommand("SELECT * FROM [tbContacts]", Con)
Con.Open()
Try
Try
Reader = Cmd.ExecuteReader()

While Reader.Read()
'sbExcelData.Append(Reader("ID").ToString)
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Company Name"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Contact Name"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Address"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(vbCrLf)

sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append(vbCrLf)
End While
Catch ex As OleDbException
MessageBox.Show("Problem with executing the query" & vbCrLf &
ex.Message & vbCrLf & "Can not continue")
End Try
Finally
Con.Close()
End Try
Catch ex As OleDbException
MessageBox.Show("DB Error")
Exit Sub
End Try
MessageBox.Show("theExcelData=" & sbExcelData.ToString)
Clipboard.SetDataObject(sbExcelData.ToString)

aRange.Select()
aWorksheet.Paste()
End Sub

Stripped out code that holds StringBuilder values
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim sbExcelData As New System.Text.StringBuilder
Dim iInsance As Integer
sbExcelData.Length = 2000

For iInsance = 1 To 10
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append(vbCrLf)
Next
Console.WriteLine("theExcelData=" & sbExcelData.ToString)
Clipboard.SetDataObject(sbExcelData.ToString)
End Sub


Downloadable project
http://home.teleport.com/~kevininstructor/dotnet/sb.zip
 
Never mind I see that the statement setting the length for the StringBuilder
Obj was the problem.
 
Back
Top