sql and pooled connection error

Question

Dear all,

Kindly look at the following code as this sample code gives an error when i hosted it on Hostexcellence , but locally it runs perfect, and the error is as the following:

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

SqlDataSource1.SelectCommand = "Select Top (3) * from News Order by NewsID Desc";
SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader;
SqlDataReader r_News = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
DataGrid_News.DataSource = r_News;
r_News.Close();
DataGrid_News.DataBind();

So What's wrong with that code ??

Answer 1

See this: http://msdn.microsoft.com/en-us/library/s4yys16a(VS.71).aspx

Public Sub ConnectToSql()
    Dim conn As New SqlClient.SqlConnection
    ' TODO: Modify the connection string and include any
    ' additional required properties for your database.
    conn.ConnectionString = & _
    "integrated security=SSPI;data source=SQL Server Name;" & _
    "persist security info=False;initial catalog=northwind"
    Try
        conn.Open()
        ' Insert code to process data.
    Catch ex As Exception
        MessageBox.Show("Failed to connect to data source")
    Finally
        conn.Close()
    End Try

End Sub

You should always include a finally clause to ensure that your connection is closed otherwise the connection will not be released (in case an exception occurs) and you will not have any more connections available.