instance of sqlDataReader - is not available to an exteranl method

[Web learner]

I am trying to create a method GetDataFor(string column) becaues I have to repeat the same statements for several columns but I get an error as follows:

The name 'dr' does not exist in the current context

It seems the dr -the instance of sqlDataReader - is not becoming available to the method. How to make it available? This seems trivial and newbie problem related to OOP, but I am confused. Could you pl. look at the code or point me to some relevant URL.

Thanks !


protected void Page_Load(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE (Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column))) //ERROR: The name 'dr' does not exist in the current context
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

 

[Andrew]

SqlDataReader dr is being declared inside Page_Load() that means it's scope
is limited to the Page_Load method. GetDataFor() cannot "see" dr. Declare
dr outside of Page_Load().

SqlDataReader dr = new SqlDataReader();

protected void Page_Load(object sender, EventArgs e)
{
...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csspec/html/vclrfcsharpspec_3_7.asp

Hope this helps.

Andrew



I am trying to create a method GetDataFor(string column) becaues I have to
repeat the same statements for several columns but I get an error as
follows:

The name 'dr' does not exist in the current context

It seems the dr -the instance of sqlDataReader - is not becoming available
to the method. How to make it available? This seems trivial and newbie
problem related to OOP, but I am confused. Could you pl. look at the code or
point me to some relevant URL.

Thanks !


protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column))) //ERROR: The name
'dr' does not exist in the current context
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

 

[Guest]

if you want your SqlDataReader to be visible in private or public methods
within which it was not declared, you would need to declare it at the class
level:

private SqlDataReader dr=null;
protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE (Year(obsDate)=2004)
ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

-- Does that help?
Peter

 

[Jim Hughes]

Modify the function signature to:

public double GetDataFor(SqlDataReader dr, string column)

and the call to it as:
Response.Write(GetDataFor(dr, "meanTemp ").ToString() + "<br>");

Resist the urge to make the scope of dr page level to avoid conflicts with
other functions on the page.


I am trying to create a method GetDataFor(string column) becaues I have to
repeat the same statements for several columns but I get an error as
follows:

The name 'dr' does not exist in the current context

It seems the dr -the instance of sqlDataReader - is not becoming available
to the method. How to make it available? This seems trivial and newbie
problem related to OOP, but I am confused. Could you pl. look at the code or
point me to some relevant URL.

Thanks !


protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column))) //ERROR: The name
'dr' does not exist in the current context
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

 

[Web learner]

Peter's suggestion just worked, and I am happy. However, to get wiser and
become a little bit helping like you all, I need to make my fundamentals
clear. I'll appreciate poiniting to any ASP.NET example on the web.

I am trying to have a look at suggestions of Jim and Andrew.

Jim, do you mean I should avoid adding line
private SqlDataReader dr=null;
above Page_load method? Without adding SqlDataReader dr=null; the suggested
code did not work. May be I have to add something more.

Thanks everybody,

web_learner

if you want your SqlDataReader to be visible in private or public methods
within which it was not declared, you would need to declare it at the
class
level:

private SqlDataReader dr=null;
protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004)
ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

-- Does that help?
Peter


--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

I am trying to create a method GetDataFor(string column) becaues I have
to repeat the same statements for several columns but I get an error as
follows:

The name 'dr' does not exist in the current context

It seems the dr -the instance of sqlDataReader - is not becoming
available to the method. How to make it available? This seems trivial and
newbie problem related to OOP, but I am confused. Could you pl. look at
the code or point me to some relevant URL.

Thanks !


protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor("meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column))) //ERROR: The
name 'dr' does not exist in the current context
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

 

[Jim Hughes]

Yes, that is what I meant. Both changes that I suggested were necessary, and
no others. Don't mix my suggestion with Peter's. While his suggestion will
work, it increases the scope of dr unecessarily.

Other things you should look at are the c# "using" statement for the
SqlConnection, the DataReader CommandBehavior.CloseConnection and
parameterized queries (regarding hard coding 2004).

the entire code should read:

protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor(dr, "meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(SqlDataReader dr, string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column)))
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

 

[Web learner]

This too worked fine. Following your suggestion about using the "c# using",
I've tried to look at example codes of Personal Web starter kit for Visual
web developer express 2005. There are examples, but being a learner/novice
who wants to grasp basic concepts in ASP.NET web programming, I found it a
bit overwhelming. I'll keep on trying and would prefer a book with
exercises.

Thanks/regards,
web_learner.

Yes, that is what I meant. Both changes that I suggested were necessary,
and no others. Don't mix my suggestion with Peter's. While his suggestion
will work, it increases the scope of dr unecessarily.

Other things you should look at are the c# "using" statement for the
SqlConnection, the DataReader CommandBehavior.CloseConnection and
parameterized queries (regarding hard coding 2004).

the entire code should read:

protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor(dr, "meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(SqlDataReader dr, string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column)))
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

Jim, do you mean I should avoid adding line
private SqlDataReader dr=null;
above Page_load method? Without adding SqlDataReader dr=null; the
suggested code did not work. May be I have to add something more.

 

[Jim Hughes]

Here is an example with my suggestions.

protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;

// using means you do not have to explicitly close or dispose the connection
using (SqlConnection objConnection = new SqlConnection(strConnection) )
{
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=@TargetYear) ORDER BY obsDate;";

SqlCommand objCommand = new SqlCommand(strSQL, objConnection);

// @TargetYear value could be obtained from a form field or querystring
// Validation of parameters should also be performed here.
objCommand.Parameters.AddWithValue(@TargetYear, "2004");

// CommandBehavior.CloseConnection means you don't have to remember to close
the Reader
SqlDataReader dr =
objCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read()) {
Response.Write(GetDataFor(dr, "meanTemp ").ToString() + "<br>");
}
}
}

This too worked fine. Following your suggestion about using the "c#
using", I've tried to look at example codes of Personal Web starter kit
for Visual web developer express 2005. There are examples, but being a
learner/novice who wants to grasp basic concepts in ASP.NET web
programming, I found it a bit overwhelming. I'll keep on trying and would
prefer a book with exercises.

Thanks/regards,
web_learner.

Yes, that is what I meant. Both changes that I suggested were necessary,
and no others. Don't mix my suggestion with Peter's. While his suggestion
will work, it increases the scope of dr unecessarily.

Other things you should look at are the c# "using" statement for the
SqlConnection, the DataReader CommandBehavior.CloseConnection and
parameterized queries (regarding hard coding 2004).

the entire code should read:

protected void Page_Load(object sender, EventArgs e)
{
string strConnection =
ConfigurationManager.ConnectionStrings["strConnMyDb"].ConnectionString;
SqlConnection objConnection = new SqlConnection(strConnection);
string strSQL = "SELECT meanTemp FROM tblWeather WHERE
(Year(obsDate)=2004) ORDER BY obsDate;";
SqlCommand objCommand = new SqlCommand(strSQL, objConnection);
objConnection.Open();
SqlDataReader dr = objCommand.ExecuteReader();
while (dr.Read()) {
Response.Write(GetDataFor(dr, "meanTemp ").ToString() + "<br>");
}
dr.Close();
objConnection.Close();
}

public double GetDataFor(SqlDataReader dr, string column)
{
double result = (dr.IsDBNull(dr.GetOrdinal(column)))
? 9999 : double.Parse(dr[colum].ToString());
return result;
}

Jim, do you mean I should avoid adding line
private SqlDataReader dr=null;
above Page_load method? Without adding SqlDataReader dr=null; the
suggested code did not work. May be I have to add something more.