ASP.NET Tips & Web Development
SQL
Selecting a random database record with SQL
Apr 9th
Here’s a “quick and dirty” way of selecting a database record at random. This can be used for things like showing a featured product or a random advert.
Here’s the SQL to use in your SelectCommand:
SELECT TOP 1 * FROM table ORDER BY NEWID()
You can either bind a control such as a Repeater to the data source or use VB to pull data from the record and use it. Here’s an example of producing a random link in a page. ASP.NET form:
<asp:HyperLink ID=”lnkRandomProduct” runat=”server” />
And here’s the VB to grab the data for the single record:
Dim dv As Data.DataView
dv = CType(srcRandomCourse.Select(DataSourceSelectArguments.Empty), Data.DataView)
Dim dr As Data.DataRowView = dv(0)
Dim strProductName As String = CStr(dr(“product_name”))
Dim strProductId As String = CStr(dr(“product_id”))
dv = CType(srcRandomCourse.Select(DataSourceSelectArguments.Empty), Data.DataView)
Dim dr As Data.DataRowView = dv(0)
Dim strProductName As String = CStr(dr(“product_name”))
Dim strProductId As String = CStr(dr(“product_id”))
lnkRandomProduct.Text = strProductName
lnkRandomProduct.NavigateUrl = “http://www.yoursite.com/products.aspx?id=” & strProductId
(3 votes, average: 5.00 out of 5)
Loading ...Confirming delete actions in ASP.NET
Mar 19th
When working with databases and the SQL DeleteCommand it may be a good idea to double check that this is what the user intended to do before risking permanently losing data. This is normally in the form of a pop up confirmation message with options of OK or Cancel.
It’s very easy to implement. Here’s how it’s done. This example has a delete button in a GridView control.
<asp:GridView ID=”grdContactNames” runat=”server” DataSourceID=”srcContacts” AutoGenerateColumns=”false” DataKeyNames=”ID”>
<Columns>
<asp:BoundField DataField=”ID” HeaderText=”ID” />
<asp:BoundField DataField=”FirstName” HeaderText=”First Name” />
<asp:BoundField DataField=”LastName” HeaderText=”Last Name” />
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID=”btnDelete” runat=”server” Text=”Delete” CommandName=”Delete” OnClientClick=”return confirm(‘Are you sure you want to delete this contact? Click OK to delete, Cancel to stop.’);” />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<Columns>
<asp:BoundField DataField=”ID” HeaderText=”ID” />
<asp:BoundField DataField=”FirstName” HeaderText=”First Name” />
<asp:BoundField DataField=”LastName” HeaderText=”Last Name” />
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID=”btnDelete” runat=”server” Text=”Delete” CommandName=”Delete” OnClientClick=”return confirm(‘Are you sure you want to delete this contact? Click OK to delete, Cancel to stop.’);” />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Instead of using a ButtonField as you might expect we use a TemplateField which allows us to add the OnClientClick property. You can then add your own confirmation message. The data record will only be deleted on confirming by clicking OK. No changes to the data source control are needed.
(3 votes, average: 5.00 out of 5) Loading ...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 ...
