Close connection problem

[Varangian]

Hello there people,

I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.

Is there a way I can do to close the Connection. below is the code.
Thank you very much as always

public System.Data.OleDb.OleDbDataReader DataReaderFunc(string
SQLSelect)
{
OleDbDataReader objDataRead = null;
OleDbConnection objConn = new OleDbConnection();

objConn.Close();
objConn.ConnectionString = GetConnection();
OleDbCommand objComm = new OleDbCommand(SQLSelect, objConn);
objConn.Open();
objDataRead = objComm.ExecuteReader(CommandBehavior.CloseConnect ion);

Console.WriteLine (objConn.State);
//objConn.Dispose();
return objDataRead;
}

[Kevin Spencer]

Your Connection remains open, alright. Worse than that, its scoped inside a
method, so when your method exits, you lose any handle on it you had while
the method was executing.

A DataReader requires a persistent Connection. For the entire time you use a
DataReader, its Connection must remain open. Therefore, you need to declare
your Connection outside of the method, so that when you are finished with
the DataReader, you can close the Connection.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
You can lead a fish to a bicycle,
but it takes a very long time,
and the bicycle has to *want* to change.

"Varangian" <of****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hello there people,

I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.

Is there a way I can do to close the Connection. below is the code.
Thank you very much as always

public System.Data.OleDb.OleDbDataReader DataReaderFunc(string
SQLSelect)
{
OleDbDataReader objDataRead = null;
OleDbConnection objConn = new OleDbConnection();

objConn.Close();
objConn.ConnectionString = GetConnection();
OleDbCommand objComm = new OleDbCommand(SQLSelect, objConn);
objConn.Open();
objDataRead = objComm.ExecuteReader(CommandBehavior.CloseConnect ion);

Console.WriteLine (objConn.State);
//objConn.Dispose();
return objDataRead;
}

[Marina]

He is passing in ConnectionBehavior.CloseConnection. So as long as the data
reader is closed, so will the connection, and that will avoid a connection
leak. This means that the connection is fine declared where it is.

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:es*************@TK2MSFTNGP14.phx.gbl...

Your Connection remains open, alright. Worse than that, its scoped inside
a method, so when your method exits, you lose any handle on it you had
while the method was executing.

A DataReader requires a persistent Connection. For the entire time you use
a DataReader, its Connection must remain open. Therefore, you need to
declare your Connection outside of the method, so that when you are
finished with the DataReader, you can close the Connection.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
You can lead a fish to a bicycle,
but it takes a very long time,
and the bicycle has to *want* to change.

"Varangian" <of****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hello there people,

I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.

Is there a way I can do to close the Connection. below is the code.
Thank you very much as always

public System.Data.OleDb.OleDbDataReader DataReaderFunc(string
SQLSelect)
{
OleDbDataReader objDataRead = null;
OleDbConnection objConn = new OleDbConnection();

objConn.Close();
objConn.ConnectionString = GetConnection();
OleDbCommand objComm = new OleDbCommand(SQLSelect, objConn);
objConn.Open();
objDataRead = objComm.ExecuteReader(CommandBehavior.CloseConnect ion);

Console.WriteLine (objConn.State);
//objConn.Dispose();
return objDataRead;
}

[Kevin Spencer]

Ah, yes. Sorry, overlooked it.

Well, the only thing left is whether or not he's closing the DataReader
outside the method somewhere.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
You can lead a fish to a bicycle,
but it takes a very long time,
and the bicycle has to *want* to change.

"Marina" <so*****@nospam.com> wrote in message
news:uu*************@TK2MSFTNGP10.phx.gbl...

He is passing in ConnectionBehavior.CloseConnection. So as long as the
data reader is closed, so will the connection, and that will avoid a
connection leak. This means that the connection is fine declared where it
is.

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:es*************@TK2MSFTNGP14.phx.gbl...

Your Connection remains open, alright. Worse than that, its scoped inside
a method, so when your method exits, you lose any handle on it you had
while the method was executing.

A DataReader requires a persistent Connection. For the entire time you
use a DataReader, its Connection must remain open. Therefore, you need to
declare your Connection outside of the method, so that when you are
finished with the DataReader, you can close the Connection.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
You can lead a fish to a bicycle,
but it takes a very long time,
and the bicycle has to *want* to change.

"Varangian" <of****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hello there people,

I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.

Is there a way I can do to close the Connection. below is the code.
Thank you very much as always

public System.Data.OleDb.OleDbDataReader DataReaderFunc(string
SQLSelect)
{
OleDbDataReader objDataRead = null;
OleDbConnection objConn = new OleDbConnection();

objConn.Close();
objConn.ConnectionString = GetConnection();
OleDbCommand objComm = new OleDbCommand(SQLSelect, objConn);
objConn.Open();
objDataRead = objComm.ExecuteReader(CommandBehavior.CloseConnect ion);

Console.WriteLine (objConn.State);
//objConn.Dispose();
return objDataRead;
}

[Marina]

In my experience, when there is a connection leak and there is a problem
obtaining a new connection, the error message is usually something that
mentions timing out getting a new connection from the connection pool.

Also, database errors will usually have more information other then
'unspecified error'. Are you sure this is the complete error message in the
exception being thrown?

To avoid connection leaks in this type of code, you should make sure you
close the datareader in whatever code is calling this function, to make sure
that the connection the data reader is using is closed.

"Varangian" <of****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hello there people,

I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.

Is there a way I can do to close the Connection. below is the code.
Thank you very much as always

public System.Data.OleDb.OleDbDataReader DataReaderFunc(string
SQLSelect)
{
OleDbDataReader objDataRead = null;
OleDbConnection objConn = new OleDbConnection();

objConn.Close();
objConn.ConnectionString = GetConnection();
OleDbCommand objComm = new OleDbCommand(SQLSelect, objConn);
objConn.Open();
objDataRead = objComm.ExecuteReader(CommandBehavior.CloseConnect ion);

Console.WriteLine (objConn.State);
//objConn.Dispose();
return objDataRead;
}

[Varangian]

Unspecified error
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Unspecified error
-

Line 54: OleDbCommand objComm = new OleDbCommand(SQLSelect,
objConn);
Line 55:
Line 56: objConn.Open();
Line 57: objDataRead =
objComm.ExecuteReader(System.Data.CommandBehavior. CloseConnection);

This is the error I'm getting. with the
ExecuteFunction(CloseConnection) the application lasts a bit more..
however there's still something wrong and the error points into the
objConn.Open();

:) Thanks for the Help