469,887 Members | 1,206 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,887 developers. It's quick & easy.

how to close sqldatareader when error occur

Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteReader(). 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.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException ex)

{

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

}

return notes;

}
Nov 19 '05 #1
4 2198
You can declare this:

SqlDataReader dr = null;

you can let the framework handle that though:

using(SqlConnection oConn = new SqlConnection("My Conn String"))
using(SqlCommand cmd = new SqlCommand("GetLogs", oConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = logID;
using(SqlDataReader dr = cmd.ExecuteReader())
{
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.ExecuteReader(). 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.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException ex)

{

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

}

return notes;

}

Nov 19 '05 #2
Yes you can declare it out side the try block:

Dim dr as datareader

Try
dr = cmd.ExecuteReader();
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***************@TK2MSFTNGP09.phx.gbl...
Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteReader(). 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.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException ex)

{

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

}

return notes;

}

Nov 19 '05 #3
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("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException 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***************@TK2MSFTNGP09.phx.gbl...
Hi

Please help me out, I can't find a way to close a sqldatareader when error
occur at statement cmd.ExecuteReader(). 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.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException ex)

{

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

}

return notes;

}

Nov 19 '05 #4
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("GetLogs", oConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID",SqlDbType.Int).Value = logID;
dr = cmd.ExecuteReader();

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

}

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:OH**************@TK2MSFTNGP15.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("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException 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***************@TK2MSFTNGP09.phx.gbl...
Hi

Please help me out, I can't find a way to close a sqldatareader when error occur at statement cmd.ExecuteReader(). 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.executeReader to create sqldatareader

public string GetLogs(int logID)

{

string notes = String.Empty;

// cannot declare like SqlDataReader dr;

try

{

SqlCommand cmd = new SqlCommand("GetLogs", oConn);

cmd.CommandType = CommandType.StoredProcedure;

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

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

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

}

dr.Close();

}

catch(SqlException ex)

{

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

}

return notes;

}


Nov 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

22 posts views Thread by Bryan | last post: by
3 posts views Thread by sam | last post: by
1 post views Thread by huzz | last post: by
3 posts views Thread by branton ellerbee | last post: by
3 posts views Thread by Woody Splawn | last post: by
5 posts views Thread by jjmraz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.