Sometimes, we’d like to know if there are actually records to be returned in our query. If there are no records, then, we could display a message accordingly. Otherwise, we can then display the record(s) as needed.
Using two reads of a DataReader, the following Code Sample demonstrates how to do this, using the Northwind Database (SQL Server)
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.1">
<title>DataReader Record Check</title>
<script language="VB" runat="server">
Dim strName as string
Sub searchit(Source as Object, E as EventArgs)
strName=text1.text
Dim strConn as string = "server=(local);uid=sa;pwd=PASSWORD;database=Northwind"
Dim sql as string = "Select FirstName, LastName, Title from NWEmployees where lastname=’" & strName & "’"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
Dim objDR as SQLDataReader
conn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if Not objDR.Read() then
Label1.text = "There are no employees with the name – " & strName & "."
objDR.Close
conn.Close
myDataList.visible="false"
label1.visible="true"
else
objDR.close
conn.Open
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyDataList.datasource=objDR
MyDataList.Databind
myDataList.visible="true"
label1.visible="false"
End If
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
Enter Last Name: <asp:TextBox id="text1" runat="server" /> <asp:Button id="button1" Text="Search" onclick="searchit" runat="server" /><br>
<font Size="2"><i>(Hint – Try ‘Davolio’, ‘Fuller’, ‘King’, ‘Peacock’ for valid names)</i></font>
<asp:DataList runat="server"
Id="MyDataList"
GridLines="Both"
cellpadding="2"
cellspacing="2"
Font-Name="Arial"
Font-Bold="false"
Font-Size="12"
Visible="false">
<ItemTemplate>
<b>Name: </b><%# Container.DataItem("FirstName")%>
<%# Container.DataItem("LastName")%><br>
<b>Title: </b><%# Container.DataItem("Title")%>
</ItemTemplate>
</ASP:DataList><br><asp:Label ID="label1" forecolor="red" font-Italic="true" runat="server" />
</Form>
</body>
</html>