Set dr.Close() and con.Close(); below the end bracket }. Now after 1 row you close the connection while trying to get a second row.
Furthermore all Columns get the same headertext. dr[2] should be dr[1] (start counting with 0 (c-principle))
And read about learning to use USING
using (SqlConnection con = ...)
{
con.Open();
using (SqlCommand cmd = new SqlCommand("select SNO, Faculty_Name from Tb_SCH_Faculty_Details", con))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
datagridView.Columns[2].HeaderText = dr[2].ToString().Trim();
datagridView.Columns[3].HeaderText = dr[2].ToString().Trim();
datagridView.Columns[4].HeaderText = dr[2].ToString().Trim();
datagridView.Columns[5].HeaderText = dr[2].ToString().Trim();
datagridView.Columns[6].HeaderText = dr[2].ToString().Trim();
}
}
dr.Close();
}
con.Close();
}