Ensure String Character Count doesn't exceed 255 Characters

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:

Code:

---

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

Code:

---

SCqryStr = SCqryStr + ","

---

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

Code:

---

If  SCqryStr.Length + SQLdr.GetString(0).Length <256 Then
  'append the data
Else
  'do not append the data
End If

---

To remove the last character

Code:

---

SCqryStr=SCqryStr.SubString(1,SCqryStr.Length-1)

---

Looks good to me DataMiser, thanks biggie-big for your help!

Almost solved but not quite! I'm going to give the full code of what I am doing

Code:

---

Private Sub WebBrowser1_DocumentCompleted(ByVal sender As System.Object, ByVal e As System.Windows.Forms.WebBrowserDocumentCompletedEventArgs) Handles WebBrowser1.DocumentCompleted
        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
        If SQLdr.HasRows And pgCnt = 0 Then
            While SQLdr.Read()
                If SCqryStr.Length + SQLdr.GetString(0).Length < 256 Then
                    SCqryStr = SCqryStr + SQLdr.GetString(0)
                    SCqryStr = SCqryStr + ","
                Else

                End If
            End While
        End If
        SQLdr.Close()
        SQLConn.Close()
        pgCnt = pgCnt + 1
        If WebBrowser1.ReadyState = WebBrowserReadyState.Complete Then
            Select Case pgCnt
                Case 1
                    With WebBrowser1.Document
                        .GetElementById("pattern").SetAttribute("value", SCqryStr)
                        .GetElementById("CII_SuperBtn").InvokeMember("click")
                    End With
                    Timer1_Tick(sender, e)
                Case 2
                    With WebBrowser1.Document.GetElementById("action:a08").InvokeMember("click")
                        Timer1_Tick(sender, e)
                    End With
                Case 3
                    With WebBrowser1.Document
                        .GetElementById("smonth").SetAttribute("value", "1")
                        .GetElementById("emonth").SetAttribute("value", "1")
                        .GetElementById("syear").SetAttribute("value", "2005")
                        .GetElementById("eyear").SetAttribute("value", "2012")
                        .GetElementById("exporterId").SetAttribute("value", "SERIES_CSV_TIME_AS_ROW")
                        .GetElementById("action:a20").InvokeMember("click")
                    End With
                    Timer1_Tick(sender, e)
                Case 4
                    For Each link As HtmlElement In WebBrowser1.Document.Links
                        If link.InnerText.Contains("Download file from CANSIM") Then
                            Try
                                Dim fReader As New WebClient
                                csvAddress = link.GetAttribute("href")
                                Dim fName As String = "CPI" + Date.Today + ".csv"
                                If Not (System.IO.File.Exists("\\EWPG-SERVICE-60\FA-BUSANALYEC\EconomicDBTemp\DownloadedCSVs\" + fName)) Then
                                    fReader.DownloadFile(csvAddress, "\\EWPG-SERVICE-60\FA-BUSANALYEC\EconomicDBTemp\DownloadedCSVs\" + fName)
                                    MsgBox("Download Complete", "", MessageBoxButtons.OK)
                                    Me.Close()
                                Else
                                    MsgBox("File Already Exists", "", MessageBoxButtons.OK)
                                End If
                            Catch exH As HttpListenerException
                                Console.WriteLine("Error Accessing " + csvAddress + " - " + exH.Message)
                            Catch ex As Exception
                                Console.WriteLine("Error Accessing " + csvAddress + " - " + ex.Message)
                            End Try
                        End If
                        Timer1_Tick(sender, e)
                    Next
                    Timer1_Tick(sender, e)
            End Select
            Timer1_Tick(sender, e)
        End If
        Timer1_Tick(sender, e)
    End Sub

---

So what this does (as I'm sure you can see) is simulates the events the user will go through with regards to querying the website. The SCqryStr is passed into Case 1 and then the code will do the rest regarding the clicking events. However, if I split the string then I'll need to iterate the whole WebBrowser1_DocumentCompleted Sub until all the values are queried and downloaded. How would the code know the last value from the database used per iteration and how would I actually iterate that sub until all values have been passed?

Am I making sense?