Solved

Random Question

Posted on 2002-07-09
13
216 Views
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.
0
Comment
Question by:global819
  • 2
  • 2
  • 2
  • +7
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
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.
0
 
LVL 2

Expert Comment

by:nahumd
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.
0
 
LVL 14

Expert Comment

by:ajexpert
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.
0
 
LVL 43

Expert Comment

by:TimCottee
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.
0
 
LVL 5

Accepted Solution

by:
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
oConn.Open

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

Randomize
'-- 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
Next

'-- Release all the references to the objects used

0
 
LVL 2

Expert Comment

by:egsemsem
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.

Osama  
0
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.

 
LVL 22

Expert Comment

by:rspahitz
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"
rstQuestions.open strSQL, connectionstring

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

' 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)
  rstQuestions.open strSQL, connectionstring
  if rstQuestions.EOF then  
    ' msgbox "Question " & lQKey(i) & " is missing!  Did someone just delete it?"
  else
    mbxAns = msgbox (rstQuestions.Fields("QuestionText").Value, vbYesNo)
    ' Answer processing added here
  endif
  rstQuestions.movenext
next i
0
 
LVL 18

Expert Comment

by:mdougan
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
   RS.MoveNext
Wend

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

Dim i as Long
Dim aQuestions() as Long

Redim aQuestions(0) as long
Randomize

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
Wend

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

You can then sequentially read through the aQuestions array.


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7141282
learning
0
 
LVL 5

Expert Comment

by:rpai
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
        Randomize
        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
        Else
            i = i - 1
        End If
    Next
GetUniqueValue = vCounter
End Function
0
 
LVL 18

Expert Comment

by:mdougan
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)
 
0
 
LVL 49

Expert Comment

by:DanRollins
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
0
 
LVL 1

Expert Comment

by:Computer101
ID: 8094215
Comment from expert accepted as answer

Computer101
E-E Admin
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
z = x + y – 1 6 67
VB6 add a minute to the date time value 8 60
Excel object stays open 19 70
vb6 connector to mongodb 2 46
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

929 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

11 Experts available now in Live!

Get 1:1 Help Now