asp.net - Binding sqldatareader to gridview c#

Question

I am creating an application for a asp.net class that I am taking. One of the pages in the application needs to allow a user to search for a specific student via last name or user ID. When the student is found the page should display the students data and his/her class schedule.

I have gotten everything to work except for the class schedule. The approach I have taken (as we learned in class) was to get the query results via the SqlDataReader and bind it to a GridView. This is done in showStudentSchedule().

The query in this function returns the correct results when I test it against the DB I created, but the grid view displaying a students schedule doesn't show up on the page.

//StudentInformation.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="StudentInformation.aspx.cs" Inherits="StudentInformation"  %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <p>
        <asp:Label ID="Label6" runat="server" Text="Search by Last Name: "></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
        <asp:DropDownList ID="DropDownList1" runat="server" 
            onselectedindexchanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True">
        </asp:DropDownList>
    </p>

    <asp:Label ID="Label1" runat="server"></asp:Label>
    <br />
    <asp:Label ID="Label2" runat="server"></asp:Label>
    <br />
    <asp:Label ID="Label3" runat="server"></asp:Label>
    <br />
    <asp:Label ID="Label4" runat="server"></asp:Label>
    <br />
    <asp:Label ID="Label5" runat="server"></asp:Label>
    <asp:Panel ID="Panel1" runat="server">
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </asp:Panel>
</asp:Content>

//StudentInformation.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class StudentInformation : System.Web.UI.Page
{ 

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string userStr = TextBox1.Text;
        int userInt;
        bool isNum = int.TryParse(userStr, out userInt);
        string sqlSelectFindUserByName;

        if (isNum)
            sqlSelectFindUserByName = string.Format("SELECT LastName FROM Personal_Info JOIN Students ON Personal_Info.ID = Students.Student_ID WHERE Personal_Info.ID = '{0}'", userInt);
        else
            sqlSelectFindUserByName = string.Format("SELECT LastName FROM Personal_Info JOIN Students ON Personal_Info.ID = Students.Student_ID WHERE Personal_Info.LastName LIKE '%{0}%'", userStr);

        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;


        SqlCommand commandFindUserByName = new SqlCommand(sqlSelectFindUserByName, connection);

        connection.Open();

        SqlDataReader readerFindUserByName = commandFindUserByName.ExecuteReader();

        DropDownList1.Items.Clear();
        DropDownList1.Items.Add("Please make a selection");
        while (readerFindUserByName.Read())
            DropDownList1.Items.Add(readerFindUserByName["LastName"].ToString());

        if (DropDownList1.Items.Count == 2)
            DropDownList1.SelectedIndex = 1;
        DropDownList1_SelectedIndexChanged(null, null);

        connection.Close();
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string nameLast = DropDownList1.SelectedItem.Value;
        displayStudent(nameLast);
    }

    private void displayStudent(String nameLast)
    {
        clearStudentLabel();

        int userInt;
        bool isNum = int.TryParse(nameLast, out userInt);

        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string sqlSelectFindUserInfoByName;

        sqlSelectFindUserInfoByName = string.Format("SELECT ID, FirstName, LastName, City, Phone FROM Personal_Info WHERE LastName LIKE '%{0}%'", nameLast);

        SqlCommand commandFindUserInfo = new SqlCommand(sqlSelectFindUserInfoByName, connection);
        connection.Open();
        SqlDataReader readerFindUserInfo = commandFindUserInfo.ExecuteReader();
        int i = 0;
        while (readerFindUserInfo.Read())
        {
            Label1.Text = "Student ID: " + readerFindUserInfo["ID"].ToString();
            Label2.Text = "First name: " + readerFindUserInfo["FirstName"].ToString();
            Label3.Text = "Last name: " + readerFindUserInfo["LastName"].ToString();
            Label4.Text = "City: " + readerFindUserInfo["City"].ToString();
            Label5.Text = "Phone: " + readerFindUserInfo["Phone"].ToString();
        }
        connection.Close();


       showStudentSchedule(userInt);

    }

    private void showStudentSchedule(int id)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string sqlSelectFindUserInfoByName = string.Format("SELECT Class_Schedule.Section_ID, Class_Schedule.Course_ID, Class_Schedule.Days, Class_Schedule.Time, CASE WHEN Personal_Info.FirstName IS NULL THEN 'Staff' ELSE (Personal_Info.LastName + Personal_Info.FirstName) END AS Name FROM Class_Schedule JOIN Student_Enrollment ON Class_Schedule.Section_ID = Student_Enrollment.Section_ID JOIN Personal_Info ON Class_Schedule.Instructor_ID = Personal_Info.ID WHERE Student_Enrollment.Student_ID = {0}", id);
        SqlCommand commandFindUserInfo = new SqlCommand(sqlSelectFindUserInfoByName, connection);
        connection.Open();
        SqlDataReader readerFindUserInfo = commandFindUserInfo.ExecuteReader();
        GridView1.DataSource = readerFindUserInfo;
        GridView1.DataBind(); 
        /*
        string connectionString = "Data Source=LocalHost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa_0001";

     string commandString = "Select * from Customers";

     SqlConnection conn = new SqlConnection(connectionString);

     SqlCommand command = new SqlCommand(commandString);

     conn.Open();
     command.Connection = conn;
     SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
     GridView1.DataSource = reader;
     GridView1.DataBind(); 
         */
    }

    private void clearStudentLabel()
    {
        Label1.Text = "";
        Label2.Text = "";
        Label3.Text = "";
        Label4.Text = "";
        Label5.Text = "";
    }
}

Answer 1

Try this out:

 SqlConnection connection = new SqlConnection();
 connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 SqlCommand command = new SqlCommand(sqlSelectFindUserByName);

 connection.Open();
 command.Connection = connection;
 SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
 GridView1.DataSource = reader;
 GridView1.DataBind();