PDA

View Full Version : string manipulation problem



csiszarg
10-02-2009, 12:19 AM
Hi,

I am working with Business Objects VBA and user forms, but I didn't find a user form topic, so I post my message here in the excel topic.

In order to solve an issue I have to manipulate a long string (more than a thousand character, but not reaching ten thousand character), which is an SQL statement.

The first problem I face with is the following: I load the parts of the select statement into a string array (which is a global string array declared in a module), and when I switch between the forms, suddenly I find that an end of file character is inserted at the end of each string in the string array, except for the first element. The declaration of the string is the following:


Type SelectStatementType
NbRows As Integer
SelStat(99, 2) As String
DotPlace(99) As Integer
End Type
Global SS As SelectStatementType

The code I use for "string cleaning" is the following:


For i = 2 To SS.NbRows
SS.SelStat(i - 1, 0) = Mid(SS.SelStat(i - 1, 0), 1, Len(SS.SelStat(i - 1, 0)) - 1)
Next

The next problem is that after I did the modifications on the appropriate parts of the select statement, I have to load back the whole SQL statement into a textbox. This SQL statement can be very long, reaching 4000-5000 character in length. Sometimes I can insert only the select statement and VBA don't concatenate it with the string after the select part (I call this part SQLAfterFrom clause). I copy here the code I use to create the sql statement.


selectstatement = ""
For i = 1 To SS.NbRows
If i = 1 Then
selectstatement = SS.SelStat(i - 1, 0)
Else
selectstatement = selectstatement & Chr(13) & "," & SS.SelStat(i - 1, 0)
End If
Next
SQLAfterFrom = Mid(AnFvParam_02.SQLText.Text, InStr(AnFvParam_02.SQLText.Text, "FROM"))
SQLWholeText = Replace("SELECT " & selectstatement & SQLAfterFrom, "¶", Chr(13))
SQLText.Text = SQLWholeText
MsgBox (SQLWholeText)

Any advice on what I am doing wrong would be highly appreciated.

Thanks,
Gabor

Bob Phillips
10-02-2009, 01:38 AM
Hi Gabor,

I am afarid I have absolutely no idea what the problem is, but I also have no idea what you are trying to do. But loading a 4-5K string into a textbox doesn't seem overly smart to me, how can anyone possibly absorb that there. I would suggest the whole approach needs a re-think.

csiszarg
10-02-2009, 01:53 AM
Hi Xld,

Meanwhile I solved the problem myself. The problem lied in the fact that when I assign value to a textbox, like this:



For i = 1 To SS.NbRows
If LBFrom.List(LBFrom.ListIndex) = SS.SelStat(i - 1, 1) Then
AnFvParam_02.SelectLineTextBox.Text =SS.SelStat(i - 1, 0)
End If
Next i


Then VBA attach an end of file character to the string (SS.SelStat(i-1,0)), which makes impossible any kind of concatenation, replacing, etc. So I needed to create a string to temporarily store the SS.SelStat(i-1,0) in it, and so I avoid this error.

Gabor

Bob Phillips
10-02-2009, 03:01 AM
Solving the (immediate) problem doesn't change my advice, I think you are storing up problems.