1

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())
phil652
  • 1,484
  • 1
  • 23
  • 48
  • `Answer` need not exist - it only has to be a flag on the correct PossibleAnswer entry. You could load the Question IDs, shuffle them, then pick the first N Questions – Ňɏssa Pøngjǣrdenlarp Jan 26 '15 at 15:05
  • This might help: http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database but it all depends where you have the problem (get the data or displaying the data). – the_lotus Jan 26 '15 at 15:17
  • You would need some kind of relation; questions to answers/possible answers to accomplish this... So even if you picked random questions, you would still need to have some sort of relation to get the other details. – Trevor Jan 26 '15 at 15:36
  • Thank you, I have update my question if you know my problem let me know – phil652 Jan 27 '15 at 15:34

1 Answers1

1

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.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thank you for your answer. I've put this into my code but there still something not right. I have edit my question. Let me know if you can see my error. – phil652 Jan 27 '15 at 15:37
  • In my code I use SQLDataReader instead of OLEDbDataReader, I'm not sure if that makes a difference. Then, just after that I use "if (dr.HasRows)" to make sure my reader actually has data in it. Put a break there and see what's going on. – Johnny Bones Jan 27 '15 at 16:25
  • If that doesn't work, you can also try [the solution presented here](http://www.vb-helper.com/howto_net_random_records.html). – Johnny Bones Jan 27 '15 at 16:29
  • This solution is also using OLEDbDataReader wich one should I use? – phil652 Jan 27 '15 at 16:47
  • @phil652 - I got sidetracked on a different site, hadn't checked this question in a few days (I answer a lot of them...) and just saw that you gave me the checkmark. I'm curious if you were able to figure it out, and if so what you did to get there? – Johnny Bones Feb 04 '15 at 16:45
  • I got it to work, I ended up using this query `SELECT Top 10 ID_Question, Question_Name FROM tblQuestions ORDER BY RND(-(100000*ID_Question)*Time())`. I am using OLEDbDataReader and Jet.OLEDB.4.0, at first it was not working because I was using the 32bit version on a 64bit machine so I had to change the target CPU to x86 – phil652 Feb 04 '15 at 16:54