There are a lot of ways to grab the number of records returned in a dataset. This is one of my useful ones. Here is a working demo.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Private Sub Page_Load()
lblMovieCount.Text = GetMovieCount().ToString()
End Sub
Private Function GetMovieCount() As Integer
Dim result As Integer = 0 Dim
con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim cmd As New SqlCommand("GetMovieCount", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.Output
Using con
con.Open()
cmd.ExecuteNonQuery()
result = CType(cmd.Parameters("@ReturnVal").Value, Integer)
End Using
Return result
End Function
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Movie Count</title>
</head>
<body>
<form id="form1" runat="server">
<div>
There are
<asp:Label
id="lblMovieCount"
Runat="server" />
movies in the database.
</div>
</form>
</body>
</html>
GetMovieCount.sql
CREATE PROCEDURE dbo.GetMovieCount
-- OUTPUT parameter to hold the count.
@ReturnVal int OUTPUT
AS
-- This will set @recordcount the the number of records returned by the SELECT query.
Set @ReturnVal = (SELECT COUNT(*) FROM Movies)