Search code examples
databasevb.netvisual-studio-2010ms-accessmultiple-choice

How to randomly select multiple choice questions from Access database

I have an Access Database containing about 30 questions. The database is divided in 3 tables; Questions, Possible Answers and Answer. The questions have from 2 to 5 possible answers. How can I randomly select 10 questions from my database and add them to my vb form?

PS: This is my first time doing this

Here is my code

Dim provider As String Dim dataFile As String Dim connString As String Public myConnection As OleDbConnection = New OleDbConnection Public dr As OleDbDataReader

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "C:\Users\Phil\Desktop\Questions.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()
    Dim str As String
    str = "SELECT Top 10 ID_Question From Questions ORDER BY RND(ID_Question)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader

  While dr.Read()
        TextBox1.Text = dr("ID_Question").ToString
  End While
    myConnection.Close()
    MsgBox("fsafa")
End Sub

The Textbox does not change and the msgBox does not show

Solution that worked for me if anyone is interested

SELECT Top 10 ID_Question, Question_Name 
FROM tblQuestions 
ORDER BY RND(-(100000*ID_Question)*Time())
Solution

  • I have to assume that your questions have an AutoNumber field, your possible answers has a one-to-many join based on that AutoNumber field and your answers have a one-to-one join based on that AutoNumber field? That would be the best way to associate the tables.

    If so, try something like this:

    SELECT Top 10 Question_ID FROM tblQuestions ORDER BY RND(Question_ID)

    This should give you the top 10 randomly selected Question_IDs (or whatever you're calling that AutoNumber field I spoke about above), and then you can left join to the Questions/Possible Answers/Answers tables based on that ID. You would simply populate a form or subform based on the SQL above in order to display the questions.