Hi all,
i'm struggling with a page idea I have.
I need to write a page with an A to Z list of available schools in the area.
I only want to display the letters in the A to Z which have school
information under them, i.e. A,b,c,d,f if no schools beginning with e are
available.
My chosen page layout out would be
A
+Name of School
+Name of School
B
+Name of School
-Name Of School
School Details
+Name of School
C
+Name Of School
E
+Name of School
I know I need to use Datalists for the drill down part of the page but I am
struggling with getting my list of schools displayed under the letters. Here
is what I've got so far:
Stored Procedure
ELECT DISTINCT(LEFT(SCH_NAME,1)) AS Letter
FROM db
ORDER BY Letter
SELECT DISTINCT(LEFT(SCH_NAME,1)) As Letter, SCH_DFEE, SCH_NAME
FROM db
SELECT SCH_DFEE, ADR_NUMBER, ADR_STREET, ADR_LOCALITY, ADR_TOWN, ADR_COUNTY,
ADR_POSTCODE, SCH_TEL, SCH_FAX, SCH_HEAD
FROM db
DataLayer.cs
public void SchoolsAtoZ(string conn_str)
{
SqlConnection conn = new SqlConnection(conn_str);
SqlDataAdapter daSchoolAtoZ = new SqlDataAdapter("EducationView_AtoZ",
conn);
dsEducation = new DataSet();
try
{
daSchoolAtoZ.SelectCommand.CommandType = CommandType.StoredProcedure;
daSchoolAtoZ.Fill(dsEducation);
dsEducation.Relations.Add("AtoZName",
dsEducation.Tables[0].Columns["Letter"],
dsEducation.Tables[1].Columns["Letter"]);
dsEducation.Relations.Add("NameAndDetails",
dsEducation.Tables[1].Columns["SCH_DFEE"],
dsEducation.Tables[2].Columns["SCH_DFEE"]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (!(conn==null))
{
conn.Close();
}
}
}
A To Z Page (aspx)
<%@ Page language="c#" Codebehind="STSchoolsAtoZ.aspx.cs"
AutoEventWireup="false" Inherits="STEducationPages.STSchoolsAtoZ" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>STSchoolsAtoZ</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:DataList id="dlSchoolsAtoZ" runat="server"
RepeatDirection="Vertical" RepeatColumns="1" Width="100%"
CellPadding="0" CellSpacing="0" >
<HeaderTemplate>
Schools A To Z
</HeaderTemplate>
<ItemTemplate>
<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tr>
<td valign="top">
<asp:Label ID="Letter" Runat="server" text='<%#
DataBinder.Eval(Container.DataItem, "Letter") %>' />
</td>
<td>
<asp:LinkButton ID="button1" Runat="server" CommandName="select">
<img src="images\webplus.gif" border="0" alt="Click plus to expand
details">
</asp:LinkButton>-->
<asp:Label ID="SCH_DFEE" Runat="server" text='<%#
DataBinder.Eval(Container.DataItem, "SCH_DFEE") %>' Visible="False" />
<asp:Label ID="SCH_NAME" Runat="server" text='<%#
DataBinder.Eval(Container.DataItem, "SCH_NAME") %>' />
</td>
</tr>
</table>
</ItemTemplate>
<SelectedItemTemplate>
<table border="0" cellpadding="2" cellspacing="0" width="100%">
<tr>
<td valign="top" width="100%" bgcolor="#66ffcc">
<asp:LinkButton ID="button2" Runat="server"
CommandName="showDetails">
<img src="images\webplus.gif" border="0" alt="Click plus to expand
details">
</asp:LinkButton>
<%# DataBinder.Eval(Container.DataItem, "SCH_NAME") %>
</td>
</tr>
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">
</td>
<td>
<asp:DataList ID="DetailsList" Runat="server" DataSource='<%#
GetChildRelation(Container.DataItem, "NameAndDetails") %>'
RepeatDirection="Vertical" RepeatColumns="1" Width="100%" CellPadding="0"
CellSpacing="0">
<HeaderTemplate>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="100">Line1</td>
<td width="200">Line2</td>
</tr>
</table>
</HeaderTemplate>
<SeparatorTemplate>
--------------------------------------------------------------------------------
</SeparatorTemplate>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td width="100">
<asp:Label ID="LABEL2" Runat="server" text='<%#
DataBinder.Eval(Container.DataItem, "ADR_NUMBER") %>' />
</td>
<td width="200">
<asp:Label ID="LABEL6" Runat="server" text='<%#
DataBinder.Eval(Container.DataItem, "ADR_STREET") %>' />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</td>
</tr>
</table>
</td>
</tr>
</table>
</SelectedItemTemplate>
</asp:DataList>
</form>
</body>
</HTML>
A to Z Page (Code Behind)
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace STEducationPages
{
/// <summary>
/// Summary description for STSchoolsAtoZ.
/// </summary>
public class STSchoolsAtoZ : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataList dlSchoolsAtoZ;
private const string _DFEE = "DFEE";
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
GetSchoolsData();
}
private void GetSchoolsData()
{
STEducationDataLayer.EducationDataLayer objLayer = new
STEducationDataLayer.EducationDataLayer();
objLayer.SchoolsAtoZ(ConfigurationSettings.AppSett ings["STEducation"]);
dlSchoolsAtoZ.DataSource = objLayer.dsEducation.Tables[0];
dlSchoolsAtoZ.DataBind();
}
protected void dlSchoolsAtoZ_ItemCommand(object Sender,
DataListCommandEventArgs e)
{
//Change the datalist to selected index
string cmd = ((LinkButton)e.CommandSource).CommandName;
if (cmd == "select")
((DataList)Sender).SelectedIndex = e.Item.ItemIndex;
GetSchoolsData();
ViewState[_DFEE] = ((Label)e.Item.FindControl("DFEE")).Text;
}
protected DataView GetChildRelation(object dataItem, string relation)
{
DataRowView drv = dataItem as DataRowView;
if (drv != null)
return drv.CreateChildView(relation);
else
return null;
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
Hope this makes sense. Sorry about the post being so long.
Any help is appreciated.
Thanks
Andrew
