Fun with DataLists – Twilight Zone Database

This Code Sample shows different methods of searching with DataLists – with Multiple LinkButtons (consolidated into one SubRoutine), and Dynamic searching with DropDownList and Textbox input. It also incorporates using a RequiredField Validator on the textbox search, and using CausesValidation=’false’ on the LinkButtons, so as not to cause validation when clicking the LinkButtons.

If you’d like to recreate this on your SQL Server – - you can download the sql script to recreate the table, along with the .CSV file with the data by clicking here.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Twilight Zone Seasons</TITLE>
<script language="VB" runat="server">
Dim sSeason as String
Dim sDesignation as String

Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
	dlsearchby.items.Add("Writer")
	dlsearchby.items.Add("Actor")
	dlsearchby.items.Add("Director")
	dlsearchby.items.Add("Title")
end if
End Sub

Sub GetSeason(Source as Object, E as EventArgs)
sDesignation=source.ID
	Select Case Source.ID
		Case "First"
			sSeason="1"
		Case "Second"
			sSeason="2"
		Case "Third"
			sSeason="3"
		Case "Fourth"
			sSeason="4"
		Case "Fifth"
			sSeason="5"
	End Select
	Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
	Dim MySQL as string = "Select * from Seasons Where Season = @season  Order by id"
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	cmd.Parameters.Add(New SQLParameter("@season", sSeason))
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	MyDataList.DataSource = objDR
	MyDataList.DataBind()
	MyDataList.visible="true"
	dlsearchby.selectedIndex=0
	txtSearch.text=""
	MyConn.Close
End Sub

Sub doSearch(Source as Object, E as EventArgs)
	Dim sSearchby as String
	MyDataList.visible="false"
	Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
	Dim MySQL as string = "Select * from Seasons where " & dlsearchby.selectedItem.text & _
	" Like ''%" & txtSearch.text & "%'' order by season"
	Trace.Warn("sql = " & MySQL)
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	dlSearch.DataSource = objDR
	dlSearch.DataBind()
	MyConn.Close
dlSearch.visible="true"
End Sub
</script>

<STYLE TYPE="text/css">
<!--
BODY {
	background : #000000
	color : #FFFFFF
}
.doWhite {
	color : #FFFFFF
}
A:LINK {
	color : #FFFFFF
}
A:VISITED {
	color : #FFFFFF
}
-->
</STYLE>
</head>
<BODY>

<Form id="form1" runat="server">
<div align="center">
<b><font Size="5" Color="#FFFFFF">Twilight Zone DataBase</font></b>
<hr><b><i><font Color="#FFFFFF">Select Season:</font></i></b>
<asp:LinkButton id="First"
	cssClass="doWhite"
	CausesValidation="false"
	Text="1st Season"
	onclick="GetSeason" runat="server" />
<asp:LinkButton id="Second"
	cssClass="doWhite"
	CausesValidation="false"
	Text="2nd Season"
	onclick="GetSeason" runat="server" />
<asp:LinkButton id="Third"
	cssClass="doWhite"
	CausesValidation="false"
	Text="3rd Season"
	onclick="GetSeason" runat="server" />
<asp:LinkButton id="Fourth"
	cssClass="doWhite"
	CausesValidation="false"
	Text="4th Season"
	onclick="GetSeason" runat="server" />
<asp:LinkButton id="Fifth"
	cssClass="doWhite"
	CausesValidation="false"
	Text="5th Season"
	onclick="GetSeason" runat="server" /><hr>
<b><i><font Color="#FFFFFF">Search:</font></i></b>
<asp:DropDownList id="dlsearchby" runat="server" />
<asp:TextBox id="txtSearch" runat="server" />
<asp:Button id="btnSearch" Text="Search" onclick="doSearch" runat="server" /><br>
<asp:RequiredFieldValidator ID="vldSearch"
	ControlToValidate="txtSearch"
	Display="Dynamic" runat="server">
		You must include search criteria
</asp:RequiredFieldValidator><hr>
<asp:DataList runat="server"
	Id="MyDataList"
	GridLines="None"
	cellpadding="2"
	cellspacing="2"
	Headerstyle-BackColor="#000000"
	HeaderStyle-Forecolor="White"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Size="8"
	BackColor="#000000"
	Forecolor="White"
	Font-Name="Arial"
	Font-Size="10">
<HeaderTemplate>
	<font Color="#FFFFFF">
	<b><i><font Size="4"><%# sDesignation%> Season</font></i></b>
	</font><br>
</HeaderTemplate>
	<ItemTemplate>
		<font Color="#FFFFFF"><b><%# Container.DataItem("Title")%></b><br>
		<b>Writer: </b><%# Container.DataItem("Writer")%><br>
		<b>Director: </b><%# Container.DataItem("Director")%><br>
		<b>Show Summary: </b><br>
		<%# Container.DataItem("Notes")%><br>
		<b>Cast :</b><br>
		<%# Container.DataItem("Actor")%></font><p>
		</ItemTemplate>
</ASP:DataList>

<asp:DataList runat="server"
	Id="dlSearch"
	GridLines="None"
	cellpadding="2"
	cellspacing="2"
	Headerstyle-BackColor="#000000"
	HeaderStyle-Forecolor="White"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Size="8"
	BackColor="#000000"
	Forecolor="White"
	Font-Name="Arial"
	Font-Size="10"
	Width="100%">
	<HeaderTemplate>
		<font Color="#FFFFFF">
		<b><i><font Size="4">Search Results </font></i></b>
		</font><br>
	</HeaderTemplate>
	<ItemTemplate>
		<font Color="#FFFFFF"><b>Season : </b><%# Container.DataItem("season")%><br>
		<b><%# Container.DataItem("Title")%></b><br>
		<b>Writer: </b><%# Container.DataItem("Writer")%><br>
		<b>Director: </b><%# Container.DataItem("Director")%><br>
		<b>Show Summary: </b><br>
		<%# Container.DataItem("Notes")%><br>
		<b>Cast :</b><br>
		<%# Container.DataItem("Actor")%></font><p>
	</ItemTemplate>
</ASP:DataList>
<b><i><font Color="#FFFFFF"><asp:Label ID="label1"  runat="server" /></font></i></b></div>
</Form>
</body>
</html>

ASP.NET 101

ASP.NET and MVC Tutorials

Fun with DataLists – Twilight Zone Database

Leave a Reply Cancel reply