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'.
In a previous example (count records in a database) we used the SQL Count function in an SQL statement to count the 'ID' field. The Count function returned the number of rows without a NULL value in the specified 'ID' column. This worked as we counted the autonumber 'ID' field and we could be sure that there were no Nulls. The example below returns the 'FirstName' field to the dataset and null fields are included.
The code below relies on the Dataset, the DataAdapter, Datable and Datarow objects rather than the SQL Count function which means we don't have to worry about Nulls when counting the rows.
At the top of our page we've imported the System.Data namespace as it includes the classes needed to work with Datasets. We've also imported the System.Data.OleDb namespace so that we can work with the OleDBConnection class for accessing our database. Our row count occurs in the Page_Load event.
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ 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)
'Declare and Create our DataSet object
Dim DataSet As DataSet = New DataSet()
'Declare string variable SQL and assign SQL Statement
Dim SQL As String = "Select FirstName FROM tblCustomers"
'Create our connection object and pass in the connection string
Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
"Data Source=H:\Inetpub\wwwroot\code\Customers.mdb")
'Open the connection object
Connection.Open()
'Create OleDBDataAdapter as we'll use its fill method to fill the DataSet
'Pass in the SQL string and the Connection object
Dim DataAdapter As OleDbDataAdapter = New OleDbDataAdapter(SQL, Connection)
'Fill the DataSet and name the newly created DataTable "Customers"
DataAdapter.Fill(DataSet, "Customers")
'Declare RowCount as an Integer which stores the number of rows in our Dataset table 'Customers'
Dim RowCount As Integer = DataSet.Tables("Customers").Rows.Count
'As RowCount is an Integer use its ToString() method to bind the row count to the text property
'of the label 'Label1' control.
Label1.Text = RowCount.ToString()
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id=Head1 runat="server">
<title>Count Records with Dataset Object</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID=Label1 runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Get the best asp hosting provider from web-hosting-top.com and save up to 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
