ASP.NET Tips & Web Development
Posts tagged record count
Returning a Record Count using VB.net
Dec 15th
When websites feature a text search and a table or list of results you will sometimes see something like:
73 records matched your search or 128 matches found.
This is actually very easy to do but incredibly hard to find an easy way of doing it. Here’s how it’s done.
This example is based on searching a company contact list.
Firstly, in your web form you need your DataSource and SelectCommand, for example:
<asp:SqlDataSource ID=”srcResults” runat=”server” ConnectionString=”<%$ ConnectionStrings:constrConnectionString %>” SelectCommand=”SELECT * FROM [contacts] WHERE [firstname] LIKE ‘%’ + txtSearch + ‘%’ OR [lastname] LIKE ‘%’ + txtSearch + ‘%’”>
Then, in your VB you get the number of records returned as follows:
Dim intMatches As Integer
Dim dv As Data.DataView
dv = CType(srcResults.Select(DataSourceSelectArguments.Empty), Data.DataView)
intMatches = dv.Count.ToString()
Dim dv As Data.DataView
dv = CType(srcResults.Select(DataSourceSelectArguments.Empty), Data.DataView)
intMatches = dv.Count.ToString()
You will need to change srcResults in the third line to the ID of your DataSource control, the rest of the code can stay as it is.
Finally, you need to output the number of records to the page. This can be done by putting a Literal or Label control in the web form:
<asp:Literal ID=”litRecordCountText” runat=”server” />
Here’s some code which gives different output depending on whether there are 0, 1 or more than 1 record counted:
If intMatches = 0 Then
litRecordCountText.Text = “<span style=’color:’cc0000′>Sorry, no contacts matched your search text.<br />Try entering just the first few letters of a name.</span>”
ElseIf intMatches = 1 Then
litRecordCountText.Text = “1 match found. The following contact matches your search.”
Else
litRecordCountText.Text = intMatches & ” matches found. The following contacts match your search.”
End If
litRecordCountText.Text = “<span style=’color:’cc0000′>Sorry, no contacts matched your search text.<br />Try entering just the first few letters of a name.</span>”
ElseIf intMatches = 1 Then
litRecordCountText.Text = “1 match found. The following contact matches your search.”
Else
litRecordCountText.Text = intMatches & ” matches found. The following contacts match your search.”
End If
(3 votes, average: 5.00 out of 5)
Loading ...Category links with record count in ASP.NET
Mar 11th
Here’s how to show a list of links with the number of records shown in brackets. You can see a working example at http://www.corporatehospitalitydirectory.com.
This is actually very simple but you can really get tied up in knots if you don’t get the SQL SelectCommand right.
You can use any of the list controls for this but here I’ve chosen the DataList control.
<asp:DataList ID=”dalCategories” runat=”server” DataSourceID=”srcCategories” RepeatColumns=”4″ RepeatDirection=”Vertical”>
<ItemTemplate>
<a href=”ShowEvents.aspx?cat=<%#Eval(“Category”)%>”>
<%#Eval(“Category”)%></a> (<%#Eval(“RecordCount”)%>)
</ItemTemplate>
</asp:DataList>
<ItemTemplate>
<a href=”ShowEvents.aspx?cat=<%#Eval(“Category”)%>”>
<%#Eval(“Category”)%></a> (<%#Eval(“RecordCount”)%>)
</ItemTemplate>
</asp:DataList>
In this example, the DataList gets its data from the srcCategories AccessDataSource control but you could equally use any of the ASP.NET 2.0+ data source controls. Within the item template the “Category” value is used in the link URL and link text and the “RecordCount” value appears after the link in brackets.
The DataList control renders an HTML table and the RepeatColumns and RepeatDirection attributes just vary the layout of this table.
<asp:AccessDataSource ID=”srcCategories” runat=”server” DataFile=”database.mdb”
SelectCommand=”SELECT DISTINCT [Category], COUNT(ID) AS RecordCount FROM [EventsTable] GROUP BY [Category] ORDER BY [Category]“>
</asp:AccessDataSource>
SelectCommand=”SELECT DISTINCT [Category], COUNT(ID) AS RecordCount FROM [EventsTable] GROUP BY [Category] ORDER BY [Category]“>
</asp:AccessDataSource>
The AccessDataSource control’s SelectCommand uses DISTINCT [Category] to show each category name only once, then a COUNT of the ID field named as RecordCount for use in the DataList control.
Works with ASP.NET 2.0 or higher.
(3 votes, average: 5.00 out of 5) Loading ...
