instance of sqlDataReader - is not available to an exteranl method

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;
}

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/d...arpspec_3_7.asp

Hope this helps.

Andrew



"Web learner" <beginner@learning.edu> wrote in message
news:O$QhrVpWGHA.1192@TK2MSFTNGP03.phx.gbl...
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;
}

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




"Web learner" wrote:

> 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;
> }

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.


"Web learner" <beginner@learning.edu> wrote in message
news:O$QhrVpWGHA.1192@TK2MSFTNGP03.phx.gbl...
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;
}

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

"Peter Bromberg [C# MVP]" <pbromberg@yahoo.nospammin.com> wrote in message
news:930A005A-C068-48D6-B5A8-361FA11B7034@microsoft.com...
> 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
>
>
>
>
> "Web learner" wrote:
>
>> 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;
>> }

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" <beginner@learning.edu> wrote in message
news:eiIRS7pWGHA.196@TK2MSFTNGP04.phx.gbl...

> 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.

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.


"Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
news:%23SYkwFqWGHA.1192@TK2MSFTNGP04.phx.gbl...
> 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" <beginner@learning.edu> wrote in message
> news:eiIRS7pWGHA.196@TK2MSFTNGP04.phx.gbl...
>
>> 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.
>
>

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>");
}
}
}

"Web learner" <beginner@learning.edu> wrote in message
news:uKcuDfqWGHA.3448@TK2MSFTNGP03.phx.gbl...
> 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.
>
>
> "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
> news:%23SYkwFqWGHA.1192@TK2MSFTNGP04.phx.gbl...
>> 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" <beginner@learning.edu> wrote in message
>> news:eiIRS7pWGHA.196@TK2MSFTNGP04.phx.gbl...
>>
>>> 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.
>>
>>
>
>