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