Random Question

Posted on 2002-07-09
Last Modified: 2010-05-02
I am accessing a Test Question Database application thru VB6. The database has a table with 80 questions in it. I would like for someone to give me an idea on how to generate the questions randomly from a recordset populated from the table.
Question by:global819
  • 2
  • 2
  • 2
  • +7
LVL 43

Expert Comment

ID: 7140598
Well you have a few options, you can either retrieve the entire set of questions and then use the Rnd() function to determine a random record to deal with. You could also return a random number in the recordset using RAND() for SQL and rnd() for access to give you a sort order and then process the questions sequentially.

To be able to give a more definitive answer it would help to know which database (SQL/ACCESS etc) and what kind of testing process you are trying to create, along with whether you are using DAO or ADO so that the code examples can be appropriate for your situation.

Expert Comment

ID: 7140606
A working idea, probably not so efficient:
Get all the records to a recordset, move to the first record, and then move forward using rs.movenext method a randon number of records.
LVL 14

Expert Comment

ID: 7140612
Well it would be wise to take care that questions should not be repeated for particular set.  For this one approach would be

Store the element in array generated by Rnd function
While generating next element check the existance in
array, if number exists then generate other number.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 43

Expert Comment

ID: 7140627
Indeed ajexpert, though if you use Rand()/Rnd() in the SQL statement that returns the records in a sorted order then you don't have to worry about this.

Accepted Solution

rpai earned 200 total points
ID: 7140666
You could do something like this:-

oConn.ConnectionString = "DSN=myDSN;UID=myUID;pwd=myPWD"
'-- Client cursor required to enable AbsolutePosition property
oConn.CursorLocation = adUseClient

Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM tbl_TestQuestions"
Set oRs = oCmd.Execute

'-- This would generate random numbers between 1 and 80
intValue = (Int(80 * Rnd) + 1)

'-- Set the pointer to the record in the RecordSet
oRs.AbsolutePosition = intValue

'-- List all the fields for that record
For iCount = 0 To oRs.Fields.Count - 1
    Debug.Print oRs.Fields(iCount).Value

'-- Release all the references to the objects used


Expert Comment

ID: 7140790
In addition to rpai's comment, you must save all the questions that are chosen to an array. So that every time you choose a random question you firstly check whether it exists in that array.

LVL 22

Expert Comment

ID: 7141203
What comes to my mind is to retrieve all primary keys into a dynamic array and shuffle them (see below.)  This would be DB-independent.

Now it becomes a simply task to use the next primary key in sequence to get the next "Random" question.

Sample (partially pseudo-)code:
' create array
dim lQKey() as long
dim lQCount as long

'open DB connection
strSQL = "Select IDKey from tblQuestions" strSQL, connectionstring

lQCount = 0
while not rstQuestions.EOF
  redim lQKey(lQCount)
  lQKey(lQCount) = rstQuestions.Fields("IDKey").Value
  lQCount = lQCount + 1

' Shuffle IDs
for i=0 to lQCount-1
  newi = rnd*lQCount
  temp = lQKey(i)
  lQKey(i) = lQKey(newi)
  lQKey(newi) = temp
next i


' Retieve Qs
dim i as integer
dim mbxAns as vbMsgboxResult

for i=0 to lQCount - 1
  strSQL = "SELECT * FROM tblQuestions WHERE IDKey = " & lQKey(i) strSQL, connectionstring
  if rstQuestions.EOF then  
    ' msgbox "Question " & lQKey(i) & " is missing!  Did someone just delete it?"
    mbxAns = msgbox (rstQuestions.Fields("QuestionText").Value, vbYesNo)
    ' Answer processing added here
next i
LVL 18

Expert Comment

ID: 7141220
If you want to make sure that you don't repeat any questions, and you want them in a random order, then you can use an algorithm I set up for shuffling a deck of cards.  Here is the pseudo-code.  Let me know if you need the code implementation:

Select all of your question into a recordset (you can order them by question_id if you want, but not important)

Declare a variable as a collection:

Dim oQuestions as New Collection

Then, move through the recordset using AddItem to add the question_ids to the collection

While Not RS.EOF
   oQuestions.AddItem RS("Question_ID").Value

Then, randomly pull items out of the collection into another array:

Dim i as Long
Dim aQuestions() as Long

Redim aQuestions(0) as long

While oQuestions.Count > 0
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
   i = Int(((oQuestions.Count - 1) - 0 + 1) * Rnd + 0)
   aQuestions(UBound(aQuestions)) = oQuestions(i)
   oQuestions.RemoveItem i
   if oQuestions.Count > 0 then
      Redim Preserve aQuestions(UBound(aQuestions) + 1) as long
   End if

When this is done, aQuestions will have a list of randomly selected question_ids

You can then sequentially read through the aQuestions array.

LVL 100

Expert Comment

ID: 7141282

Expert Comment

ID: 7141420
Private Function GetUniqueValue() As Variant
Dim sTemp as String
Dim i As Integer
Dim intValue As Integer
Dim vCounter As Variant
sTemp = "X"
   ReDim vCounter(10)
    For i = 1 To 10
        intValue = Int(10 * Rnd) + 1
        If InStr(sTemp, CStr(intValue)) = 0 Then
            sTemp = sTemp & " " & intValue
            Debug.Print i & vbTab & intValue & vbTab & sTemp
            vCounter(i) = intValue
            i = i - 1
        End If
GetUniqueValue = vCounter
End Function
LVL 18

Expert Comment

ID: 7141730
PS, I'm guessing that the collection indexes start at 0 and go to count - 1, but it's possible that they are 1 based, if so, then this statement would change to:

'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
 i = Int((oQuestions.Count - 1 + 1) * Rnd + 1)
LVL 49

Expert Comment

ID: 8002228
Hi global819,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept rpai's comment(s) as an answer.

global819, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer

Expert Comment

ID: 8094215
Comment from expert accepted as answer

E-E Admin

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 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