Randomly selecting rows from ms access in VB

Trying to randomly select rows out of my access database.

Page 1 of 1

3 Replies - 7809 Views - Last Post: 30 May 2008 - 08:14 PM Rate Topic: -----

#1 daveym1988   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 06-December 07

Randomly selecting rows from ms access in VB

Post icon  Posted 29 May 2008 - 05:42 PM

Hello there,

I have been working on my Quiz game project which untill recently has been pulling questions off of a notepad file. However, i have now converted my questions text file onto a ms access database and have linked it to my project via data connections etc . . .

All the labels load up the first question in the database automatically as i had each label assigned to a certain field.

Obviously i want the rows to be selected at random so added some code to try make this happen, however when i try and run the project it comes up with an unhandled error saying {"There is no row at position 5."}.

The code for the particular sub is as follows:

Public Sub loadQuestion()

		Dim qDice As New Random
		qcount = qDice.Next(1, 9)

		lblQuestion.Text = QUESTIONSDataSet.QuestionTbl(qcount).question
		lblCategory.Text = QUESTIONSDataSet.QuestionTbl(qcount).category
		lblA1.Text = QUESTIONSDataSet.QuestionTbl(qcount).answerA
		lblA2.Text = QUESTIONSDataSet.QuestionTbl(qcount).answerB
		lblA3.Text = QUESTIONSDataSet.QuestionTbl(qcount).answerC
		lblA4.Text = QUESTIONSDataSet.QuestionTbl(qcount).answerD
		qselect = QUESTIONSDataSet.QuestionTbl(qcount).actualAnswer
	End Sub



Does anyone know why it is throwing the error? I have played about with it but can't seem to fugure it out and any help would be GREATLY appreciated.

Also i eventually want to have it so it will randomly pick out, say 10 rows and show the question/rows one after the other, just so it doesn't randomly pick the same question twice in one game. So if there any suggestions to going about that, it would be a bonus!

Many, many thanks in advance!

;)

Is This A Good Question/Topic? 0
  • +

Replies To: Randomly selecting rows from ms access in VB

#2 mensahero   User is offline

  • I Desire...
  • member icon

Reputation: 17
  • View blog
  • Posts: 680
  • Joined: 26-May 08

Re: Randomly selecting rows from ms access in VB

Posted 30 May 2008 - 11:08 AM

Hello.. well I swear this is easy.. but this java thing for the past few days actually.. made me forget how to do this.. lmao..

well it has something to do with index.. each record in access db has an index.. refer to them using there index.. first record is always 0..

well read something about record index.. and database access with vb6.. it will give you enough knowledge in basic access db navigation.. like movefirst, movenext, etc..

and lastly.. since you made your labels bound.. (bound means well you attach a field to its datasource in design time, something like that) ..
you can navigate it using while loop..

something like this..

with db.recordset

if .bof = false then
.movefirst
end if

while not .EOF
a = .fields("questionNumber")
if a == b then
label.caption = .fields("question")
exit sub
endif
.movenext
wend

well I'm not sure.. so hopefully you got a little idea out of that..

well b is the random number.. and i use questionNumber since I forgot how to refer to record index... well.. probably your code up there wont work... so recode it..

goodluck..
Was This Post Helpful? 0
  • +
  • -

#3 daveym1988   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 06-December 07

Re: Randomly selecting rows from ms access in VB

Posted 30 May 2008 - 05:54 PM

View Postmensahero, on 30 May, 2008 - 11:08 AM, said:

well it has something to do with index.. each record in access db has an index.. refer to them using there index.. first record is always 0..


Hey there, your answer sure did count for somethin, so thankyou VERY much.

All i had to change was the part of the code where i declared the random, i changed the 1 to a 0, hence why i quoted the above part of your reply! :)

So thanks so much, i really appreciate your selfless act of helping me out! :) :^:

This post has been edited by daveym1988: 30 May 2008 - 05:55 PM

Was This Post Helpful? 0
  • +
  • -

#4 mensahero   User is offline

  • I Desire...
  • member icon

Reputation: 17
  • View blog
  • Posts: 680
  • Joined: 26-May 08

Re: Randomly selecting rows from ms access in VB

Posted 30 May 2008 - 08:14 PM

View Postdaveym1988, on 30 May, 2008 - 05:54 PM, said:

View Postmensahero, on 30 May, 2008 - 11:08 AM, said:

well it has something to do with index.. each record in access db has an index.. refer to them using there index.. first record is always 0..


Hey there, your answer sure did count for somethin, so thankyou VERY much.

All i had to change was the part of the code where i declared the random, i changed the 1 to a 0, hence why i quoted the above part of your reply! :)

So thanks so much, i really appreciate your selfless act of helping me out! :) :^:


:blink: :blink: awtz :blink: :blink: :D I have mixed emotion.. :crazy:
Was This Post Helpful? 1

Page 1 of 1