  |
| View previous topic :: View next topic |
| Author |
Message |
jozin
Newbie
Joined: 25 Jul 2005
Posts: 7
|
 Posted: Jul 25th, 2005 06:44 PM Post subject: How to retrieve random questions from a database. |
 |
|
Hi all,
I got this problem. I have a set of 100 test questions in a QUESTION table. how can I get say 20 random questions from my table. Can you please show me a sample code on how to do it?
Any help will be very much appreciated. =D |
|
| Back to top |
|
dougthomas
Moderator
Joined: 27 Jul 2005
Posts: 271
Location: Essex, UK
|
| Posted: Jul 31st, 2005 05:51 AM Post subject: |
|
|
Hi Jozin,
The major problem is to create 20 different random numbers in the range 1 to 100. The Random number generator in VB creates a number greater than or equal to Zero and less than one.
So to create a random number in the range required we need something like:
IntRandom = int(100* Rnd + 1)
The trouble is that if we do this 20 times we may get the same number twice
Rather than have to get into writing a recursive routine that checks if a number has already been generated I took a slightly different approach.
1. Set up an array holding the numbers 1 to 100
2. For a random number of times randomly swap random elements in the array with each other
3. Use this randomly sorted array to pick out the questions, by number
e.g.
| Code: |
Dim intNumbers (100) as Integer
Dim strQuestion (20) as String
Dim intEnd as Integer
Dim IntR1 as Integer
Dim IntR2 as Integer
Dim intTemp as Integer
Dim IntI as Integer
Dim strSQL as String
Dim db As Connection
'
' Establish a connection to the Database
' and Open it. Establish the Recordset
'
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyProject\Questions.mdb;"
Set adoRs = New Recordset
'
' Set up an array with the numbers 1 to 100
'
For intI = 0 to 99
intNumbers(intI) = intI + 1
Next intI
'
' Seed the Random Number Generator and
' set up a loop that will go for somewhere
' between 100 and 1000 times
'
Randomize
intEnd = int (901 * Rnd + 100)
For intI = 1 to intEnd
'
' Generate two random numbers between 1 and 100
' and swap these two elements of the array
'
IntR1 = Int(100 * Rnd + 1)
IntR2 = Int(100 * Rnd + 1)
IntTemp = IntNumbers(intR1)
IntNumbers(IntR1) = intNumbers(IntR2)
IntNumbers(IntR2) = intTemp
Next intI
'
' Use the first 20 elements of the, now swapped, array
' to pick up the questions from the Database
' and save each question in the strQuestion array
'
For intI = 1 to 20
strSQL = "SELECT Question FROM tblQuestions WHERE QuestionNumber = " & Cstr(intNumbers(intI))
adoRS.Open strSQL, db, adOpenStatic, adLockOptimistic
strQuestion(intI-1) = Cstr(intI) & " " & adoRS!Question
Next intI
|
Please note that I have just cobbled the above together and it may contain typos - use it as a guide rather than working code.
A few Statistics Graduates (and proper VB programmers) may not like it, but I found that it performed well enough for the application I used the technique for.
(I'm sure there must be a "nicer" way of doing it, but I don't know what it might be. Having to generate unique Random Numbers seems to be a bit of a contradiction) Over to the experts.....
Regards
Doug |
|
| Back to top |
|
jozin
Newbie
Joined: 25 Jul 2005
Posts: 7
|
| Posted: Aug 4th, 2005 03:44 AM Post subject: |
|
|
hi Doug,
I'm sorry for the delay of my reply. I got some problems with my connection. Well anyway, thank you very much for your help. I sure can use this code as a guide. I hope I can contact you again for some clarifications.
Again thanks a lot and more power.
jozin. =D |
|
| Back to top |
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
