none
problem with storedprocedure or dataset RRS feed

  • Question

  •  

    "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."

     

    I get this message whenever my program reaches the part where it is supposed to run the storedprocedure and store the records gathered, to the dataset.

     

    here is the part of the code where the error comes outSadthe red one)

     

    If RemoteDataSet.IsCT_Show.Count > 0 Then RemoteDataSet.IsCT_Show.Clear()

    Dim BusinessObject As New BusinessLayer.clsFileMaintenance

    BusinessObject.Sub_Show(ServerPath2, "IsCT_Show", CommandType.StoredProcedure, RemoteDataSet, "IsCT_Show")

    DataGrid1.DataSource = RemoteDataSet.IsCT_Show

    Friday, April 25, 2008 2:36 AM

All replies

  • Hi,

     

    Nothing specific that anyone can tell here to overcome this .

    You would have to do these checks yourself to ascertain what is the cause of the time out error.

     

    a) Modify the stored procedure to get a smaller data set

          or

    b) Check the network connections (ping) between the client and the server machine

        or

    c) try and run this code asynchronusly

       or

    d) try and find out the time taken by the stored procedure itself to come up wirth a result on the SQL server results window

     

    All these would be indicators as to why the code piece is timing out

     

     

    Regards

    Nikhil

     

    Saturday, May 17, 2008 10:36 AM
  • thanks Nikhil for giving your time to helping me with my problem.

    but unfortunately i wasnt able to find out what the real problem is, using the methods you gave me.

     

    that problem was relieved from me.

     

    but with what i'm working on right now, it happened again.

     

    here's the code i'm working on:

    Private Sub btnFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFile.Click

    Dim LineStr As String, mDate As String, BrStr As String, ItmStr As String, QtyStr As String

    Dim myComm As New SqlCommand

    Dim myConn As New SqlConnection

    If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

    FileOpen(1, OpenFileDialog1.FileName, OpenMode.Input)

    lines = getNumLines(1)

    FileClose(1)

    FileOpen(1, OpenFileDialog1.FileName, OpenMode.Input)

    mDate = ""

    For i = 0 To (lines - 1)

    LineStr = "" : BrStr = "" : ItmStr = "" : QtyStr = ""

    Input(1, LineStr)

    If Mid(LineStr, 1, 1) = "1" Then

    mDate = ((RightStr(LineStr, 2)) & "/01/" & Mid((RightStr(LineStr, 6)), 1, 4))

    With myComm

    myConn.ConnectionString = ServerPath

    myConn.Open()

    .Connection = myConn

    z = "delete from newscores..mercurysaledraft "

    z = z & "where refdate = '" & Trim(mDate) & "'"

    .CommandText = z

    .CommandType = CommandType.Text

    .CommandTimeout = 12000000

    .ExecuteNonQuery()

    myConn.Close()

    End With

    GoTo nxt

    End If

    If Mid(LineStr, 1, 1) = "2" Then

    BrStr = Mid(LineStr, 2, 3)

    ItmStr = Mid(LineStr, 5, 6)

    QtyStr = Mid(LineStr, 11, 5)

    End If

    If Trim(mDate) <> "" And Trim(BrStr) <> "" And Trim(ItmStr) <> "" And Trim(QtyStr) <> "" Then

    With myComm

    myConn.ConnectionString = ServerPath

    myConn.Open()

    .Connection = myConn

    z = "insert into newscores..mercurysaledraft "

    z = z & "(branchcode, mitemcode, qty, refdate) values "

    z = z & "('" & Trim(BrStr) & "', '" & Trim(ItmStr) & "', " & Trim(QtyStr) & ", "

    z = z & "'" & Trim(mDate) & "')"

    .CommandText = z

    .CommandType = CommandType.Text

    .CommandTimeout = 1200

    .ExecuteNonQuery()

    myConn.Close()

    End With

    End If

    nxt: Next i

    FileClose(1)

    End If

    End Sub

    Public Function getNumLines(ByVal fileName As Integer) As Integer

    Dim numLines As Integer

    Dim g As String

    numLines = 0

    While Not (EOF(1))

    Input(1, g)

    numLines = numLines + 1 'counts number of rows in the file

    End While

    Return numLines 'return the number of lines found

    End Function

     

    i tried closing the connection after every database process, but still, i get the connection timeout error.

    i hope this code could give you an idea where/what i'm doing wrong.

    Thursday, July 3, 2008 2:48 AM