Timing out

[Jim via DotNetMonster.com]

Hi,

I've been getting the message:

Timeout period elapsed prior to obtaining a connection from the pool.

I'm using this function on all my pages. Can you see anything incorrect
with the code?

Thanks


Imports System
Imports Microsoft.Visua lBasic
Imports System.Web
Imports System.Configur ation
Imports System.Data.Sql Client
Imports System.Data

Namespace Data

Public Class dataClass

Public Shared Function GetPage(ByVal courseNumber As Integer,
ByVal lessonNumber As Integer, ByVal pageNumber As Integer ) As IDataReader
Dim strConnection As String
strConnection = ConfigurationSe ttings.AppSetti ngs
("ConnectionStr ing")

Dim dbConnection As New SqlConnection(s trConnection)

Dim queryString As String = "SELECT [tblPage].*, [tblLesson].
[LessonNumber],[tblLesson].[LessonTitle], [tblCourse].[CourseNumber],
[tblCourse].[CourseTitle] FROM [tblPage], [tblLesson], [tblCourse] WHERE ((
[tblPage].[Pa"& _
"geNumber] = @PageNumber) AND ([tblLesson].[LessonNumber] =
@LessonNumber) AND (["& _
"tblCourse].[CourseNumber] = @CourseNumber)) AND tblPage.lessonI D
= tblLesson.lesso nID AND tblLesson.Cours eID = tblCourse.Cours eID"

Dim dbCommand As New SqlCommand
dbCommand.Comma ndText = queryString
dbCommand.Conne ction = dbConnection

Dim dbParam_pageNum ber As New SqlParameter
dbParam_pageNum ber.ParameterNa me = "@PageNumbe r"
dbParam_pageNum ber.Value = pageNumber
dbParam_pageNum ber.DbType = DbType.Int32
dbCommand.Param eters.Add(dbPar am_pageNumber)
Dim dbParam_lessonN umber As New SqlParameter
dbParam_lessonN umber.Parameter Name = "@LessonNum ber"
dbParam_lessonN umber.Value = lessonNumber
dbParam_lessonN umber.DbType = DbType.Int32
dbCommand.Param eters.Add(dbPar am_lessonNumber )
Dim dbParam_courseN umber As New SqlParameter
dbParam_courseN umber.Parameter Name = "@CourseNum ber"
dbParam_courseN umber.Value = courseNumber
dbParam_courseN umber.DbType = DbType.Int32
dbCommand.Param eters.Add(dbPar am_courseNumber )

dbConnection.Op en
Dim dataReader As SQLDataReader = dbCommand.Execu teReader
(CommandBehavio r.CloseConnecti on)
Return dataReader

End Function

End Class
End Namespace

--
Message posted via http://www.dotnetmonster.com

[Brock Allen]

Re: Timing out

This sounds like the classic problem of leaking connections to the database.
For this one snippet make sure that whoever's calling GetPage calls Dispose()
or Close() on the returned DataReader.

-Brock
DevelopMentor

http://staff.develop.com/ballen

[color=blue]
> Hi,
>
> I've been getting the message:
>
> Timeout period elapsed prior to obtaining a connection from the pool.
>
> I'm using this function on all my pages. Can you see anything
> incorrect with the code?
>
> Thanks
>
> Imports System
> Imports Microsoft.Visua lBasic
> Imports System.Web
> Imports System.Configur ation
> Imports System.Data.Sql Client
> Imports System.Data
> Namespace Data
>
> Public Class dataClass
>
> Public Shared Function GetPage(ByVal courseNumber As
> Integer,
> ByVal lessonNumber As Integer, ByVal pageNumber As Integer ) As
> IDataReader
> Dim strConnection As String
> strConnection = ConfigurationSe ttings.AppSetti ngs
> ("ConnectionStr ing")
> Dim dbConnection As New SqlConnection(s trConnection)
>
> Dim queryString As String = "SELECT [tblPage].*,
> [tblLesson].
> [LessonNumber],[tblLesson].[LessonTitle], [tblCourse].[CourseNumber],
> [tblCourse].[CourseTitle] FROM [tblPage], [tblLesson], [tblCourse]
> WHERE ((
> [tblPage].[Pa"& _
> "geNumber] = @PageNumber) AND ([tblLesson].[LessonNumber] =
> @LessonNumber) AND (["& _
> "tblCourse].[CourseNumber] = @CourseNumber)) AND
> tblPage.lessonI D
> = tblLesson.lesso nID AND tblLesson.Cours eID = tblCourse.Cours eID"
> Dim dbCommand As New SqlCommand
> dbCommand.Comma ndText = queryString
> dbCommand.Conne ction = dbConnection
> Dim dbParam_pageNum ber As New SqlParameter
> dbParam_pageNum ber.ParameterNa me = "@PageNumbe r"
> dbParam_pageNum ber.Value = pageNumber
> dbParam_pageNum ber.DbType = DbType.Int32
> dbCommand.Param eters.Add(dbPar am_pageNumber)
> Dim dbParam_lessonN umber As New SqlParameter
> dbParam_lessonN umber.Parameter Name = "@LessonNum ber"
> dbParam_lessonN umber.Value = lessonNumber
> dbParam_lessonN umber.DbType = DbType.Int32
> dbCommand.Param eters.Add(dbPar am_lessonNumber )
> Dim dbParam_courseN umber As New SqlParameter
> dbParam_courseN umber.Parameter Name = "@CourseNum ber"
> dbParam_courseN umber.Value = courseNumber
> dbParam_courseN umber.DbType = DbType.Int32
> dbCommand.Param eters.Add(dbPar am_courseNumber )
> dbConnection.Op en
> Dim dataReader As SQLDataReader = dbCommand.Execu teReader
> (CommandBehavio r.CloseConnecti on)
> Return dataReader
> End Function
>
> End Class
> End Namespace[/color]

[Jim via DotNetMonster.com]

Re: Timing out

Thanks. When I'm calling GetPage, I'm binding it to a dataset. So I have:

<ASP:Repeater id="RepeaterPag eText" runat="server" DataSource="<%#
Data.dataClass. GetPage(IntCour seNumber,IntLes sonNumber,IntPa geNumber) %>">
<ItemTemplate >
<%# DataBinder.Eval (Container.Data Item, "pageText") %>
</ItemTemplate>
</ASP:Repeater>

Where would I close the DataReader? Would it be in the Page Load:
Sub Page_Load(sende r As Object, e As EventArgs)
Page.DataBind()
End Sub

--
Message posted via http://www.dotnetmonster.com

[Brock Allen]

Re: Timing out

Call Dispose() after you call DataBind()

-Brock
DevelopMentor

http://staff.develop.com/ballen

[color=blue]
> Thanks. When I'm calling GetPage, I'm binding it to a dataset. So I
> have:
>
> <ASP:Repeater id="RepeaterPag eText" runat="server" DataSource="<%#
> Data.dataClass. GetPage(IntCour seNumber,IntLes sonNumber,IntPa geNumber)
> %>">
> <ItemTemplate >
> <%# DataBinder.Eval (Container.Data Item, "pageText") %>
> </ItemTemplate>
> </ASP:Repeater>
> Where would I close the DataReader? Would it be in the Page Load:
> Sub Page_Load(sende r As Object, e As EventArgs)
> Page.DataBind()
> End Sub[/color]

[Jim via DotNetMonster.com]

Re: Timing out

Thanks so much. I'll try that.

--
Message posted via http://www.dotnetmonster.com

[Jim via DotNetMonster.com]

Re: Timing out

Sorry to hassle you again but just 1 more question.

Would this be the correct way of doing it:

Sub Page_Load(sende r As Object, e As EventArgs)
Page.DataBind()
Dispose()
End Sub

I tried to use dbConnection.Cl ose but it doesn't recognize the connection.
Also, is it fine to close the connection in the class as well?

Thanks so much

--
Message posted via http://www.dotnetmonster.com

[Brock Allen]

Re: Timing out

I'd change it to:

Sub Page_Load()
Dim rdr as IDataReader = Nothing
Try
rdr = GetPage(IntCour seNumber,IntLes sonNumber,IntPa geNumber)
RepeaterPageTex t.DataSource = rdr
RepeaterPageTex t.DataBind()
Finally
If Not rdr is Nothing then rdr.Close()
End Try
End Sub


-Brock
DevelopMentor

http://staff.develop.com/ballen

[color=blue]
> Sorry to hassle you again but just 1 more question.
>
> Would this be the correct way of doing it:
>
> Sub Page_Load(sende r As Object, e As EventArgs)
> Page.DataBind()
> Dispose()
> End Sub
> I tried to use dbConnection.Cl ose but it doesn't recognize the
> connection. Also, is it fine to close the connection in the class as
> well?
>
> Thanks so much
>[/color]

[Jim via DotNetMonster.com]

Re: Timing out

I have several repeaters on the page that I'm binding the data to.

Can I just bind to the page then close the reader? Sorry, I am new at this.

So something like:

Sub Page_Load()
Dim rdr as IDataReader = Nothing
Try
rdr = GetPage(IntCour seNumber,IntLes sonNumber,IntPa geNumber)
Page.DataBind()
Finally
If Not rdr is Nothing then rdr.Close()
End Try
End Sub

--
Message posted via http://www.dotnetmonster.com

[Brock Allen]

Re: Timing out

The DataReader is a forward-only read-only data access mechanism. So if you
have more than one Repeater than needs to bind to the same result set and
you're using the DataReader, then you're going back to the database for each
one -- this is not good for performance. I'd suggest looking into the DataSet
as an alternate mechanism.

Now, if each of your Repeaters binds to a different set of data from the
database (so different SQL statements), then it's ok to use the DataReader
(you're still going to the database for each one), but you also still need
to call Close() on each one.

Programming's hard. What can I say :)

-Brock
DevelopMentor

http://staff.develop.com/ballen

[color=blue]
> I have several repeaters on the page that I'm binding the data to.
>
> Can I just bind to the page then close the reader? Sorry, I am new at
> this.
>
> So something like:
>
> Sub Page_Load()
> Dim rdr as IDataReader = Nothing
> Try
> rdr = GetPage(IntCour seNumber,IntLes sonNumber,IntPa geNumber)
> Page.DataBind()
> Finally
> If Not rdr is Nothing then rdr.Close()
> End Try
> End Sub[/color]

[Jim via DotNetMonster.com]

Re: Timing out

Thanks. That makes sense I am using the same resultset and I'll look into
the dataset. If I use a dataset then I won't need to close any connections
from the page calling the function so that should resolve the connection
leak I'm having right?

Thanks again for all your help.

--
Message posted via http://www.dotnetmonster.com