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
98114Can I send emails without using CDONTS?130322
1832115I 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
2313118When 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
4723190How do I display data on a web page using arrays instead of Do...While...MoveNext...???...186006
5331161How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?238089
5748181How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency.964702
5801106How can I find out if a record already exists in a database? If it doesn't, I want to add it.183511
581983How can I quickly sort a VBScript array? 230033
710530For session variables to work, must the Web visitor have cookies enabled?133635
8284149How can I remove multiple spaces between words in a string? That is, if I have: Hi    there how can I get: Hi there?129557

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]