Solved

How do I create a random selection from a database?

Posted on 2008-10-12
2
213 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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
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.

762 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

13 Experts available now in Live!

Get 1:1 Help Now