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 = "";
}
}
