.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Open vs Close Connection

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :ASP.Net

Hi Guys,

I have a question related to  leave a SQL connection open or close it.

Here is the scenario...

I have an Intranet page that monitors a Robot, that webpage needs to be up 24hours/7days a week.

Robot sends information to a SQL database every couple of seconds or minutes depending on the work it performs.

My page has an AJAX Timer that triggers every 5 sec, in that page I have to query the database (simple SELECT - no more then 15 lines) and show my info in a gridview.

The question is: Should I open the connection and leave it open or should i open and close every 5 secs?


View Complete Post

More Related Resource Links

SQL server connection - keep it opened all the time, or open/close on demand ?



I have a VB6 app that gets data from several devices and has to store it in a SQL server 2005 database.
It may have to store small amounts of data (a few KB at most) somewhere between 5 and 500 times per day.
My app only sends data to DB, doesn't read anything.

My question is what's better in this case:

a) the app opens a connection to DB at startup and closes it at exit, or
b) when data arrives, the app opens a connection, sends data, then closes the connection


Open SQL Server Connection Troubleshooting

Hello, This is regarding ASP.Net (VB) 2.0 and SQL Server 2008.  My project's (there is only one in my solution) has a "Start Action" set to a "Specific Page" (..\Forms\frmMain.aspx).  Occasionally (though not always) a large number of connections (30+) will be opened prior to the frmMain.PreInit event.  These connections stay open for quite some time.  I am closing all of my connections with extreme prejudice.  In my "finally" clause of my try...catch I have myconnection.close.  Immediately after the "End Try" I have myconnection.dispose (redundant, I know).  I have accounted for all connections.  I placed a breakpoint on frmMain.PreInit and then (sometimes) a large number of connections (used SSMS to run sp_who and sp_who2) will have just opened.  What would open a large number of connections prior to the starting page's preinit event?  I am closing (and then disposing) all of my connections.  I thought that I must be missing a myconnection.close in the "finally" clause, but I have looked repeatedly and cannot find anywhere that a connection is open and then not closed.  Also, I do not believe that I have even opened a connection prior to the frmMain's preinit event.        

how many open connection is recommended to leave - in sql server 2008 Enterprise Edition

hi i have my C# program that work with sql-server 2008 Enterprise Edition i have 40 users that connect to this database. i dont close the connection. how many open connection is recommended to leave ? or is it better to close connection ? thank's in advance

how to open ssh / secure shell connection using vb and run commands

Hi I need to open connection for Secure shell and run commands from a remote site i am using Vb.net   i have seen many component that can use but i would like to code by my own if i could please give me your brilliant ideas

claims authenticated (FBA) user cannot open a connection to our custom application database

We have designed a custom sharepoint site. This site uses a membership provider that I implemented, and the users are stored in our custom application database. These are the steps I follow to reproduce the error: 1. Browse to our site. Select Forms Authentication from the default.aspx selection. 2. Enter in a username from our DB table. This user I have configured as a site collection administrator. 3. Login to the site. The user is authenticated by our membership provider and allowed access (ValidateUser returns true) 4. Browse to a page that attempts to connect to the custom app DB. 5. I get the following error when the page attempts to open a connection to our DB: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server NOTES: The code that opens the DB connection is running with SPSecurity.RunWithElevatedPrivileges. Our application pool account has access to the database. My user can connect to the database fine as well as the user that our  application pool is running as. I have validated the connection string that is being used in the debugger, and that is correct.

When establishing a connection with a SQL databse using a try catch Does the try catch close the dat

HI everyone,  I have read a lot about SQL and I am told always to close the connection to the database after you finish. But what happens when you use a try catch and an error oucurs that bypasses the close statement? Since the SQL is enclosed in the try catch you cannot close the connection. Does the try catch close the connection automaticaly?  If it needs to be closed how would you close it? CurtisAlways Lost in Code, Always mark answers as correct if they answer you question and solve your problem. This way others when searching for similar problems can find the answer faster.

GridView crashes when user enters html open "<" or close ">" characters in a textbox

My GridView is bound to a sql data source and allows editing of fields. Coding is in C# and JavaScript. Using Visual Studio 2008 in test mode, if I enter a less-than "<" or greater-than ">" character in a GridView textbox an error is immediately thrown upon pressing enter: "Microsoft Visual StudioMirosoft JScript runtime error:Sys.WebForms.PageRequestManagerServerErrorException: An unknown erroroccurred while processing the request on the server. The status dode returnedfrom the server was: 500"When the < or > is entered in production, the page aborts and loads the application start page. I need to be able to catch and replace those characters BEFORE the error is thrown. I've tried hooking OnTextChange code to the textboxes, but the error is thrown as soon as I press enter...the OnTextChange code never runs. Any help will be greatly appreciated.

SQL 2005 open connection timeout error


We have Microsoft SQL Server 2005 - 9.00.4207.00 (X64)  Enterprise Edition (64-bit) on Windows server 2003 (Build 3790: Service Pack 2) .

Periodically (~ once per 15 min) some db-clients have error messages when open connection (over SQL Native Client ) like:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

All timeouts more or equal 15 sec. Less then 15 sec is not acceptable by business rules. Performance counters (cpu,memory,disk etc.) on server does not show bottleneck. Only one sql counter - logins/sec show trouble. When problem exists then this counter = 0. Also dramatically decrease many other counters - sql batch requests/sec, transactions/sec, cpu load and some ones. SQL Server "sleep" few seconds (4 - 5) and then accessible again.




Named Pipes Provider, error: 40 - Could not open a connection to SQL Server


This was originally posted in the SSIS Forum, but a member of the IS team suggested it be moved here.  "Most recently I got this error (Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) so does this mean that SQL Server is still trying to use named pipes even if I only have TCP/IP enabled in SQL Server Configuration Manager?"

I'm having the same issue, and here's our scenario:

  1. Installed SQL Server 2005 Developer Edition on a machine with WinXP SP2 and enabled remote connections over TCP/IP
  2. Installed SQL Server 2005 Standard Edition on a machine with Win2003 SP1 (remote connections over TCP/IP enabled by default)
  3. Attempted to 'Copy Database' from Developer Edition TO Standard Edition using 'Detach and Attach' method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
  4. A

ExecuteReader requires an open and available Connection. The connection's current state is closed.


I am accessing SQL2005 with C# code using  OleDbConnection.


A try and catch block catches the following error once a while between the Open() and Close() of the connection:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.


I do not even have any idea where to start to debug this.  The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens. 


Any hint would be greatly appreciated.



provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

after lots  of goes and back, I have the same problem but it is related to my Visual studio too. Because I can access to the same database  for new projects but when I use an existing project with a new default page I get the same error.
I am using visual studio 2005 connecting to MSSQL 2008 in server that has an instance of SQLServer express 2005 too
the code for boht new and existing project is bellow

I appricate any help


protected void Page_Load(object sender, EventArgs e)



SqlConnection cn =

ORA-06413: Connection not open.


Hi everyone,

I have Oracle client 10g 32 bit installed on my Windows 7 64 bit machine. I am using System.Data.Oracleclient to talk to Oracle databases running on 8i and 10g server. I have my website setup on localhost as well. When I run my website in debug in visual studio 2010, it fails to open a connection and returns an error code ORA-06413: Connection not open. When I run it under localhost, it opens a connection fine and executes a query alright. But becaue I need to debug the code as it do not generate expected results I need to debug it, but am stuck due to this Visual Studio not talking to Oracle. Any ideas on how to get around this problem.


close connection



I am using a adapted version of


Don't we need something like a closing-routine / close connection command after getting data from the database?

If yes: What is the correct closing command?


implications of not closing an open connection declared locally


 Hello All.


I was wondering about something.  I'm supporting a .NET C# application which has a number of database connections declared locally as IDbConnection with MS SQL Server and NHibernate.  These connections are declared locally and not explicitly closed after a specific operation is performed.  Are these connections automatically closed after the local method is executed, or could these connections be floating around somewhere?  We're having semaphore max out problems, and I'm trying to see if this may be the culprit.  I'm doubtful, as I believe these connections would automatically close, but thought I'd throw out hte possibility.






create and open global database connection object


Hi to everyone:

I`m trying to install the allinta cms software www.allinta.com, I have already create a MySQL Data Base, but when I try to launch my cmsadmin I t appears an error

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'dbConnectionStr'

/cmsadmin/inc/db_connection.asp, line 75

IT  seems to be the Object Connection, could somebody help me to give the right code instruction please, I don´t have an idea on how to make it.

At this moment I opened in dreamweaver and it´s

Set objConn = Server.CreateObject("ADODB.Connection")

Is there any script for Open/Close sql-server 2008 ?


Is there any script (any batch file) for Open/Close sql-server 2008 ?

thank's in advance

Trying to enumerate the contents of folder ''. Can't open data connection (425).


Hello Guys,

I know there were posts before about this issue but contacting the web hoster did not help me.
The oddity is, that the same website files, copied and pasted, connection string altered to point to the
production/live db, published to the httpdocs folder works fine BUT the same web files, copied and
pasted, connection string altered to poin to the dev db, published to the subdomains/dev fodler causes the error below:

 ------ Build started: Project: AHN, Configuration: Debug Any CPU ------
   AHN -> P:\Web_Development\xxx\DEV\bin\xxx.dll
------ Publish started: Project: xxx, Configuration: Debug Any CPU ------
Connecting to ftp://xxx.co.uk/subdomains/dev/httpdocs...
Transformed Web.config using Web.Debug.config into
Copying all files to temporary location below for package/publish:
Deleting existing files...
An error occured trying to enumerate the contents of folder ''. Can't
open data connection (425).
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Publish: 0 succeeded, 1 failed, 0 skipped ==========

When I asked the hoster whats going on, he obsiously that its not his fold because the publishing is

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend