Generating Random Questions from SQL database (Using VB 6.0)

One part from the Quiz module in our CAI for Computer Repair Concepts

Page 1 of 1

4 Replies - 12697 Views - Last Post: 12 July 2009 - 08:55 AM Rate Topic: -----

#1 yhengalfanza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 06-July 09

Generating Random Questions from SQL database (Using VB 6.0)

Posted 06 July 2009 - 08:03 AM

hi friends! I'm a senior IT student from the Philippines...we are currently conducting our Thesis entitled Computer Aided INstruction for Computer Repair Concepts..This is actually an application drag-and-drop program...
We're still on the first stage...We are working on the quiz module right now..

My problem is we were not able to generate questions randomly from our database which is SQL..We can actually retrieve data from the database in sequence..Kindly help us modify our code for our main concern...which will allow the program to generate random questions from the database, we are using option buttons for the choices.(optional--->>will be able to choose the correct answer and compute for the score...). By the way, we are using VB 6.0.

Hoping for your response as soon as possible! thank you so much!

Below is our code for the Quiz.Kindly check this out..tnx :)

Option Explicit
Dim lessnum As Integer

Sub S_LOAD_con()

	Dim rsless As New ADODB.Recordset
	Dim strsql As String
	
	strsql = "select quiznum, quizquestion, choice1,choice2,choice3,choice4 from quizmaintenance"
	
	rsless.Open strsql, conn
		
		lblquiznum.Caption = rsless!quiznum
	   txtQuestion.Text = rsless!quizquestion
	   
	   optChoice(0).Caption = rsless!choice1
	   optChoice(1).Caption = rsless!choice2
	   optChoice(2).Caption = rsless!choice3
	   optChoice(3).Caption = rsless!choice4
			  
	rsless.Close
	
End Sub

Private Sub cmdOK_Click()
Dim rsless As New ADODB.Recordset
	Dim strsql As String
   
	lessnum = lessnum + 1
	
	strsql = "select quiznum, quizquestion, choice1,choice2,choice3,choice4 from quizmaintenance" & _
			" where quiznum = '" & lessnum & " ' "
  
	rsless.Open strsql, conn
		If rsless.EOF = False Then
		
			lblquiznum.Caption = rsless!quiznum
	   txtQuestion.Text = rsless!quizquestion
	   
	   optChoice(0).Caption = rsless!choice1
	   optChoice(1).Caption = rsless!choice2
	   optChoice(2).Caption = rsless!choice3
	   optChoice(3).Caption = rsless!choice4
				   
		Else
			MsgBox "Youve Finish The Quiz?", vbOkOnly
			cmdOK.Enabled = False
			Exit Sub
			
		End If
		
	rsless.Close
End Sub

Private Sub Form_Load()
lessnum = 1
S_LOAD_con
End Sub



Mod Edit: Please use code tags when posting your code. Code tags are used like so => :code:

Thanks,
PsychoCoder :)

Is This A Good Question/Topic? 0
  • +

Replies To: Generating Random Questions from SQL database (Using VB 6.0)

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Generating Random Questions from SQL database (Using VB 6.0)

Posted 06 July 2009 - 08:38 AM

For selecting random questions on each query to the database you actually have a couple options. You can use the RAND() Function like this

SELECT 
			RAND(quiznum * 100), quizquestion, choice1,choice2,choice3,choice4 
FROM
			 quizmaintenance



If you're using SQL 2005 you can use the HASHBYTES Function. something like

SELECT quizquestion, choice1,choice2,choice3,choice4, HASHBYTES('md5',CAST(quiznum +100 as VARCHAR)) quiznum 
FROM quizmaintenance ORDER BY quiznum




Another option would be to use NEWID(), like this

SELECT
		  quiznum , quizquestion, choice1,choice2,choice3,choice4 
FROM
		 quizmaintenance 
ORDER BY
		 NEWID()




Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#3 yhengalfanza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 06-July 09

Re: Generating Random Questions from SQL database (Using VB 6.0)

Posted 06 July 2009 - 06:06 PM

tnx psychocoder!

unfortunately i received lots of error messages..on the first example, it says "incorrect arguments to rand"; just to verify 100 is the total # of questions???..

for the second one "command text was not set for the command object" I was wondering what this particular code means--->>>HASHBYTES('md5',CAST(quiznum +100 as VARCHAR)) ; this is actually my first time to encounter this code....

and for the 3rd example, what is exactly NEWID()? do i have to create a new function, what is it all about?

many thanks!:)
Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Generating Random Questions from SQL database (Using VB 6.0)

Posted 11 July 2009 - 07:41 PM

get the count of the recordset and use the non repeated random no generation in the code snippet here then

use like this

 lessnum = lessnum + 1
' before you use this Get the algorith for random nogen
qno =   rndArray(lessnum)

	strsql = "select quiznum, quizquestion, choice1,choice2,choice3,choice4 from quizmaintenance" & _
			" where quiznum = '" & lessnum & " ' "
 
	rsless.Open strsql, conn
   if not rsless.eof then
		If lessnum>recordcount  Then
	   
			lblquiznum.Caption = rsless!quiznum
	   txtQuestion.Text = rsless!quizquestion
	   
	   optChoice(0).Caption = rsless!choice1
	   optChoice(1).Caption = rsless!choice2
	   optChoice(2).Caption = rsless!choice3
	   optChoice(3).Caption = rsless!choice4
				   
		Else
			MsgBox "Youve Finish The Quiz?", vbOkOnly
			cmdOK.Enabled = False
			Exit Sub
		   
		End If
	  end if 
	rsless.Close




non repeating random numbers
http://www.dreaminco...snippet1929.htm
Was This Post Helpful? 0
  • +
  • -

#5 yhengalfanza  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 06-July 09

Re: Generating Random Questions from SQL database (Using VB 6.0)

Posted 12 July 2009 - 08:55 AM

thanks a lot guys! and thava i haven't tried your code yet..let me work on that, actually it seems complicated for me..:)

By the way, i just wanted to share with you what I did with the quiz..so far, i was able to generate the questions randomly but the problem is the last record from the database never changed, it stays the last question still...

and one more thing, the upper limit is actually stated which is 15, what if its dynamic or there should be no exact number for the total items of the quiz, what is the code for that?

thanks a lot! and more power to you guys!:)


Private Sub Form_Load()

lessnum = 1
Load_Con
Randomize

End Sub

Sub Load_Con()

	Dim rsless As New ADODB.Recordset
	Dim strsql As String
	
	strsql = "select quiznum,quizquestion,choice1,choice2,choice3,choice4	from quizmaintenance" & _
			 " where quiznum = Round(((15 - 1) * RAND() + 1),0)"
			
	rsless.Open strsql, conn
	
	txtQuestion.Text = rsless!quizquestion
	optChoice(0).Caption = rsless!Choice1
	optChoice(1).Caption = rsless!Choice2
	optChoice(2).Caption = rsless!Choice3
	optChoice(3).Caption = rsless!Choice4
			   
	rsless.Close
	
End Sub

Private Sub cmdOK_Click()

  Dim rsless As New ADODB.Recordset
  Dim strsql As String
	
	lessnum = lessnum + 1
	
	strsql = "select quiznum,quizquestion,choice1,choice2,choice3,choice4 from quizmaintenance" & _
			 " where quiznum = '" & lessnum & " ' "

	rsless.Open strsql, conn
		
	If rsless.EOF = False Then
	txtQuestion.Text = rsless!quizquestion
	optChoice(0).Caption = rsless!Choice1
	optChoice(1).Caption = rsless!Choice2
	optChoice(2).Caption = rsless!Choice3
	optChoice(3).Caption = rsless!Choice4
		 
	 Else
			MsgBox "Youve Finish The Quiz!", vbOKOnly
			'frmTestResult.Show
			cmdOK.Enabled = False
			Exit Sub

		End If
		
	rsless.Close
 End Sub



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1