Solved

How do I create a random selection from a database?

Posted on 2008-10-12
2
214 Views
Last Modified: 2012-05-05
I'm creating a Jeopardy style game. I've pulled all the categories from the database into a datatable, 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. I believe I would also need to change the flag in datatable from 'N' for no it hasn't all ready been used to 'Y' it has so the category is not selected in the random selection.
~TIA
Public Function GetCategories() As DataTable

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblCategory where Used = 'N'"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetCategories = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

        Catch ex As Exception

            Throw ex

        End Try

    End Function
 

    Public Function GetQuestion() As DataTable

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblQuestion"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetQuestion = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

        Catch ex As Exception

            Throw ex

        End Try

    End Function
 

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

       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)
 

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
 

        m_sender = Strings.Right(sender.tag, 1)

        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)

'Sorry for the lengthy code

Open in new window

0
Comment
Question by:98fatboyrider
2 Comments
 
LVL 13

Accepted Solution

by:
TechTiger007 earned 500 total points
ID: 22698379
Fill your datatable with rows from database for questions which are not asked
Create a dataview over this datatable with filter condition as "N"

Random obj = new Random();
DataRow question = dv.Rows[obj.Next(dv.Rows.Count)];
question["status"] = "Y"

update the datatable back to database


0
 
LVL 23

Expert Comment

by:Tiggerito
ID: 22701967
If you want to get a single random question from the database the syntax may vary depending the database. This should work for mySql

Select * from tblQuestion order by rand() limit 1
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 47
VB.net/WinForms: adjust Screen Resolution 6 40
How do ASP.NET and MVC work together? 4 25
Error in page 3 45
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now