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>