ASP.NET Tips & Web Development
SelectCommand
Showing only present and future dates using SelectCommand in ASP.NET
Mar 11th
Where you have a database of events you can easily exclude those which have gone by leaving only current and future events. However, getting the SelectCommand syntax right is not obvious.
SelectCommand=”SELECT [EventName], [EventDate] FROM [EventsTable] WHERE DateValue([EventDate]) >= DATE() ORDER BY [EventDate]“
To show only current and future events add DateValue([EventDate]) >= DATE() to the WHERE clause of your data source SelectCommand. DateValue grabs the date value of your date field, DATE() returns today’s date.
This can be varied to show dates in the past by changing the operator from “>=” to “<” or past and present “<=”.
Works with ASP.NET 2.0 or higher.
(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 ...
