Count Records in Microsoft Access Database

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">

<script runat="server">

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

    Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
    "Data Source=H:\Inetpub\wwwroot\code\Customers.mdb")
    Connection.Open()
    Dim Command As New OleDbCommand("Select Count(ID) FROM tblCustomers", Connection)

    Dim RecordCount As String = CType(Command.ExecuteScalar(), String)

    Connection.Close()
    Label1.Text = RecordCount

End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id=Head1 runat="server">
<title>Count Records in ASP.NET</title>
</head>

<body>
<form id="form1" runat="server">
<div>
<asp:Label ID=Label1 runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>

Learn how to Count the number of records in a Dataset and even those Null fields.

Get the best ASP hosting with DiscountASP.NET - great value, money back guarantee.

Advertisements



MembersPro

MembersPro PayPal - ASP Membership software

Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.

Global ASP.NET Hosting Leader - Click Here