Retrieve multiple items of data from database into one single label

Question

I want to retrieve multiples values from database and display on one label.

My database design currently is:

courseid  scholarshipid  course
--------------------------------
    1           1        maths
    2           1        english
    3           1        science

scholarshipid   schName    
-----------------------
      1         moon 
      2         start
      3         light

I would like to retrieve this information into one single label such as

Maths, English, Science

However, now I manage to retrieve one of it which is just maths.

This is my code in code behind:

protected void btnShowDetails_Click(object sender, EventArgs e)
{
    string Selectedid = GVFBAcc.SelectedRow.Cells[1].Text;//get user id
    int selectedIdtoPass = Convert.ToInt32(Selectedid);

    courseBLL getRecord = new courseBLL();
    addcourse courseRetrieve = new addcourse();
    courseRetrieve = getRecord.getCourseDetail(selectedIdtoPass);

    lbCourse.Text = courseRetrieve.course1 + "," + courseRetrieve.course1;
}

in my courseBLL

public addcourse getCourseDetail(int idcourse)
{
        addcourse userObj = new addcourse();
        courseDAL objtoPass = new courseDAL();
        userObj = objtoPass.displayCourseInfo(idcourse);
        return userObj;
}

in my DAL

public addcourse displayCourseInfo(int idcourse)
{
        string strCommandText = "Select course from course where schokarshipid = @schokarshipid";

        SqlCommand cmd = new SqlCommand(strCommandText, conn);
        cmd.Parameters.AddWithValue("@scholarshipid", idcourse);

        conn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();
        addcourse userObj = new addcourse();

        while (myReader.Read())
        {
            int sID = Convert.ToInt32(myReader["courseID"].ToString());

            string course = myReader["course"].ToString();

            userObj = new addcourse(sID, course);
        }

        myReader.Close();
        return userObj;
}

Edited:

public addcourse displayCourseInfo(int idcourse)
{
        var courses = new List<addcourse>();
        string strCommandText = "Select statement here"

        SqlCommand cmd = new SqlCommand(strCommandText, conn);
        cmd.Parameters.AddWithValue("@scholarshipid", idcourse);

        conn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();

        while (myReader.Read())
        {
            int sID = Convert.ToInt32(myReader["scholarshipid"].ToString());

            string course = myReader["course"].ToString();

            courses.Add(new addcourse(sID,course));
        }

        myReader.Close();

        return courses;
}

I have removed some fields for this purpose so that I will not confuse you

Answer 1

You should return a List<addcourse> from getCourseDetail() and from displayCourseInfo and then you can do this if your addCourse has Course property:

lbCourse.Text = string.Join(", ",  courses.Select(x => x.Course));

If the property name is different then change x.Course to that.

So in your reader you will do this

    var courses = new List<addcourse>();
    while (myReader.Read())
    {
        int sID = Convert.ToInt32(myReader["courseID"].ToString());

        string course = myReader["course"].ToString();


        courses.Add( new addcourse(sID, course)) ;
   } 
   return courses;

Your query is also wrong:

Select  course, from course ss inner join  where courseID =@courseID

So you should fix that and make sure you get the columns you need because right now it is only getting course column and it has syntax errors. Run your query directly against the db using management studio. Once your query works the copy paste it and replace where part with a parameter the way you have it right now.

EDIT

It seems you are still stuck with this so I will give you a more detailed answer; however, you should really study up on some fundamental programming concepts. Anyhow, here it is:

Your code behind:

protected void btnShowDetails_Click(object sender, EventArgs e)
{
    string Selectedid = GVFBAcc.SelectedRow.Cells[1].Text;//get user id
    // Make sure this is the scholarshipid or the whole thing will
    // not work.
    int selectedIdtoPass = Convert.ToInt32(Selectedid);

    courseBLL courseRetriever = new courseBLL();

    // Remember you will get a list here
    List<addcourse> courses = courseRetriever.getCourseDetail(selectedIdtoPass);

    // This will take the list and separate the course property with a comma and space
    var courseNames = string.Join(", ", courses);

    // Now assign it to the text property
    lbCourse.Text = courseNames;
}

Your BLL:

// See here you are retuning a list
// But you need to pass the scholarshipid so you can get the courses for that scholarshipid
public List<addcourse> GerCourses(int scholarshipId)
{
        courseDAL courseRetriever = new courseDAL();

        // Get the list
        List<addcourse> courses = courseRetriever.GetCourses(scholarshipId);
        return courses;
}

Your DAL:

// See here you are retuning a list
// But you need to pass the scholarshipid so you can get the courses for that scholarshipid
public List<addcourse> GetCourses(int scholarshipId)
{
        // Make sure your query is correct, see you have spelling mistakes
        // it should be scholarshipid not schokarshipid. it has to match the column in your database
        // Make sure your table is called course and it has 3 columns: 1. courseid 2. course
        // 3. scholarshipid or this query will not work
        string strCommandText = "Select courseid, course from course where scholarshipid = @scholarshipId";

        SqlCommand cmd = new SqlCommand(strCommandText, conn);
        cmd.Parameters.AddWithValue("@scholarshipid", scholarshipId);

        conn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();

        // create a list so we can hold all the courses
        List<addcourse> userObjs = new List<addcourse>();

        // This will read one row at a time so keep reading until there are no more records
        while (myReader.Read())
        {
            int sID = Convert.ToInt32(myReader["courseid"].ToString());

            string course = myReader["course"].ToString();

            addcourse userObj = new addcourse(sID, course);

            // add it to the list
            userObjs.Add(userObj);
        }

        myReader.Close();

        // return the list
        return userObjs;
}

A few more tips:

1. Give your methods more meaningful names. See the names I gave them GetCourses, now it reads like English and it is easy to follow.
2. Your class addcourse should be called Course. addcourse sounds like an action "add course", that could be a good name for a method if you were adding courses. Also use Pascal notation for class names and method names. Use Camel casing for arguments, parameters and local variables.
3. Learn how to use the Visual Studio debugger. Go watch some YouTube videos on Visual Studio Debugger.

Answer 2

Create a for loop then add it on the variable you are using