title: Binding sqldatareader to gridview c#
body: <p>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.</p>
  
  <p>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().</p>
  
  <p>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.</p>
  
  <p>//StudentInformation.aspx</p>
  
  <pre><code>&lt;%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="StudentInformation.aspx.cs" Inherits="StudentInformation"  %&gt;
  
  &lt;asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"&gt;
  &lt;/asp:Content&gt;
  &lt;asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"&gt;
      &lt;p&gt;
          &lt;asp:Label ID="Label6" runat="server" Text="Search by Last Name: "&gt;&lt;/asp:Label&gt;
          &lt;asp:TextBox ID="TextBox1" runat="server"&gt;&lt;/asp:TextBox&gt;
          &lt;asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" /&gt;
          &lt;asp:DropDownList ID="DropDownList1" runat="server" 
              onselectedindexchanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True"&gt;
          &lt;/asp:DropDownList&gt;
      &lt;/p&gt;
  
      &lt;asp:Label ID="Label1" runat="server"&gt;&lt;/asp:Label&gt;
      &lt;br /&gt;
      &lt;asp:Label ID="Label2" runat="server"&gt;&lt;/asp:Label&gt;
      &lt;br /&gt;
      &lt;asp:Label ID="Label3" runat="server"&gt;&lt;/asp:Label&gt;
      &lt;br /&gt;
      &lt;asp:Label ID="Label4" runat="server"&gt;&lt;/asp:Label&gt;
      &lt;br /&gt;
      &lt;asp:Label ID="Label5" runat="server"&gt;&lt;/asp:Label&gt;
      &lt;asp:Panel ID="Panel1" runat="server"&gt;
          &lt;asp:GridView ID="GridView1" runat="server"&gt;
          &lt;/asp:GridView&gt;
      &lt;/asp:Panel&gt;
  &lt;/asp:Content&gt;
  </code></pre>
  
  <p>//StudentInformation.aspx.cs</p>
  
  <pre><code>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 = "";
      }
  }
  </code></pre>