How to close database connection

HI guys, I have the following code that instantiates my data layer and connect to the database. I then retun the DataSet back to the codebehind. When/Where do I close the database connection? If I close the connection on the datalayer my codebehind gives me 'connection is closed' errors.

So.. where do I close the connection? THanks!

--- users.aspx.vb ---

Dim allUsers As New XYZ.admin
Dim userDataTable As New DataSet
userDataTable = allUsers.GetUsers()

do what i need to with userDataTable
blah
blah

--- admin.vb ---

Public Function GetUsers() As DataSet
'
'Create a connection to the database
'
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("MyCon"))
Dim myCommand As New SqlDataAdapter("SPROC", myConnection)
'
'Mark command as a SPROC and add Parameters
'
blah
blah

return myDataSet


Graphfixz Data & Web Design
http://www.graphfixz.com
0
Graphfixz
1/5/2006 5:29:25 PM
asp.net.getting-started 91979 articles. 3 followers. Follow

5 Replies
455 Views

Similar Articles

[PageSpeed] 17

First of all:  You should be using try / catch/ finally
blocks.  In your try block, open your connection and then get your
data by filling a dataset.  In your catch block, you just catch if
there are any errors.  In your finally block you place a close on
your connection and then return the data set or return the dataset on
the outside of the finally.


try {
  

     ' open connection
     ' fill data set

}
catch ( Exception e )
{
        lblError.Text = e.Message;
}

finally {

  ' close connection
}

'return dataset


Also look into use of the using statement that is good for working with data connections as it uses dispose on the object immediatly then just waiting.


Eric Ramseur
Rainbow Portal 2.0 Admin
Download Rainbow 2.0!!
Rainbow Code
Rainbow Portal Community
C# 2005 Group
0
eramseur
1/5/2006 8:04:37 PM

I did that. I tried the 'try catch' however, when the dataset gets returned I get errors on my codebehind that the connection is closed.

I must be doing something wrong.


Graphfixz Data & Web Design
http://www.graphfixz.com
0
Graphfixz
1/5/2006 9:46:16 PM

What I do on my dataaccess layers is this: Right before I return the dataset I assign it to a variable like so.

Dim ds As Dataset = myCommand.ExecuteNonQuery()

CloseConnection()

return ds

If you do it that way, you can cloase the database from your dataaccess layer.

 

James


James Hearst
.NET Application Developer
0
bluemistonline
1/5/2006 10:42:06 PM
I don't see any place in your code where you actually open the connection.
0
SinkableHail
1/5/2006 11:08:08 PM
// The command below just sets the connection string for your connection
Dim
myConnection As New SqlConnection(ConfigurationSettings.AppSettings("MyCon"))


// You need to add this before assigning this connection to your command. this Opens the connection
myConnection.Open()

<run your sproc here>
<assign to dataset>

//This will close your connection
myConnection.Close()
0
SinkableHail
1/5/2006 11:10:36 PM
Reply:

Similar Artilces:

How do I get the database connections to close down?
Hello all, I really hope some kind soul out there can help me with this problem! I'm using Perl dbi:Pg (as well as DBIx::Recordet) in a CGI script where the users connect to my database through the web. The problem is that the database connections won't close down and I end up with an increasing number of these when 'ps-ef|grep postgres': postgres 22906 695 0 13:40 ? 00:00:00 postgres: apache testdb [local] postgres 22913 695 0 13:41 ? 00:00:00 postgres: apache testdb [local] postgres 22926 695 0 13:47 ? 00:00:00 postgres: apache testdb [local] postgres 22933 695...

Why am I getting a cannot connect, database engine not started error?
I have created an .exe file for distribution...however, when i run it i get an error saying it cannot connect to the database, database engine not started. If I open powerbuilder, start the engine, and then run the exe file..it works fine. What am I missing in my exe or project? Thanks in Advance, RC I guess your exe is trying to connect over network and your database server is installed on your development machine. Assuming that you are using ASA, it has an option to tell the db engine to start if its not already started (ODBC DSN properties). This works fine if the app and t...

Connection.close() not closing my connection?
My servlet calls con.close() within the finally {} of a try..catch block. Yet I notice that sa_conn_info still lists the connection. Shouldn't the connection disappear from the sa_conn_info list when I close it? And if the answer to that is "yes, it should", has anybody seen situations like mine where it *doesn't* close it? Thanks for your help, Kathleen My bad... through a series of really stupid coding mistakes, I managed to cause a SQLException to be thrown from within the finally {} block before the connection could be closed... and I was ignoring the ...

During connection lost, how to close database connection
I'm using TidTCPServer OnExecute method. When client connect to server, I create Database connection with Unidac to PostGreSQL (connection ceated and handled inside onExecute). Problem is when the connection to client is lost, the connection to database does'nt disappear. When cheking from PostGRE pgAdmin there is several connections more than what we have clients. I have tried to put dbConnect.close or FreeandNil(dbConnect) or dbConnect.Free in try.. except.. end except block. But it looks like when the connection is lost, the thread stop/collaps and the database connection stay...

Connection to .mdf database closed immediately after start debug on Visual Studio 2005
Hi all, I have added SQL Database called "LangkaDatabase.mdf" into the project. I have also created a new table called "Members" and populated with data, and added Members dataset into Form1 in the project (by dragging it). But after run the application, the data connection to LangkaDatabase.mdf disconnected immediately (this is shown on the Server Explorer by Red Cross on the LangkaDatabase.mdf). Could someone please show me how to keep the connection open when run the application?? Any help greatly appreciated... Thanks   Hi,  Its not an i...

Connection to .mdf database closed immediately after start debug on Visual Studio 2005 #2
Hi all, I have added SQL Database called "LangkaDatabase.mdf" into the project (by right click the project > add new item > select SQL Database > and so on...). I have also created a new table called "Members" and populated with data, and added Members dataset into Form1 in the project (by dragging it). But after run the application, the data connection to LangkaDatabase.mdf disconnected immediately (this is shown on the Server Explorer by Red Cross on the LangkaDatabase.mdf). Could someone please show me how to keep the connection open when run the ap...

how i run a server report from report manager and at the same time a void closing the .mdf database in order not to get an error that database file is closed when trying to insert a new record.
I have had this problem several times. Every time i open report manager and run a report, i can not insert a new record in the database that serves as the data store for the reports iam hosting on the report server. I get an error that the database is not accessible because its closed.  True, when i click database explorer and right click on the .mdf database file that supplies my sever reports with data and check its properties, its state is closed. After a while (30 to 60 minutes) the database is open again and a new record can be added successfully. This happens on ou...

how to close database connection, connection pool issue
I have create a asp.net 2.0 application with mysql using the .net/connector 5.1. Most of my pages use a ObjectDataSource control and a Repeater Control. I am calling Close() on both the reader and DB connection but it appears that the connections are not closing and the connection pool gets filled up and I get an error. Does anyone know why the connections aren't closing?  Heres the function that the ObjectDataSource calls:Function myFunc(ByVal subCat As String, ByVal sortBy As String) Dim connStr As String = ConfigurationManager.ConnectionStrings("connector").Co...

How to get the result of the database function before connecting to the database .
You can find out which databases the user has access to with HAS_DBACCESS(): select name from master..sysdatabases where has_dbaccess(name) = 1 order by name So when the user selects a site, the front end should check the result of the HAS_DBACCESS() function for each database - if it's 1, then it can be selected; if not, it can't. Please reply asap Thanks How about a special user ID that has only enough authority to check a name? Sign on with it, ask if the user is OK, sign off, sign on with the real user. <jm> wrote in message news:40c608ad.25cd.1681692777@...

get error Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Hi,my interaction with database is completely lost. I cannot even open database and view tablesI get error Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.I am using VS 2005  Error log: SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.2008-09-07 13:10:03.51 Server      Detected 1 CPUs. This is an informational message; no user action is required.2008-09-07 13:10:28.40 Server   &...

close window appliation without connection close of database
 Hello,If i close window application without connection close of database, connection will be closed or it is still there in pool?Thanks VISHAL PAREKH vrparekh@gmail.com:  Hello, If i close window application without connection close of database, connection will be closed or it is still there in pool? Thanks It will be alive unless you close it using Connection.Close()  MAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippets vrparekh@gmail.com:If i close window application without connection close of database, co...

Database Connection???How to connect a Database in an other server?
How to connect a Database(.mdb) in an other server? Can I use the connection string like below? mycon.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=http://www.****.com/***/mydb.mdb" You can not. That simple. Access is file based. No a client/server architecture.Thomas TomiczekPowerNodes ApS(Microsoft MVP C#/.NET)---Building a Website? Try the PowerNodes CMS - http://www.powernodes.com/ Correction: you can. Just make sure you can reach the acces db file - through a file share that is bound as drive. Not that you should. Performance will be horrib...

Unable to connect to database server: Unable to start database engine
I'm not sure if this is the right place to put this. However, here goes. I am using PB 7.0 I set up my database using the ODBC Data Source Administrator configuration for Adaptive Server Anywhere (System DSN). Then I created my profile. I tested the connection and it worked fine. Then I went to Jaguar Manager, created the Connection Cache using the database I just setup. I setup everything currectly including using the odbc32.dll (Driver Tab) I got this error when I pinged it: (error is taken from my aguar's srv.log file) May 24 10:06:32 2001: cm__odbc_error: ...

How to close a connection to the database
Is there any way to close a connection to the database server with Jconnect? I'm opening a connection from an application run through a cgi script on the web. It seems that if there is some kind of problem at runtime, the connections are kept open on the server. In the documentation, I haven't found any instruction to do this. Thanks Guibert Englebienne Are you calling _con.close(); You can call this to disconnect. Make sure if you are doing the connect as part of the same try block as the query, you disconnect in a finally block. Here's another thing...

difference between connection.close and command.connection.close
hello, What is the difference between odbcconnection.close and odbccommand.connection.close? I am making connection once in my application in global.asax and opening connection at application_start event and closing it at application_end event. I am accessing connection through "global.myconnection" in webforms. NOw in the webforms,do I have to mycommand.connection.open before comand.executereader?? Does mycommand.connection.close , closes the odbcconnection that I make in gloabl.asax? It is confusing, because on the first function executing on webform, when I myc...

Web resources about - How to close database connection - asp.net.getting-started

Connection - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

iMedia Connection: Interactive Marketing News, Features, Podcasts and Video - iMediaConnection.com
If you send more email, you might make more money. Then again, you might destroy your reputation and revenue stream. Here's how to know what ...

HTTP persistent connection - Wikipedia, the free encyclopedia
... tacked on to an existing protocol. If the browser supports keep-alive, it adds an additional header to the request: Following this, the connection ...

MOTHER’S DAY: Facebook Examines Connections Between Moms, Kids
With Mother’s Day on the calendar this coming Sunday, Facebook examined the relationships between mothers and their children on the social network, ...

Police tear open roof to arrest man in connection with stabbing murder
Police have torn open the roof of a Gold Coast home to arrest a man in connection with the stabbing death of 16-year-old Michael Brack.

Paris attacks: Belgium connection probed as terrorist past of Brussels district of Molenbeek under spotlight ...
Prosecutors reveal a growing Belgian connection to the deadly Paris attacks, focusing on a poor Brussels district.

Half of data connections by top 500 Android apps are 'covert' with no effect on user experience
Researchers at the Massachusetts Institute of Technology have discovered that half of the communications connections established by the top free ...

Don’t Let Great Content Fall on Deaf Ears: Know your Connection Strategy
... via employee newsletter when it should have come through small-group manager meetings All of these and more are the result of a faulty connection ...

Brussels Connection Under Spotlight After Paris Killings
(EurActiv) — Prosecutors on Sunday disclosed a growing Belgian connection to the Paris attacks as Premier Charles Michel conceded that a Brussels ...

Be Goofy, Smile At Your Baby: Protection Begins with Connection, Pediatrician Says
VideoPart 3 of a series Andrew Garner wears silly ties and finds it easy to make a goofy face. These are valuable tools for charming the babies ...

Resources last updated: 11/22/2015 4:32:52 AM