Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Random Question

Posted on 2002-07-09
13
Medium Priority
?
229 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 800 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
 
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 101

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
Suggested Courses
Course of the Month20 days, 16 hours left to enroll

810 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