Demo of Displaying a Set of Records in Random Order

This demo illustrates how to display a set of records in random order in a DataGrid when you do not have access to change the SQL statement itself. This technique loads the database data into a DataSet, and then adds a new column to the DataTable that stores a random number. Then, a DataView is created and sorted by this random number column. Finally, it is this DataView that is bound to the DataGrid's DataSource. (To see the random display, continually refresh the page.)


Random NumberFAQ IDQuestionViews
110030For session variables to work, must the Web visitor have cookies enabled?133635
167783How can I quickly sort a VBScript array? 230033
193114Can I send emails without using CDONTS?130322
1941190How do I display data on a web page using arrays instead of Do...While...MoveNext...???...186006
2681149How can I remove multiple spaces between words in a string? That is, if I have: Hi    there how can I get: Hi there?129557
5872161How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?238089
6062181How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency.964702
7300115I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem?233530
8623106How can I find out if a record already exists in a database? If it doesn't, I want to add it.183511
9093118When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... 168750


Source Code
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub
	
	
  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "sp_Popularity"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to a DataSet
    Dim myDataAdapter as New SqlDataAdapter(myCommand)    
    Dim myDataSet As New DataSet()
    myDataAdapter.Fill(myDataSet, "FAQs")
    myConnection.Close()
    
    ' Now, randomly order the DataTable's Rows Collection
    Dim myDataTable as DataTable = myDataSet.Tables(0)
    myDataTable.Columns.Add(New DataColumn("RandNum", GetType(Integer)))
    
    Dim i as Integer
    Dim rndNum as New Random()
    For i = 0 to myDataTable.Rows.Count - 1
		myDataTable.Rows(i)("RandNum") = rndNum.Next(10000)
    Next i
    
    Dim myDataView as DataView = myDataTable.DefaultView
    myDataView.Sort = "RandNum"
        
    dgRandOrder.DataSource = myDataView
    dgRandOrder.DataBind()    
  End Sub
</script>

  <asp:DataGrid runat="server" id="dgRandOrder"
         AutoGenerateColumns="False"
         Font-Name="Verdana" Width="85%"
         Font-Size="11pt" HorizontalAlign="Center">
         
    <HeaderStyle BackColor="Navy" ForeColor="White" HorizontalAlign="Center"
                 Font-Size="14pt" Font-Bold="True" />
   
    <Columns>
	  <asp:BoundColumn DataField="RandNum" HeaderText="Random Number" />
      <asp:BoundColumn DataField="FAQID" HeaderText="FAQ ID" />
      <asp:BoundColumn DataField="Description" HeaderText="Question" />
      <asp:BoundColumn DataField="ViewCount" HeaderText="Views" />
    </Columns>
  </asp:DataGrid>


[Return to the article]