The following code counts the number of records in a Microsoft Access database 'Customers'. The table 'tblCustomers' has a primary key autonumber field 'ID', and two textfields 'FirstName' and 'LastName'. The database is located in the folder 'H:\Inetpub\wwwroot\code\Customers.mdb'.
We use the SQL Count function in our SQL command to count the 'ID' field. The Count function returns the number of rows without a NULL value in the specified column. As we will be counting the autonumber 'ID' field we can be sure that there are no Nulls.
We've imported the System.Data.OleDb name space at the top of our page so that we can work with the OleDBConnection and OleDBCommand classes for accessing our database. Our count occurs in the Page_Load event.
After creating a connection object and passing in a connection string we then create our command object and pass in our SQL Count statement as well as our connection object. We then simply call the ExecuteScalar method of our Command object and return the count. The ExecuteScalar method executes the query, and returns the first column of the first row in the result set returned by the query, which in this case will be the record count.
ExecuteScalar returns an object variable so we need to cast it to a string and assign the value to our new string 'RecordCount'. After that we simply set the text property of our label 'Label1' to the value of 'RecordCount'.
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.Oledb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
Dim Command As New OleDbCommand("Select Count(ID) FROM tblCustomers", Connection)
Dim RecordCount As String = CType(Command.ExecuteScalar(), String)
Label1.Text = RecordCount
<head id=Head1 runat="server">
<title>Count Records in ASP.NET</title>
<form id="form1" runat="server">
<asp:Label ID=Label1 runat="server" Text="Label"></asp:Label>
Learn how to Count the number of records in a Dataset and even those Null fields.