asked on

How do I create a random selection from a datatable?

I'm creating a Jeopardy style game. I've pulled all the categories into a datatable from the database, but what I would like to do is randomize the categories so the same ones are not displayed everytime a game is started but I'm not sure how to go about it.
~TIA

Public Class frmBoard
    Inherits System.Windows.Forms.Form
 
    Private m_Categories As DataTable
    Private m_Question As String
 
    Dim frmQ As New frmQuestion
    Dim oboard As New ClsBoard()
    Dim vwQuestion As New DataView(oboard.GetQuestion())
 
 
    Dim dtQuestions As DataTable
    Dim arrCol1(4) As Button
    Dim arrCol2(4) As Button
    Dim arrCol3(4) As Button
    Dim arrCol4(4) As Button
    Dim arrCol5(4) As Button
    Dim arrCol6(4) As Button
 
    Private Sub frmBoard_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim oBoard As New ClsBoard
        Dim dtCategories As New DataTable
 
        'hook up array
        arrCol1(0) = btn1200
        arrCol1(1) = btn1400
        arrCol1(2) = btn1600
        arrCol1(3) = btn1800
        arrCol1(4) = btn11000
 
        arrCol2(0) = btn2200
        arrCol2(1) = btn2400
        arrCol2(2) = btn2600
        arrCol2(3) = btn2800
        arrCol2(4) = btn21000
 
        arrCol3(0) = btn3200
        arrCol3(1) = btn3400
        arrCol3(2) = btn3600
        arrCol3(3) = btn3800
        arrCol3(4) = btn31000
 
        arrCol4(0) = btn4200
        arrCol4(1) = btn4400
        arrCol4(2) = btn4600
        arrCol4(3) = btn4800
        arrCol4(4) = btn41000
 
        arrCol5(0) = btn5200
        arrCol5(1) = btn5400
        arrCol5(2) = btn5600
        arrCol5(3) = btn5800
        arrCol5(4) = btn51000
 
        arrCol6(0) = btn6200
        arrCol6(1) = btn6400
        arrCol6(2) = btn6600
        arrCol6(3) = btn6800
        arrCol6(4) = btn61000
 
        dtCategories = oBoard.GetCategories
        txtCat1.Text = dtCategories.Rows(0).Item(1)
        txtCat2.Text = dtCategories.Rows(1).Item(1)
        txtCat3.Text = dtCategories.Rows(2).Item(1)
        txtCat4.Text = dtCategories.Rows(3).Item(1)
        txtCat5.Text = dtCategories.Rows(4).Item(1)
        txtCat6.Text = dtCategories.Rows(5).Item(1)
        For x = 0 To 4
            arrCol1(x).Text = ("$" & (x + 1) * 200)
            arrCol2(x).Text = ("$" & (x + 1) * 200)
            arrCol3(x).Text = ("$" & (x + 1) * 200)
            arrCol4(x).Text = ("$" & (x + 1) * 200)
            arrCol5(x).Text = ("$" & (x + 1) * 200)
            arrCol6(x).Text = ("$" & (x + 1) * 200)
 
        Next
        m_Categories = dtCategories
        Me.Show()
    End Sub
 
    Private Sub btnCol1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1200.Click, btn1400.Click, btn1600.Click, btn1800.Click, btn11000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
        'hook up array
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub
 
    Private Sub btnCol2_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2200.Click, btn2400.Click, btn2600.Click, btn2800.Click, btn21000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
 
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub
 
    Private Sub btnCol3_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn3200.Click, btn3400.Click, btn3600.Click, btn3800.Click, btn31000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
 
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub
 
    Private Sub btnCol4_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn4200.Click, btn4400.Click, btn4600.Click, btn4800.Click, btn41000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
 
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub
 
    Private Sub btnCol5_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn5200.Click, btn5400.Click, btn5600.Click, btn5800.Click, btn51000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
 
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub
 
    Private Sub btnCol6_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn6200.Click, btn6400.Click, btn6600.Click, btn6800.Click, btn61000.Click
        Dim icolumn As Integer
        Dim irow As Integer
        icolumn = Strings.Left(sender.tag, 1) - 1
        irow = Strings.Right(sender.tag, 1) - 1
 
        Dim frmQ As New frmQuestion
        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"
        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)
        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)
        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)
        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)
        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)
        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)
        frmQ.ShowDialog()
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Binuth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

98fatboyrider

ASKER

Thanks Binuth. What I'd like to happen is each time the game is started, it will pull six random categories, flag them as having been used, and then load the categories on the form. I have a field in the category Access database for keeping track if they have been used. Currently all categories are set to 'N' for no. Does this help you at all?