Asp.net: Why is my session reads only one row of a column in ASP.NET grid view?

Question

I'm trying to read values from a certain column in an ASP.NET Grid View. But when I try to create a session to read them it only reads one row. Any idea what is the possible cause?

This is my code:

Dim cmd As New SqlCommand("select distinct Name,assignment_id, Description ,cod from assignments 
            INNER Join crmsLecturerXCrsSection ON crmsLecturerXCrsSection.emp_key = assignments.emp_key
            INNER Join CRMSStudentsXCrsSection ON CRMSStudentsXCrsSection.CrsSec_id = crmsLecturerXCrsSection.CrsSec_id
            INNER JOIN CRMS_CourseXSection ON CRMS_CourseXSection.CrsSec_id = CRMSStudentsXCrsSection.CrsSec_id
            INNER JOIN CRMSECTIONS ON CRMSECTIONS.SEC_ID = CRMS_CourseXSection.SEC_ID
            left JOIN crmscourses ON crmscourses.crs_id = CRMS_CourseXSection.crs_id
            INNER JOIN CRMS_CourseXSection cs ON CRMS_CourseXSection.SEC_ID = CRMSECTIONS.SEC_ID
            INNER JOIN CRMSSEMESTER ON CRMSSEMESTER.SEM_ID = CRMS_CourseXSection.SEM_ID
            where  CRMSSEMESTER.SEM_ID='1'   
            and crmsLecturerXCrsSection.emp_key='436' and crmscourses.crs_desc='" + Session("crs") + "'", con)



        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()

        da.Fill(dt)

        Dim row As DataRow
        For Each row In dt.Rows
            Session("assid") = row("assignment_id").ToString()
        Next

And this is the other page code:

 Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("dbconnection").ConnectionString)
        Dim cmd As New SqlCommand("select * from assignments where assignment_id='" + Session("assid") + "'", con)
        Dim da As New SqlDataAdapter(cmd)

Here I want to read the assignment's id so I can pass it to another page and it gets passed but only the first row.

Answer 1

Try something like this:

    Dim listIDs As New List(Of String)
    Dim row As DataRow
    For Each row In dt.Rows
        listIDs.Add(row("assignment_id").ToString())
    Next
    Session("assid") = listIDs;

And then in the other function you could get all assignments like:

   Dim cmd As New SqlCommand("select * from assignments where assignment_id in ('" + String.Join("','", Session("assid")) + "')", con)

You would want to check if list is null or empty at the begin of the ButtonClick function as best practice, using a select * also is a bad idea, but as learning practice this would work.