Problem in randomizing selection from Access database table

Question

Learning78 0 Light Poster

14 Years Ago

Hi;
I am new to VB.net and am developing a memory quiz. The quiz contains more than 100 questions imported from access 2002 db. For the learning process, I need to run the game again and again. At the moment, I can't randomize the question order, I am getting the questions in same sequence as they are taken from db. Could nebody give me some idea, how can I pint to different tb records.
My application has a timer for maintaining question timings. I would like to know where in the function I could utilize the randomize function.
Code is

Public Function rungame()
        Try
            If t <= totq - 1 Then
                ' totq is total no of questions
                ' t is row of table
                Timer1.Enabled = True
                If con.State = ConnectionState.Open Then con.Close()
                con.Open()
                Dim adp As New OleDbDataAdapter("select * from tb2", con)
                Dim ds As New DataSet
                adp.Fill(ds)
                Dim dt As DataTable
                dt = ds.Tables(0)
                qbox.Text = dt.Rows(t).Item(1)
                PictureBox1.ImageLocation = dt.Rows(t).Item(2)
                PictureBox2.ImageLocation = dt.Rows(t).Item(3)
                PictureBox3.ImageLocation = dt.Rows(t).Item(4)
                PictureBox4.ImageLocation = dt.Rows(t).Item(5)
                t = t + 1
                con.Close()
            Else
                Timer1.Enabled = False
                qbox.Text = "Match the given TEXT with the correct IMAGE option"
                optiona.Text = "Option A"
                optionb.Text = "Option B"
                optionc.Text = "Option C"
                optiond.Text = "Option D"
                Button1.Enabled = True
                MsgBox("Thanks for Participation : " & namebox.Text      & "Your score is : " & scorebox.Text, MsgBoxStyle.Information, "Completed")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

The code for timer is

Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Static i As Integer
        i = i + 1
        If optiona.Checked = True Or optionb.Checked = True Or optionc.Checked = True Or optiond.Checked = True Then
            validans()
            GoTo ter
        End If
        timebox.Text = 30 - i
        If i >= 30 Then
ter:        Timer1.Enabled = False
            If t <= totq Then
                Label1.Text = ""
                optiona.Text = ""
                optionb.Text = ""
                optionc.Text = ""
                optiond.Text = ""
                optiona.Checked = False
                optionb.Checked = False
                optionc.Checked = False
                optiond.Checked = False
                i = 0
                rungame()
            Else
                Exit Sub
            End If
        End If
    End Sub

Below is the database table with data

Question No        Question   Image_Op1   Image_Opt2   Image_Op3 Image_Op4   Ans_Image
1	   What the hell was that  d:\Lost - 1x01_2118452.bmp	d:\Lost - 1x01_2130297.bmp	d:\Lost - 1x01_2287746.bmp	d:\Lost - 1x01_2129046.bmp	d:\Lost - 1x01_2118452.bmp
2	    I'm having contraction  d:\Lost - 1x01_291959.bmp	d:\Lost - 1x01_289957.bmp	d:\Lost - 1x01_296463.bmp	d:\Lost - 1x01_319736.bmp	d:\Lost - 1x01_289957.bmp

vb.net

Edited 14 Years Ago by Learning78 because: input wrong code

4 Contributors
11 Replies
150 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by Learning78

All 11 Replies

CodeDoctor 0 Light Poster

14 Years Ago

Hi;
I am new to VB.net and am developing a memory quiz. The quiz contains more than 100 questions imported from access 2002 db. For the learning process, I need to run the game again and again. At the moment, I can't randomize the question order, I am getting the questions in same sequence as they are taken from db. Could nebody give me some idea, how can I pint to different tb records.
My application has a timer for maintaining question timings. I would like to know where in the function I could utilize the randomize function.
Code is

Public Function rungame()
        Try
            If t <= totq - 1 Then
                ' totq is total no of questions
                ' t is row of table
                Timer1.Enabled = True
                If con.State = ConnectionState.Open Then con.Close()
                con.Open()
                Dim adp As New OleDbDataAdapter("select * from tb2", con)
                Dim ds As New DataSet
                adp.Fill(ds)
                Dim dt As DataTable
                dt = ds.Tables(0)
                qbox.Text = dt.Rows(t).Item(1)
                PictureBox1.ImageLocation = dt.Rows(t).Item(2)
                PictureBox2.ImageLocation = dt.Rows(t).Item(3)
                PictureBox3.ImageLocation = dt.Rows(t).Item(4)
                PictureBox4.ImageLocation = dt.Rows(t).Item(5)
                t = t + 1
                con.Close()
            Else
                Timer1.Enabled = False
                qbox.Text = "Match the given TEXT with the correct IMAGE option"
                optiona.Text = "Option A"
                optionb.Text = "Option B"
                optionc.Text = "Option C"
                optiond.Text = "Option D"
                Button1.Enabled = True
                MsgBox("Thanks for Participation : " & namebox.Text      & "Your score is : " & scorebox.Text, MsgBoxStyle.Information, "Completed")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

The code for timer is

Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Static i As Integer
        i = i + 1
        If optiona.Checked = True Or optionb.Checked = True Or optionc.Checked = True Or optiond.Checked = True Then
            validans()
            GoTo ter
        End If
        timebox.Text = 30 - i
        If i >= 30 Then
ter:        Timer1.Enabled = False
            If t <= totq Then
                Label1.Text = ""
                optiona.Text = ""
                optionb.Text = ""
                optionc.Text = ""
                optiond.Text = ""
                optiona.Checked = False
                optionb.Checked = False
                optionc.Checked = False
                optiond.Checked = False
                i = 0
                rungame()
            Else
                Exit Sub
            End If
        End If
    End Sub

Below is the database table with data

Question No        Question   Image_Op1   Image_Opt2   Image_Op3 Image_Op4   Ans_Image
1	   What the hell was that  d:\Lost - 1x01_2118452.bmp	d:\Lost - 1x01_2130297.bmp	d:\Lost - 1x01_2287746.bmp	d:\Lost - 1x01_2129046.bmp	d:\Lost - 1x01_2118452.bmp
2	    I'm having contraction  d:\Lost - 1x01_291959.bmp	d:\Lost - 1x01_289957.bmp	d:\Lost - 1x01_296463.bmp	d:\Lost - 1x01_319736.bmp	d:\Lost - 1x01_289957.bmp

I am going to suggest that you modify your query you are executing to the database.

Lets assume you can uniquely identify each record in your database. Each record should have a UniqueIdentifier column that is auto incrementing.

You can use the following code to query this database:

Dim iRandom as  New Random().Next(1, 100)
Dim adp As New OleDbDataAdapter("select * from tb2 where KeyColumnName = " & iRandom, con)

This will give you a single record at a time. The record will be a random number from 1 to 100.

Another way to accomplish this is to define a new OleDbDataAdapter to obtain the Minimum and Maximum values of the KeyColumnName you have in your database.

Dim adpMax as OleDBDataAdapter ("select Max(KeyColumnName) from tb2")

Dim adpMin as OleDBDataAdapter ("select Min(KeyColumnName) from tb2")

(Note: KeyColumnName is the name of your Primary Key AutoNumber field)

Hope this helps.

sknake 1,622 Senior Poster

14 Years Ago

Try this:

Public Class frmRandom
	Dim lst As List(Of Integer)

	Private Sub ResetQuestions()
		If (lst Is Nothing) Then
			lst = New List(Of Integer)
		End If
		lst.Clear()
		Dim i1 As Integer
		For i1 = 1 To 100
			lst.Add(i1)
		Next
	End Sub

	Private Function GetNextQuestion() As Integer
		If (lst.Count = 0) Then
			Throw New Exception("Out of question!")
		End If

		Dim idx As Integer = New Random().Next(0, lst.Count)
		Dim result As Integer = lst(idx)
		lst.RemoveAt(idx)
		Return result
	End Function

	Private Sub ButtonResetQuestions_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonResetQuestions.Click
		ResetQuestions()
	End Sub

	Private Sub ButtonGetQuestion_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonGetQuestion.Click
		Dim questionId = GetNextQuestion()
		'Select * From Table Where Id = QuestoinId
		Console.WriteLine(questionId)
	End Sub

	Private Sub frmRandom_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		ResetQuestions()
	End Sub
End Class

sknake 1,622 Senior Poster

14 Years Ago

Are your controls databound? Are you calling fill after you create the data adapter? Can you upload your project?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Learning78 0 Light Poster · Answer 1 · 2009-09-28T17:24:44+00:00

Code Doc;

I have used random.next() but still no randomization achieved.

Now, I have added a new column to my database with the name Flag and initially I have left it false (uncheck). I am calling flag_status() function from the rungame() function such that if flag value =False it changes to True so that next time, the same record is not slected. I have defined a counter that has initial value 0 and increment it until 50 for 50 questions to appear in the random order.
But still I am not able to stop the question duplication.

Function flag_status()

Dim adp As New OleDbDataAdapter("select * from tb1 where [Flag]=" + t.ToString(), con)
        adp.Fill(ds)
        dt = ds.Tables(0)
        If Flag = False Then
            
            Flag = True
                   
        End If
    End Function

this function is called from the rungame() function which itself is being called inside button1_click within a do loop.

Public Function rungame()       
 Try            If t <= totq - 1 Then                ' totq is total no of questions                ' t is row of table           
     Timer1.Enabled = True               
 If con.State = ConnectionState.Open Then con.Close()                con.Open()               
 Dim adp As New OleDbDataAdapter("select * from tb2", con)               
 Dim ds As New DataSet               
 adp.Fill(ds)                
Dim dt As DataTable               
 dt = ds.Tables(0)                
qbox.Text = dt.Rows(t).Item(1)               
 PictureBox1.ImageLocation = dt.Rows(t).Item(2)              
 PictureBox2.ImageLocation = dt.Rows(t).Item(3)              
 PictureBox3.ImageLocation = dt.Rows(t).Item(4)               
 PictureBox4.ImageLocation = dt.Rows(t).Item(5)
flag_status()
                If Flag = True Then
                    count = count + 1
                Else
                    count = count - 1

                End If
                con.Close()
Else
                Timer1.Enabled = False
                qbox.Text = "Match the given TEXT with the correct IMAGE option"
                optiona.Text = "Option A"
                optionb.Text = "Option B"
                optionc.Text = "Option C"
                optiond.Text = "Option D"
                Button1.Enabled = True
                
            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try
    End Function

Code for button1_click is given below, his button runs the game.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        On Error Resume Next
        t = 0
        scorebox.Text = "00"
        Dim dg1 As New Dialog1

        If dg1.ShowDialog() = Windows.Forms.DialogResult.OK Then

            namebox.Text = nme
            agebox.Text = age
            Button1.Enabled = False
            tslbl6.Text = nq
            Do Until count = 50
                rungame()
            Loop
        End If

    End Sub

Any suggestions on how to accomplish the random order of questions and bound the question to the required no. i.e. 50

Learning78 0 Light Poster · Answer 2 · 2009-09-28T17:54:44+00:00

What about the timer function? How could I achieve that with this code?

Learning78 0 Light Poster · Answer 3 · 2009-09-29T15:35:01+00:00

SKNAKE;

I tried your suggestion. I used t which is no of row (idx) and rondomize it rand.next() and used it in my rungame() function
using query

Dim adp As New OleDbDataAdapter("select * from tb1 where [idx]=" & t, con)

Evry time application is executed, a new question appears but every time I click on next question it gives the same question again.
What could be the problem here? Thanks for your suggestions.

Learning78 0 Light Poster · Answer 4 · 2009-09-29T17:35:48+00:00

yeah. I'm using fill adp after creating the oledbadapter. The code for rungame() function is already shared in the first post.

Public Function rungame()
        Try
            If t <= nq Then
                Timer1.Enabled = True
                If con.State = ConnectionState.Open Then con.Close()
                con.Open()
                Dim adp As New OleDbDataAdapter("select * from tb1 where [idx]=" & t, con)    ' idx is the field with row no.
                Dim ds As New DataSet
                adp.Fill(ds)
                dt = ds.Tables(0)
                qbox.Text = dt.Rows(0).Item(1)
                PictureBox1.ImageLocation = dt.Rows(0).Item(2)
                PictureBox2.ImageLocation = dt.Rows(0).Item(3)
                PictureBox3.ImageLocation = dt.Rows(0).Item(4)
                PictureBox4.ImageLocation = dt.Rows(0).Item(5)
                con.Close()
            Else
                Timer1.Enabled = False
                qbox.Text = "Match the given TEXT with the correct IMAGE option"
                optiona.Text = "Option A"
                optionb.Text = "Option B"
                optionc.Text = "Option C"
                optiond.Text = "Option D"
                Button1.Enabled = True
                MsgBox("Thankyou for participating in the Game : " & namebox.Text  "Your score is : " & scorebox.Text, MsgBoxStyle.Information, "Completed")


            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try
    End Function

This function is called when button1 (getquestion_click) is click.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        On Error Resume Next
        '  t = 0
        scorebox.Text = "00"
        Dim dg1 As New Dialog1
        
        If dg1.ShowDialog() = Windows.Forms.DialogResult.OK Then

            namebox.Text = nme
            agebox.Text = age
            ' Button1.Enabled = False
            tslbl6.Text = nq
            '  Do Until count = 50
            Dim questionId = GetNextQuestion()
            Console.WriteLine(questionId)
            rungame()
            '  Loop
        End If

    End Sub

I think a loop is needed here to make a count of required questions and exit the function once count becomes 50 (in case u want to display 50 questions).

Learning78 0 Light Poster · Answer 5 · 2009-09-29T19:58:14+00:00

I have used

rand().next(1,50) for 50 integer list

as well as

Rand(ByVal Low As Long, _
                          ByVal High As Long) As Long
        Rand = Int(((High - Low + 1) * Rnd()) + Low)

but for in each case, I get duplicate numbers.
Also I added (DatTime.Now.Millisecond) in rand().next() function as well as called randomize( DatTime.Now.Millisecond) function in form load but still duplicates are there.

If nebody has idea for correct random function that works perfectly, kindly share it.

sknake 1,622 Senior Poster Featured Poster · Answer 6 · 2009-09-29T20:02:31+00:00

Random is time-seeded so if you call the method twice in a close interval you will get the same value. If you need a more-random method you can use the System.Security.Cryptography.RNGCryptoServiceProvider

Geekitygeek 480 Nearly a Posting Virtuoso · Answer 7 · 2009-09-29T21:22:10+00:00

The other option is to create a single, class level instance of Random which will be seeded when the form loads. Then use this one for all subsequnet calls. As sknake mentioned, the Random class is seeded based on the current system time in ticks. By creating the instance of Random inside a method it is reseeded each time its called. If these calls occur close together the seed may not have updated resulting in the same set.

By creating a single class level instance you avoid reseeding for each call :)

Learning78 0 Light Poster · Answer 8 · 2009-09-29T23:01:43+00:00

If I create a single level instance of random in form load, then where would I call it?

rungame() function needs a randomize value every time before it is called. So should I use rand().next(1,50) within the form load? or call randomize() in form load and define a rand() public function elsewhere?

How could I upload a project file here?

Problem in randomizing selection from Access database table

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers