Using Record Count to Test for Empty Results
There is another code sample on this site to test an empty DataReader before displaying results (Check for Empty), however, this is an alternative which will ONLY work, apparently with SQL Server. It uses two SQL statements, separated by a semi-colon to get two result sets. Then, it uses the NextResult and GetValue methods to accomplish the goal of checking for the empty DataReader, before displaying results, by checking for a RowCount first. If the number of records returned is larger than ’0′, then it’s displayed. Otherwise, a message is displayed “Sorry – no records exist with the last name of …
Thanks go to Bipin Joshi for the inspiration for this code sample. I saw his record count article and realized this could be used as an Empty DataReader check also.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.2">
<title>Using Record Count to Test for Empty Results</title>
<script language="VB" runat="server">
Dim strConn as String="server=YourServer;uid=YourUID;pwd=YourPwd;database=Northwind"
Sub GetRecord(Source as Object, E as EventArgs)
ph1.visible="true"
label1.text=""
Dim MySQL as string = "Select Count(*) from Employees Where LastName = " & _
"@LName;Select FirstName, LastName, Title, Address, City, Region, PostalCode, " & _
"HomePhone from Employees Where LastName = @LName"
Dim MyConn As SqlConnection = New SqlConnection(strConn)
MyConn.Open()
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
cmd.Parameters.Add(New SQLParameter("@LName", text1.text))
cmd.Connection=MyConn
objDR=Cmd.ExecuteReader
objDR.Read()
Dim intVal as Integer
intVal=objDR.GetValue(0)
label1.text=intVal & " Record Matched your query"
if intVal >0 then
MyDataGrid.Visible="true"
objDR.NextResult()
MyDataGrid.DataSource = objDR
MyDataGrid.DataBind()
else
MyDataGrid.Visible="false"
label1.text="Sorry - no records exist with the last name of " & text1.text
End If
MyConn.Close
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
Search Employees Here: (Use Last Name)
<asp:TextBox
id="text1"
runat="server" />
<asp:Button
id="button1"
Text="Get Employee Details"
onclick="GetRecord" runat="server" /><br>
<i><b><font Color="#FF0000" size="2">(Hint - Try ''Davolio'', ''Fuller'', ''King'', ''Peacock'' for valid names) </font></b></i>
<asp:placeholder ID="ph1" runat="server" Visible="false">
<hr>
<asp:Datagrid runat="server"
Id="MyDataGrid"
GridLines="Both"
cellpadding="2"
Headerstyle-BackColor="#8080C0"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="14"
Headerstyle-Font-Bold="true"
BackColor="#8080FF"
Font-Name="Arial"
Font-Size="11"
BorderColor="Black">
</asp:DataGrid>
<asp:Label ID="label1" runat="server" />
</asp:placeholder>
</Form>
</body>
</html>