1

I was wondering if there's a way to choose a random record after using the "Connect to Database" tool within Visual Studio 2017. I'm trying to select a random record between 1 and 21. The MS access database has QNumber (Primary Key), Question, Answer1, Answer2, Answer3 and Answer4. QNumber is a Number whereas every other is Text.

I tried using this code from the official Microsoft site.

Dim MyValue As Integer
MyValue = Int((6 * Rnd) + 1)    ' Generate random value between 1 and 6.

However, I wasn't sure how to apply it to my situation.

Since using the "Connect to Database" tool, I've used this code to insert the data from the MS Access Database to fill in my text boxes:

txtQNumber.DataBindings.Add("Text", QuizDataSet, "Quiz.QNumber")
txtQ.DataBindings.Add("Text", QuizDataSet, "Quiz.Question")
txtAnswer.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer1")
txtAnswer2.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer2")
txtAnswer3.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer3")
txtAnswer4.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer4")

The code below shows what I've done to stop the quiz after 10 questions have been answered. The bit with "BindingContext.." is how the questions progress once the forward button is clicked. 

Answered = 0

Private Sub cmdForward_Click(sender As Object, e As EventArgs) Handles cmdForward.Click

        BindingContext(QuizDataSet, "Quiz").Position = BindingContext(QuizDataSet, "Quiz").Position + 1

Answered = Answered + 1

If Answered = 10 Then
    End
End If

So far, the output of the text boxes has been very linear - it starts from QNumber 1, moves onto QNumber 2, etc. and goes to QNumber 10. I want it to be random. For example, it starts at QNumber 15, moves onto QNumber 6 and then ends on QNumber 19 or something like that.

I would assume I would have to use .Position, shown beforehand, but again, I'm not really sure so any help would be very much appreciated.

Martin
  • 16,093
  • 1
  • 29
  • 48
C. McAu
  • 13
  • 3
  • The code looks like Visual Basic and not C# – Sir Rufo Apr 12 '19 at 22:06
  • @Sir Rufo Yes, It is. Sorry about that, It's been quite a long day. Thank you to Martin for editing that. – C. McAu Apr 12 '19 at 22:11
  • 1
    Possible duplicate of [How to get random record from MS Access database](https://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database) – Martin Apr 12 '19 at 22:12
  • 1
    I would suggest that rather than trying to randomly select the record in the VB.Net code, you simply modify your `SELECT` statement to order the results randomly. See the question I linked in my earlier comment. – Martin Apr 12 '19 at 22:13
  • I'll try that just now, thank you. Will that randomise every time the database is called upon? – C. McAu Apr 12 '19 at 22:20

1 Answers1

0

You can modify your SQL query to include a pseudo-random value for each record:

Select *, Rnd(-Timer() * [ID]) As RandomNumber 
From YourTable

where ID is the primary key. Then sort on RandomNumber.

Or, you can let the query sort:

Select *
From YourTable
Order By Rnd(-Timer() * [ID]);
Gustav
  • 53,498
  • 7
  • 29
  • 55