By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

StringBuilder problem

P: n/a
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


Jul 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Never mind I see that the statement setting the length for the StringBuilder
Obj was the problem.
Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.