I have been struggling with a timeout problem for a week. Here is my first post on this issue where I got some pointers that moved me along the debugging path - http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/111/threadid/228684/scope/posts/Default.aspx. This is a pretty complex problem. I have a Microsoft GridView component that connects to a separate database (not the standard DNN DB). The major connection components to the SQL Server database are the main GridView, a nested GridView in each row, ObjectDataSource components, and the DNN data provider infrastructure. For the past week I have been running the site on my local hardware using VS 2005 debugger. One of my problems is that I have only gotten this to fail on my local site a couple of times. However there is one nuance that I think is the central part of my problem and seems to indicate that I don’t have a bug but that I am just overtaxing the database connections on my hosting site. In my nested GridView I have two DropDownLists. Although I ultimately get the data via an efficient SQL stored procedure they get loaded quite frequently. I have worked with relational databases for many years but I am not a professional DBA. However, it looks like this issue has to be solved at the application level. It seems I have two choices here – just eliminate the drop downs or save the data and drastically reduce the database proc executions. There is a fair amount of data in the drop downs but they do provide a convenience for users. Would expanding the connection pool make any sense if the hosting site can do that? I haven't done much with drop downs in web applications. Any suggestions?

Thanks for any help or suggestions,

G.M. 

AssemblyVersion: 04.08.02
PortalID: 0
PortalName: IATSE Local 476
UserID: 5
UserName: local476
ActiveTabID: 68
ActiveTabName: Members
RawURL: /Default.aspx?tabid=68&error=Exception+has+been+thrown+by+the+target+of+an+invocation.
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer: http://www.iatse476.org/Default.aspx?tabid=68&ctl=Edit&mid=454
UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 39597839-ce7f-42c9-91bc-4e4ca1cc868b
InnerException: Exception has been thrown by the target of an invocation.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Data.ProviderBase.DbConnectionFactory.GetConnection
StackTrace:
Message: DotNetNuke.Services.Exceptions.PageLoadException: Exception has been thrown by the target of an invocation. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: 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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at SSCI.Modules.SSCITableMod.SqlDataProvider.GetPgMembers(String sortExpression, Int32 startRowIndex, Int32 maximumRows, String searchArgLN, String searchArgJC, String typeArg, String Msg) at SSCI.Modules.SSCITableMod.SSCITableModController.GetPgMembers(String sortExpression, Int32 startRowIndex, Int32 maximumRows, String searchArgLN, String searchArgJC, String typeArg) --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) at System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() at System.Web.UI.Control.EnsureChildControls() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---
Source:
Server Name: GENERIC28

Thanks for the quick reply Brandon. I checked the procs that fill the DropDownLists using the VS 2005 debugger and SQL Profiler. There is no doubt they are getting executed a lot. Caching makes sense at least as a first try. One minor problem is that I got the data from an old file where the data lists were not standardized, i.e. the users just typed in various job and certification descriptions using different abbreviations, etc. You would be surprised how many versions of the same thing are in the lists with different spellings. The bottom line is that for caching to work reasonably well the lists should be cut down. I hope I can get some help from my customer but I have a feeling I am about to do some grunt work. At any rate it's a good suggestion.

I haven’t worked with the Asp.Net DropDownList before or tried caching the data for it. I did find a couple of articles on it and I’ll check them out. If you have any favorite articles or tutorials, let me know and I’ll read them.

Thanks,

G.M.