Click here to Skip to main content
15,444,490 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
im developing online Web base Application in VB.Net and getting error

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


Search allot but didn't find any solution.
after using my application for 3 to 5 minuets it will give an Error..
Im Also Uploading The code Please Let me know if any thing is wrong in code or Connection not closing properly .

What I have tried:

This code i use For Insert Statement before inserting im checking either value is allready available in Sql Table or not..

Private Sub attachement()
        If getcurrentinfo.Text = "" Then
            ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), UniqueID,
                                 "javascript:alert('Please Select Broker Name');", True)
        ElseIf TextBox7.Text = "" Then
            ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), UniqueID,
                                 "javascript:alert('Please Enter Vehicle Type');", True)

        Else

            'Dim con As SqlConnection
            cn = New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ConnectionString)
            Try

                cn.Open()

                Dim theQuery As String = "SELECT * FROM VehicleAttachement WHERE Shop_id=@Shop_id AND BrokerName=@BrokerName AND BrokerCompany=@BrokerCompany AND VehicleType=@VehicleType"
                Dim cmd1 As SqlCommand = New SqlCommand(theQuery, cn)

                cmd1.Parameters.AddWithValue("@BrokerName", getcurrentinfo.Text)
                cmd1.Parameters.AddWithValue("@BrokerCompany", brokercom.Text)
                cmd1.Parameters.AddWithValue("@VehicleType", TextBox7.Text)
                cmd1.Parameters.AddWithValue("@Shop_id", schoolID.Text)
                Using reader As SqlDataReader = cmd1.ExecuteReader()
                    If reader.HasRows Then
                        ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), UniqueID,
                                               "javascript:alert('Vehicle Type  " & Me.TextBox7.Text & " is allready Attached With " & Me.getcurrentinfo.Text & "');", True)

                        Return

                    Else
                        cn.Close()
                        ' User does not exist, add them
                        If getcurrentinfo.Text = "" Then

                        Else
                            cn.Open()
                            Dim sql As String = "INSERT INTO VehicleAttachement VALUES(@BrokerName,@BrokerCompany,@VehicleType,@Typ,@Purchs,@Sale,@Status,@Shop_id)"
                            Dim cmd As New SqlCommand(sql, cn)
                            cmd.Parameters.AddWithValue("@BrokerName", getcurrentinfo.Text)
                            cmd.Parameters.AddWithValue("@BrokerCompany", brokercom.Text)
                            cmd.Parameters.AddWithValue("@VehicleType", TextBox7.Text)
                            cmd.Parameters.AddWithValue("@Typ", DropDownList1.Text)
                            cmd.Parameters.AddWithValue("@Purchs", TextBox8.Text)
                            cmd.Parameters.AddWithValue("@Sale", TextBox9.Text)
                            cmd.Parameters.AddWithValue("@Status", "Active")
                            cmd.Parameters.AddWithValue("@Shop_id", schoolID.Text)
                            cmd.ExecuteNonQuery()
                            cn.Close()

                            ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), UniqueID,
                                       "javascript:alert('Vehicle Type " & Me.TextBox7.Text & " Attached With " & Me.getcurrentinfo.Text & "');", True)


                        End If

                        Return
                    End If
                End Using
                cn.Close()
            Catch ex As Exception

            End Try
            cn.Close()
            'con.Close()
        End If


    End Sub




This Code Im using for Updating

Private Sub updaterates()

        Dim con As SqlConnection
        con = New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ConnectionString)
        Try
            con.Open()

            cmd = New SqlCommand("UPDATE VehicleAttachement  SET Typ=@Typ,Purchs=@Purchs,Sale=@Sale  WHERE ID=@ID AND Shop_id=@Shop_id ", con)
            cmd.Parameters.Add("@Typ", SqlDbType.VarChar).Value = DropDownList1.Text
            cmd.Parameters.Add("@Purchs", SqlDbType.VarChar).Value = TextBox8.Text
            cmd.Parameters.Add("@Sale", SqlDbType.VarChar).Value = TextBox9.Text
            cmd.Parameters.Add("@Shop_id", SqlDbType.VarChar).Value = schoolID.Text
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = getclasscombo.Text
            dr = cmd.ExecuteReader()
            con.Close()
            ScriptManager.RegisterClientScriptBlock(Me, GetType(Page), UniqueID,
                                   "javascript:alert('Rates Updated For " & Me.TextBox7.Text & "');", True)

        Catch ex As Exception

        End Try
        con.Close()
      
    End Sub



This COde is using for load data in Gridview

Private Sub brokercompany()
       Dim dt As New DataTable()
       Dim strConnString As String = (ConfigurationManager.ConnectionStrings("constring").ConnectionString)
       Dim con As New SqlConnection(strConnString)
       Dim sda As New SqlDataAdapter()
       Dim cmd As New SqlCommand("Select id,Broker,IncuraCompany as [Broker Company] From Brokerattachment Where Broker=@Broker AND Shop_id=@Shop_id")
       cmd.Parameters.AddWithValue("@Broker", getcurrentinfo.Text)
       cmd.Parameters.AddWithValue("@Shop_id", schoolID.Text)
       cmd.CommandType = CommandType.Text
       cmd.Connection = con
       sda.SelectCommand = cmd
       sda.Fill(dt)
       GridView3.DataSource = dt
       GridView3.DataBind()
   End Sub


Simple Insert Statement Without checking value available or not

Private Sub Savealance()

       'Dim con As SqlConnection
       cn = New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ConnectionString)
       Try
           cn.Open()
           Dim sql As String = "INSERT INTO Brokerbalance VALUES(@BrokName,@Ballance,@Shop_id)"
           Dim cmd As New SqlCommand(sql, cn)

           cmd.Parameters.AddWithValue("@BrokName", TextBox4.Text)
           cmd.Parameters.AddWithValue("@Ballance", TextBox6.Text)
           cmd.Parameters.AddWithValue("@Shop_id", schoolID.Text)
           cmd.ExecuteNonQuery()
           cn.Close()


       Catch ex As Exception

       End Try


   End Sub
Posted
Updated 30-Dec-20 8:52am

 
Share this answer
 
Call .Dispose() on your connections when you're done with them.
 
Share this answer
 
Comments
Naqash Younis 30-Dec-20 21:39pm     CRLF
Where i have to call cn.Dispose() and please also check either my all codes are in proper format
Dave Kreskowiak 30-Dec-20 22:58pm     CRLF
Seriously? Instead of con.Close(), you replace that with con.Dispose(), OR you can use "using" blocks and that will take care of it for you:
Using cn = New SqlConnection(ConfigurationManager.ConnectionStrings("constring").ConnectionString)    Try        cn.Open()        Dim sql As String = "INSERT INTO Brokerbalance VALUES(@BrokName,@Ballance,@Shop_id)"        Dim cmd As New SqlCommand(sql, cn)                cmd.Parameters.AddWithValue("@BrokName", TextBox4.Text)        cmd.Parameters.AddWithValue("@Ballance", TextBox6.Text)        cmd.Parameters.AddWithValue("@Shop_id", schoolID.Text)        cmd.ExecuteNonQuery()    Catch ex As Exception        ' You better do something with the exception, like log it somewhere,        ' otherwise any error will just get swallowed and you won't know any        ' thing went wrong and scratch your head wondering why it doesn't work.        End TryEnd Using

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900