Ensure String Character Count doesn't exceed 255 Characters

[Stuugie]

Hi all,

I have a form that is supposed to query a website. I have the following code that first looks at and uses data from an SQL Server db connection:

ConnString = "Data Source=SQL2008T1;Initial Catalog=EconAnalysis;Integrated Security=True"
            SCqryStr = ""
            SQLConn.ConnectionString = ConnString
            SQLConn.Open()
            SQLStr = "SELECT vSeries_Number FROM tblVSeriesList"
            SQLCmd.Connection = SQLConn
            SQLCmd.CommandText = SQLStr
            SQLdr = SQLCmd.ExecuteReader
            tpCnt = 0
            x = ""
            'If SQLdr.HasRows Then
            If SQLdr.HasRows Then
                While SQLdr.Read()
                    SCqryStr = SCqryStr + SQLdr.GetString(0) 'SQLdr(SQLdr(0))
                    SCqryStr = SCqryStr + ","
                End While
            End If
            SQLdr.Close()
            SQLConn.Close()

The problem is that there are over 2000 values being placed into the string SCqryStr and then those values are supposed to be passed into a textbox from the website. There are too many values being placed into the text box and my processes to set attributes and invoke clicks are receiving errors because of it.

How can I write an If statement that looks at the length of the string to ensure that it is less than 255 characters? The only hitch is that once the maximum character count is reached, I don't want to pass an incomplete value to the string. As an example, each value being passed looks something like v54027307 and I can't pass the value if it is v54027 instead of v54027307. So not only do I need to check on the character count but I also need to keep each value intact. Any ideas?

One last issue is the last character is always a comma because of

SCqryStr = SCqryStr + ","

How can I remove the last comma of the string?
Any help or pushes will be most appreciated.

[Reverend Jim]

First of all, I would use StringBuilder instead of String if you are doing a lot of concatenating (initialize the StringBuilder object to be large to avoid multiple reallocations). Strings have to be reallocated every time you concatenate. StringBuilder objects do not.

Next, I would concatenate "," plus the new string value, then at the end I would do a SubString to remove the first ",". You won't know the length of your string until you are out of the loop but why are you concerned about 255 characters?

If you declare the StringBuilder as

Dim sb As New System.Text.StringBuilder(10000)

then the initial max size of sb is 10000 chars. It will increase automatically if that limit is reached. You can get the length by

sb.Length

[Stuugie]

Thanks for that Reverend Jim. I like the idea behind string builder compared to using a string and will amend my code accordingly.

[Pgmer]

Jim I agree with you as stuugie.. I liked the way you explained solution with explaination. Hope you give such technical explanation in your solution in the threads you reply.

[Reverend Jim]

Hope you give such technical explanation in your solution in the threads you reply.

It varies. If I'm trying to dash off a quick answer before I rush out the door then the answers are shorter. If it's 3:00 in the morning and I can't sleep I tend to go on at length. Unfortunately, that's also the time I can misread a question and totally miss the boat. Fortunately there is no shortage of people to keep me honest.