Read another WEB page using StreamReader hangs after reading 300 lines

[DougP]

There is a report on our intranet that has two columns of numbers we use for cost tracking. There are 8000+ line items, I need to get two columns, the first and last.
The code works Ok for a while but then locks up. I suspect a buffer is full or something.
What it does is load the whole report into a string then goes line by line and extracts the two numbers then runs an SP to insert them in a table, this is done is a while loop. but after a while, pun inteneded, it just hangs. I then look in the SQL table and it get to about 300-400 records. is there a better way to rrad that much data in insert it in a SQL table?
Or Any other Ideas. we can't get the data any other way but this report. It was copied and pasted into Excel b4 but I want to automate it as part of the Time keeping App I'm creating. We need to do this weekly.

        Dim strResult As String
        Dim objResponse As WebResponse
        Dim objRequest As WebRequest = HttpWebRequest.Create(strURL)
        Dim SPMID As String = ""
        Dim CostTracker = ""
        Dim StartingData As Boolean = False
        objResponse = objRequest.GetResponse()
        Using sr As New StreamReader(objResponse.GetResponseStream())
            strResult = sr.ReadToEnd()
            sr.Close()
        End Using

        Dim aLine As String = ""
        Dim aParagraph As String = ""
        Dim strReader As New StringReader(strResult)
        While True
            aLine = strReader.ReadLine()
            If aLine Is Nothing Then
                aParagraph = aParagraph & vbCrLf
                Exit While
            ElseIf InStr(1, aLine, "----") Then  'look for line accross page to start with actual data
                StartingData = True
            ElseIf StartingData Then
                'Debug.Print(aLine)
                CostTracker = Trim(Mid(aLine, 1, 13))
                SPMID = Trim(Mid(aLine, 160, 18))
                Debug.Print(SPMID, CostTracker)
                Dim Retval As String = sp_SOWInsertSPMID(SPMID, CostTracker)
            Else

            End If
        End While
[/code

DougP

 

[DougP]

I was looking in the error log I created and this was written while it ran the Stored Procdure.

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

it happend while it was in the Stored Procedure >> sp_SOWInsertSPMID, near the bottom of the code

here is that

   Public Function sp_SOWInsertSPMID(ByVal SPMID, ByVal Costtracker)
        Dim cnn As SqlConnection
        Dim cmd As New SqlCommand
        'use ConnectionString from WEB config file
        Dim connStr As String = ConfigurationManager.ConnectionStrings("SOWTimeReportingConnectionString").ConnectionString
        cnn = New SqlConnection(connStr)
        Try
            cnn.Open()
            With cmd
                .Connection = cnn

                .CommandText = "sp_SOWInsertSPMIDTemp"
                .CommandType = CommandType.StoredProcedure

                .Parameters.AddWithValue("SPMID", SPMID)
                .Parameters(0).SqlDbType = SqlDbType.NVarChar

                .Parameters.AddWithValue("COSTTRACKER", Costtracker)
                .Parameters(1).SqlDbType = SqlDbType.NVarChar

                ' execute Sp
                .ExecuteNonQuery()
            End With
            cnn.Close()
            cnn = Nothing
        Catch ex As Exception
            ' this saves the error to the sp_SOW_InsertEventlog and pops up an error window
            Dim ErrorTitle As String = "Error in ddlSPMID_SelectedIndexChanged"
            Dim PageName As String = System.IO.Path.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath)
            Dim Retval As String = ErrorTrap(PageName & " - " & System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message, ErrorTitle)
        End Try
       sp_SOWInsertSPMID = ""
   End Function

the following is the Stored Procedure.

ALTER procedure [dbo].[sp_SOWInsertSPMIDTemp]
	@SPMID nvarchar(20),
	@costtracker nvarchar(20)
AS
	insert into dbo.SPMIDTemp
	(spmid,costtracker)
	Values
	(@SPMID,@costtracker)

DougP