What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleSelect random records from a database table in Visual Basic .NET
DescriptionThis example shows how to select random records from a database table in Visual Basic .NET.
Keywordsdatabase, random records, select records, VB.NET
CategoriesVB.NET, Database
Note that this program assumes that the table contains an Index field that numbers its records starting with 0.

When you click the Go button, the program calls function GetDbConnection to open a database connection. The program then executes a query to see how many records are in the table.

Next the program makes an array to hold the indexes that it selects. It makes a SortedList to keep track of the selected indexes in numeric order. It then enters a loop to select the indexes.

For each index, the program picks a random number between 0 and the number of records that have not yet been selected. For example, if the table contains 10 records then it picks between 0 and 9, 0 and 8, 0 and 7, and so forth.

The code then loops through the previously selected indexes from smallest to biggest. Each time it finds an index <= the newly selected index, it increments the newly selected index.

For example, suppose the program has already selected indexes 2, 4, and 6, and now selects index 3. To ensure that it selects from all of the unpicked indexes, it must increment the new index for 2 (new value = 4), and 4 (new value = 5).

Now the program loops through the selected records, selecting each from the table by using its index and displaying it in the ListBox. (If someone knows how to select the i-th record from the table without using an index field and without looping through the entire table, let me know.)

Private Sub btnGo_Click(ByVal sender As System.Object, _
    ByVal e As
System.EventArgs) Handles btnGo.Click
    ' Open the database.
    Dim conn As OleDbConnection = GetDbConnection()

    ' See how many records there are.
    Dim query As String = "SELECT COUNT(*) FROM Books"
    Dim cmd As New OleDbCommand(query, conn)
    Dim num_records As Integer = cmd.ExecuteScalar()

    ' Make an array to hold the selected indexes.
    Dim num_random As Integer = Integer.Parse(txtNum.Text)
    Dim indexes(num_random - 1) As Integer
    Dim sorted_indexes As New SortedList(num_random - 1)

    ' Generate the indexes.
    Dim rand As New Random
    For i As Integer = 0 To num_random - 1
        ' Generate the i-th index.
        Dim new_value As Integer = rand.Next(0, num_records _
            - i)

        ' For each previously generated index <=
        ' than this one, increment this one.
        For j As Integer = 0 To i - 1
            If sorted_indexes.GetByIndex(j) _
                <= new_value _
                new_value += 1
            End If
        Next j

        ' Save tjhe new value.
        indexes(i) = new_value
        sorted_indexes.Add(new_value, new_value)
    Next i

    ' Fetch and display the selected records.
    query = "SELECT * FROM Books " & _
        "WHERE Index=?"
    cmd = New OleDbCommand(query, conn)
    For i As Integer = 0 To num_random - 1
        ' Fetch record i.
        Dim index As Integer = indexes(i)
        cmd.Parameters.Add(New OleDbParameter("Index", _

        Dim reader As OleDbDataReader = _

        lstSelected.Items.Add( _
            index & ": " & reader.Item("Title"))
    Next i

    ' Close the connection.
End Sub
Function GetDbConnection locates the database in the program's code directory. It creates a connection to the database, opens it,and returns it.
' Open the database.
Private Function GetDbConnection() As OleDbConnection
    ' Compose the database file name.
    ' Modify this if the database is somewhere else.
    Dim database_name As String = Application.StartupPath()
    database_name = database_name.Substring(0, _
    database_name = database_name & "\RandomRecords.mdb"

    ' Compose the connect string.
    Dim connect_string As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & database_name

    ' Open a database connection.
    Dim conn As New OleDbConnection(connect_string)

    ' Return the connection.
    Return conn
End Function
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.