how to close sqldatareader when error occur

[mimi]

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteRead er(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeRead er to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}

[Chris Carter]

You can declare this:

SqlDataReader dr = null;

you can let the framework handle that though:

using(SqlConnec tion oConn = new SqlConnection(" My Conn String"))
using(SqlComman d cmd = new SqlCommand("Get Logs", oConn))
{
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Parameters. Add("@ID", SqlDbType.Int). Value = logID;
using(SqlDataRe ader dr = cmd.ExecuteRead er())
{
if(dr.Read())
notes = dr["Notes"].ToString();
}
}
"mimi" wrote:

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteRead er(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeRead er to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}

[Kan Coon]

Yes you can declare it out side the try block:

Dim dr as datareader

Try
dr = cmd.ExecuteRead er();
Catch ex as SQlException

Finally
dr.close
End try

You should also be closing that datareader in the finally section of your
exception handler.
"mimi" <mh****@hotmail .com> wrote in message
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteRead er(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeRead er to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}

[Kevin Spencer]

Hi mimi,

You certainly CAN declare it outside the try scope. All you are declaring is
a variable, not a SqlDataReader. The variable is like a box to put the
SqlDataReader IN. You should also set it to null, and check for null when
you attempt to close it. And you should close it in the finally block, which
ALWAYS executes. Example:

public string GetLogs(int logID)

{

string notes = String.Empty;

SqlDataReader dr = null;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{
...
}
finally
{
if (dr != null)
dr.Close()
return notes;
}
}

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
The sun never sets on
the Kingdom of Heaven

"mimi" <mh****@hotmail .com> wrote in message
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteRead er(). I can't close it in catch because
it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeRead er to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}

[mimi]

thanks. It works for me with setting SqlDataReader to null

public string GetLogs(int logID)
{
string notes = String.Empty;
SqlDataReader dr = null;

try
{
SqlCommand cmd = new SqlCommand("Get Logs", oConn);
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;
dr = cmd.ExecuteRead er();

if (dr.Read())
{
notes = dr["Notes"].ToString();
}
}
catch(SqlExcept ion ex)
{
...
}
finally
{
if (dr != null)
dr.Close()
}
return notes;

}

"Kevin Spencer" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:OH******** ******@TK2MSFTN GP15.phx.gbl...

Hi mimi,

You certainly CAN declare it outside the try scope. All you are declaring is a variable, not a SqlDataReader. The variable is like a box to put the
SqlDataReader IN. You should also set it to null, and check for null when
you attempt to close it. And you should close it in the finally block, which ALWAYS executes. Example:

public string GetLogs(int logID)

{

string notes = String.Empty;

SqlDataReader dr = null;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{
...
}
finally
{
if (dr != null)
dr.Close()
return notes;
}
}

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
The sun never sets on
the Kingdom of Heaven

"mimi" <mh****@hotmail .com> wrote in message
news:%2******** *******@TK2MSFT NGP09.phx.gbl.. .

Hi

Please help me out, I can't find a way to close a sqldatareader when error occur at statement cmd.ExecuteRead er(). I can't close it in catch because it is local in try scope and I can't declare it outside try scope either
since we have to call cmd.executeRead er to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("Get Logs", oConn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@ID",SqlDb Type.Int).Value = logID;

SqlDataReader dr = cmd.ExecuteRead er();

if (dr.Read())

{

notes = dr["Notes"].ToString();

}

dr.Close();

}

catch(SqlExcept ion ex)

{

//can't call dr.Close() because it's not global

}

return notes;

}