Issue with method

Question

I've got another issue. My method:

public StudentProfile GetFullStudentProfile(int userID)
{
    SqlConnection conn = new SqlConnection(Config.DbConnectionString);
    SqlCommand cmd = new SqlCommand("GetFullUserProfile", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int));
    cmd.Parameters["@UserID"].Value = userID;
    StudentProfile sp = null;
    try
    {
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (reader.Read())
        {
            sp = new StudentProfile((int)reader["UserID"], (string)reader["UserName"], (string)reader["UserFamilyName"], (DateTime)reader["UserBirthDay"], (string)reader["UserTelephone"], (string)reader["UserEmail"], (DateTime)reader["UserRegDate"], (string)reader["UserComment"], (bool)reader["UserActive"]);
        }

        reader.Close();

    }
    catch (Exception ex)
    {
        //lbl.Text = ex.Message;
    }
    finally
    {
        conn.Close();
    }
    return sp;
}

Code in WebUserControl:

protected void ddlStudents_SelectedIndexChanged(object sender, EventArgs e)
{
    CatalogAccess ca=new CatalogAccess();
    lblEmail.Visible = true;
    lblName.Visible = true;

    lblTelephone.Visible = true;
    HiddenID.Value = ddlStudents.SelectedValue;
    lblName.Text = HiddenID.Value;
    lblEmail.Text = ca.GetFullStudentProfile(ddlStudents.SelectedIndex).UserEmail;
    lblFamilyName.Visible = true;
    lblBirth.Visible = true;
    ddlStudents.Items.Clear();
    PopulateStudentsDDL();

}

When I run the code I am getting exception

Do I understand right that the problem is in method GetFullStudentProfile()? If it's so, how can it be fixed?

I think the problem is:

StudentProfile sp = null;

So it says that variable sp was not filled with data in try block. Is it?

Now adding the StudentProfile.cs class listing:

public class StudentProfile

{ public StudentProfile(int userID, string userName, string userFamilyName, DateTime userBirthDay, string userTelephone, string userEmail,DateTime userRegDate,string userComment,bool userActive) { UserID = userID; UserName = userName; UserFamilyName = userFamilyName; UserBirthDay = userBirthDay; UserTelephone = userTelephone; UserEmail = userEmail; UserRegdate = userRegDate; UserComment = userComment; UserActive = userActive; }

public int UserID
{
    get;
    set;
}

public string UserName
{
    get;
    set;
}

public string UserFamilyName
{
    get;
    set;
}

public DateTime UserBirthDay
{
    get;
    set;
}

public string UserTelephone
{
    get;
    set;
}

public string UserEmail
{
    get;
    set;
}

public DateTime UserRegdate
{
    get;
    set;
}

public string UserComment
{
    get;
    set;
}

public bool UserActive
{
    get;
    set;
}

}

Answer 1

What exactly is null? I bet GetFullStudentProfile method returns null.

Answer 2

Yes, that's exactly what happened. The student matching passed ID is not found. I would do the following in your Client code:

StudentProfile sp=ca.GetFullStudentProfile(ddlStudents.SelectedIndex);
lblEmail.Text = sp != null ? sp.UserEmail : string.Empty;

Also i would rewrite the GetFullStudentProfile method's reader operation to something like this:

conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

if (reader.Read())
{
    sp = new StudentProfile((int)reader["UserID"], (string)reader["UserName"], (string)reader["UserFamilyName"], (DateTime)reader["UserBirthDay"], (string)reader["UserTelephone"], (string)reader["UserEmail"], (DateTime)reader["UserRegDate"], (string)reader["UserComment"], (bool)reader["UserActive"]);
}

reader.Close();

It's just a face-lift for the routine, looks nicer imho

Answer 3

Also:

HiddenID.Value = ddlStudents.SelectedValue;
lblName.Text = HiddenID.Value;

I noticed in your Autos that the name was set as 1. So I think you actually are meaning to set it as:

lblName.Text = ddlStudents.SelectedValue;

I don't believe that setting the value equal to itself is going to benefit you in any way. Check your database, does the column for UserEmail allow nullables?

Also, in your database, I believe that UserID increments starting at 1, correct? SelectedIndex, I believe, starts at 0. You may need to increment the selectedvalue item by 1.

I just looked at your other question (http://stackoverflow.com/questions/5745820/assign-list-member-to-label) and I feel I have a bit more info here.

If your database doesn't allow nullable types, I would change:

CatalogAccess ca=new CatalogAccess();
lblEmail.Visible = true;
lblName.Visible = true;

lblTelephone.Visible = true;
HiddenID.Value = ddlStudents.SelectedValue;
lblName.Text = HiddenID.Value;
lblEmail.Text = ca.GetFullStudentProfile(ddlStudents.SelectedIndex).UserEmail;
lblFamilyName.Visible = true;
lblBirth.Visible = true;
ddlStudents.Items.Clear();
PopulateStudentsDDL();

to:

CatalogAccess ca=new CatalogAccess();
lblEmail.Visible = true;
lblName.Visible = true;

lblTelephone.Visible = true;

// if your table's ID increments starting at 0, otherwise ddlStudents.SelectedIndex + 1
StudentProfile student = GetFullStudentProfile(ddlStudents.SelectedIndex);

if (student != null)
{
    lblName.Text = student.UserName;
    lblEmail.Text = student.UserEmail;
    //rest of the labels going here (I only see Name and Email)
}

...

You also may consider putting your calls making labels visible inside of the sp != null conditional so that only if a valid person displays will it actually display those labels. Otherwise keep them before it.

Otherwise I would do similar to Dimitri and say

lblName.Text = student.UserName != null ? student.UserName : String.Empty;
lblEmail.Text = student.UserEmail != null ? student.UserEmail : String.Empty;
// etc.

If nullables are possible, the student object could actually be assigned to, but the properties may not which may cause your application problems at runtime.

Answer 4

Ok. I made a new method:

public UserDetails GetUser(int userID)
{
    SqlConnection conn = new SqlConnection(Config.DbConnectionString);
    SqlCommand cmd = new SqlCommand("GetFullUserProfile", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 4));
    cmd.Parameters["@UserID"].Value = userID;
    try
    {
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
        // Check if the query returned a record.
        if (!reader.HasRows) return null;
        // Get the first row.
        reader.Read();
        UserDetails emp = new UserDetails((int)reader["UserID"], (string)reader["UserName"], (string)reader["UserFamilyName"], (DateTime)reader["UserBirthDate"], (string)reader["UserTelephone"], (string)reader["UserEmail"], (DateTime)reader["RegDate"], (string)reader["UserComment"], (int)reader["UserActive"]);
        reader.Close();
        return emp;
    }
    catch (SqlException err)
    {
        throw new ApplicationException("Data error.");
    }
    finally
    {
        conn.Close();
    }
}

New Class:

using System;

public class UserDetails
{
    public UserDetails(int userID, string userName, string userFamilyName, DateTime userBirthDate, string userTelephone, string userEmail, DateTime regDate, string userComment, int userActive)
    {
        UserID = userID;
        UserName = userName;
        UserFamilyName = userFamilyName;
        UserBirthDay = userBirthDate;
        UserTelephone = userTelephone;
        UserEmail = userEmail;
        RegDate = regDate;
        UserComment = userComment;
        userActive = UserActive;
    }

    public int UserID
    { get; set; }

    public string UserName
    { get; set; }

    public string UserFamilyName
    { get; set; }

    public DateTime UserBirthDay
    { get; set; }

    public string UserTelephone
    { get; set; }

    public string UserEmail
    { get; set; }

    public DateTime RegDate
    { get; set; }

    public string UserComment
    { get; set; }

    public int UserActive
    { get; set; }
}

After that in my back-code of WebUserControl:

UserDetails ud = ca.GetUser(24);
    //HiddenID.Value = String.Format(ud.UserID);
    lblName.Text = ud.UserName;
    lblEmail.Text = ud.UserEmail;
    lblFamilyName.Text = ud.UserFamilyName;
    lblBirth.Text=String.Format("{0:dd/MM/yyy}",ud.UserBirthDay);
    lblTelephone.Text = ud.UserTelephone;

And got the method worked. So, this stage is ready. Thanks to everybody who helped me. Thread is closed.