CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 Maximum number of Connections Exceeded

Print topic Send  topic

Author Message
Dreamcatchers


Posts: 120
Posted: 01/28/2014, 10:19 PM

I have an application that launches a training course based on a link in the database. However throughout the day SQL Server 2012 will issue an error "Maximum number of connections exceeded".
I am looking at custom code I wrote to see if I am the cause of the problem. However I am not clear if what I wrote is right or wrong.
Steps in my routine are:
1. open a connection
2. set up a SQL variable
3. execute the SQL (update roster table)
4. Set another SQL variable
5. execute the SQL (update Student table)
6. Close the connection
7 Set the connection to nothing
8. Get the course link from the url parameter
9. Launch the course using javascript

Do I need to close the connection between each SQL execute?
Could error trapping I have potentially skip the close connection?

My code is listed below. Your advice would be greatly appreciated.


Dim rosterid, link_course, SQL, Connection, errormessage
rosterid=CCGetParam("rosterid",0)
session("rosterid") = rosterid
Set Connection = New clsDBSmart
Connection.Open
sql="UPDATE roster SET Status = 2 where rosterid="&rosterid
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
' Update Student record
sql="Update employees SET Last_RosterID =" & rosterid & "where emp_id=" & ccgetuserid()
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
link_course=Request.QueryString("link_course")
Response.Write("<" & "script>" & vbCrLf)
Response.Write("window.open('" & link_Course & "',status=0,toolbar=0,menubar=0,titlebar=0,Width=980,Height=658,resizable=1);")
Response.Write(vbCrLf & "<" & "/script>")
' -------------------------
_________________
Training and LMS Developer
View profile  Send private message
eratech


Posts: 513
Posted: 01/30/2014, 7:45 PM

I've been using .NET for some time, and no ASP for several years so from vague memory, is there a 'Connection Pooling' option on the SQL Connection setup? That usually allows connections to be reused on web apps.

And (again from memory) I think you have the right "open connection -use it - use it again - close" instead of closing each time.

It's probably more to do with the SQL Server than with your coding. I've had some serious connections for pages happening on projects and never ran into that error.

Cheers

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Dreamcatchers


Posts: 120
Posted: 01/31/2014, 7:29 AM

connection pooling is a feature of ADO.NET (the data access mechanism within .NET. It means that .NET

Back to my original question it seems you answered it. I can open a connect, do multiple operations, then close the connection.

That seemed to make since as doing an open and close for each operation (read, write, update, delete) was inefficient and may bog down the database in heavy use times.
_________________
Training and LMS Developer
View profile  Send private message
Dreamcatchers


Posts: 120
Posted: 01/31/2014, 7:29 AM

connection pooling is a feature of ADO.NET (the data access mechanism within .NET. It means that .NET

Back to my original question it seems you answered it. I can open a connect, do multiple operations, then close the connection.

That seemed to make since as doing an open and close for each operation (read, write, update, delete) was inefficient and may bog down the database in heavy use times.
_________________
Training and LMS Developer
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.