ASP.NET Tips & Web Development
DataView
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 ...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 ...Setting the page title dynamically in ASP.NET
Mar 12th
With a database driven site it’s very useful if pages giving details of a specific product have that product name in the page title.
This sounds like it should be quite easy but it isn’t. You can insert data from a field into a page quite easily by binding the data to a control. However, the ASP.NET controls have to sit with the <form> tags in the <body> of the HTML so binding data to the <head> section is more difficult.
It has to be done programmatically. Here how’s it’s done in VB.
Dim dv As Data.DataView
dv = CType(srcDataSourceControl.Select(DataSourceSelectArguments.Empty), Data.DataView)
Dim dr As Data.DataRowView = dv(0)
Dim strProductName As String = CStr(dr(“ProductName”))
Dim strProductNumber As String = CStr(dr(“ProductNumber”))
Dim strProductCategory As String = CStr(dr(“ProductCategory”))
Page.Header.Title = strProductName & ” – ” & strProductNumber & ” – ” & strProductCategory
dv = CType(srcDataSourceControl.Select(DataSourceSelectArguments.Empty), Data.DataView)
Dim dr As Data.DataRowView = dv(0)
Dim strProductName As String = CStr(dr(“ProductName”))
Dim strProductNumber As String = CStr(dr(“ProductNumber”))
Dim strProductCategory As String = CStr(dr(“ProductCategory”))
Page.Header.Title = strProductName & ” – ” & strProductNumber & ” – ” & strProductCategory
This example uses srcDataSourceControl as the data source control and assumes that the database table has fields called ProductName, ProductNumber and ProductCategory. You can bind as many fields as you like in this way.
The title is set by giving a string value to Page.Header.Title. This can be strings bound with DataView (strProductName) or bits of text in quote marks (” – “) joined by the & sign.
One final important thing. The <head> section in your page HTML must include the runat=”server” to enable the header content to be manipulated on the server side.
<head runat=”server”>
(3 votes, average: 5.00 out of 5) Loading ...
