SQL Data Reader into Label - Value doesn't display

Question

DB-Acess.cs

This is where the Public SqlDataReader getEmail is initialised.

public SqlDataReader getEmail(string UserName)
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            //string noemail ="noemailsaved";
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "Select Email from dbo.EMPLOYEE where Username ='" + UserName + "'";
            SqlDataReader reader = newCmd.ExecuteReader();
            while (reader.Read())
            {
                string email = reader["EMPLOYEE.Email"].ToString();

            }
            conn.Close();
            reader.Close();
            return reader;
          }

I'm using OOP and calling the function in asp.net page and want to display the value in a label. Below is the code I'm using to call the function.

SqlDataReader reader = dba.getEmail(pname);
lblEmail.Text = reader.ToString();
lblEmail.DataBind();

Instead of seeing the Email address of the Employee i'm seeing System.Data.SqlClient.SqlDataReader

Please help in correcting this error.

Thank you in advance.

logixologist · Answer 1 · 2016-08-01T21:45:36.477

There are a few things going wrong here:

1) You are setting the string email to the value of the reader. Which because you are declaring it inside the reader, will never be able to be used. You will lose scope immediately.

2) You are doing this:

lblEmail.Text = reader.ToString();
lblEmail.DataBind();

This is setting the label to the name of the reader (the instance), not the value the reader is producing. No reason to bind, either.

A better way to do it is

lblEmail.Text = email;

Make sure you declare the email variable outside the reader

score 1 · Answer 2 · edited May 23 '17 at 10:28

So there are so many issues going on, I decided to write this comment as the beginning to an appropriate solution.

First your method is called getEmail -- shouldn't it return the email (in other words, a string instead).

public string GetEmail(string UserName)
    {
        string email = string.Empty;
        if (conn.State.ToString() == "Closed")
        {
            conn.Open();
        }
        //string noemail ="noemailsaved";
        SqlCommand newCmd = conn.CreateCommand();
        newCmd.Connection = conn;
        newCmd.CommandType = CommandType.Text;
        newCmd.CommandText = "Select Email from dbo.EMPLOYEE where Username ='" + UserName + "'";
        SqlDataReader reader = newCmd.ExecuteReader();
        while (reader.Read())
        {
            email = reader["EMPLOYEE.Email"].ToString();

        }
        conn.Close();
        reader.Close();
        return email;
      }

Then all you have to do is:

lblEmail.Text = db.GetEmail(pname);

That should at least get you going. You should also look into using parameterized queries as well as the using statement.

Why do we always prefer using parameters in SQL statements?

Thank you for answer. I prompt an error "There is already an open DataReader associated with this Command which must be closed first." any idea what causes this? — Omesh, Aug 01 '16 at 22:06
@Omesh -- that means your original code isn't working as I didn't change that. Where is the error coming from? At first look, should you call reader.Close() before conn.Close()? — sgeddes, Aug 01 '16 at 22:08

score 0 · Answer 3 · answered Aug 01 '16 at 21:37

Yes that's cause you are calling ToString() on reader object and thus it just printing the classname fully qualified reader.ToString().

Moreover, you are dong it wrong. Current code shouldn't work since you are returning reader which has already been closed and thus you can't read from it. Rather, you should change your method to return the email and use it like

public string getEmail(string UserName)
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            //string noemail ="noemailsaved";
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
           // Hopefully your query returns a single email record
            newCmd.CommandText = "Select Email from dbo.EMPLOYEE where Username ='" + UserName + "'";
            SqlDataReader reader = newCmd.ExecuteReader();
            string email = string.Empty;
            while (reader.Read())
            {
                email = reader["EMPLOYEE.Email"].ToString();

            }
            conn.Close();
            reader.Close();
            return email;
          }

Moreover if your query returns a single email value then use ExecuteScalar() rather like

        string email = newCmd.ExecuteScalar() as string;

Now you can assign it in caller

lblEmail.Text = dba.getEmail(pname);

Thank you for answer. I prompt an error "There is already an open DataReader associated with this Command which must be closed first." any idea what causes this? — Omesh, Aug 01 '16 at 22:06
@Omesh, somewhere else in your code you have already opened another reader object which is still open. check it. — Rahul, Aug 01 '16 at 22:08

jonju · Accepted Answer · 2016-08-01T22:22:38.513

ERRORS

Return type of the function getEmail is SqlDataReader and you are expecting String i.e. an Email.
Declaration of email in string email = reader["EMPLOYEE.Email"].ToString(); is inside while loop. Therefore, email becomes local to the while loop. It will not recognize outside the loop.
And you are returning reader' an instance ofSqlDataReader,but you were expecting aString`.
In you second code block, what your doing is not wrong(it won't give error) but that is not what you are expecting to get. You should be declaring a String variable eg. email and assign the function to it(or you can directly assign it to lblEmail Text property.

SUGGESTION

The way you are checking ConnectionState in if(conn.State.ToString() == "Closed") may give you the desired result but is not recommended. Instead you should check like this if (conn.State == ConnectionState.Closed).

Now the most awaiting part: The improvised code: lol!

UPDATE

public string getEmail(string UserName){
    if (conn.State == ConnectionState.Closed){
        conn.Open();
    }
    //string noemail ="noemailsaved";
    string email="";
    using(SqlCommand newCmd = new SqlCommand()){
        newCmd.Connection = conn;
        newCmd.CommandType = CommandType.Text;
        newCmd.CommandText = "Select Email From dbo.EMPLOYEE Where Username = @uname";
        newCmd.Parameters.AddWithValue("@uname",UserName);
        using(SqlDataReader reader = newCmd.ExecuteReader()){
              while (reader.Read()){
                   email = reader["Email"].ToString();
              }
        }
    }
    conn.Close();
    //reader.Close();
    return email ;
}

For setting the Label Text

lblEmail.Text = dba.getEmail(pname);

Thank you for answer. I prompt an error "There is already an open DataReader associated with this Command which must be closed first." any idea what causes this? — Omesh, Aug 01 '16 at 22:07
try disposing the SqlCommand & reader: `if(newCmd!=null){newCmd.Dispose();} if(reader!=null){reader.Dispose();}` or you can use `using` statement — jonju, Aug 01 '16 at 22:13
IndexOutofRangeException for Employee.Email. Any idea on how to correct this? @jonju — Omesh, Aug 01 '16 at 22:20
Ofcourse it should be `Email` only not `Employee.Email`. How could i missed it? — jonju, Aug 01 '16 at 22:22
So easy yet so difficult lol !! Anyway really glad I could helped you — jonju, Aug 01 '16 at 22:26

score 0 · Answer 5 · answered Mar 29 '20 at 05:28

string q,d; int ano=0;

    SqlConnection con = new SqlConnection("Data Source=SANDEESQLEXPRESS;Initial Catalog=agent demo;Integrated Security=True");
    con.Open();

    SqlCommand cmd = new SqlCommand("select * from po where agentno=@ano", con);
    cmd.Parameters.AddWithValue("ano",ano);

    SqlDataReader dr = cmd.ExecuteReader();

    if (dr.Read())
    {
        d = dr["date1"].ToString(); 
    }

    dr.Close();

    Label1.Text = d+ "";
   does not show value of date in lablel

I fail to see how your answer relates to the question? – Torben Koch Pløen Mar 29 '20 at 09:20 — Torben Koch Pløen, Mar 29 '20 at 09:20

SQL Data Reader into Label - Value doesn't display

5 Answers5