Selecting multiple distinct values in database

Question

I have this online quiz that generates random questions, but the only problem is that it repeats the previous questions. I have limited questions (I have 10 questions in my table, but I have limited the number of questions to be 5. The output would only display 5 random questions) which I named as RequiredRecords.

 question_id 
     1 
     3
     4
     7 
     9 
     14 
     15
     24
     26
     29

Should output after the random

  question_id 
     3
     4
     9 
     14 
     24

I have tried to visit this question, but it doesn't resolve my problem. Below is some of my code and SQL statements I used.

I figured out that there's nothing wrong with my query on creating random questions and I can display it with no duplication, but there's something wrong with my other codes that makes the program having a duplication. Please help me.

---

Code Behind using VS2008 3.5

Partial Class Student_DetailView
Inherits System.Web.UI.Page

Shared TotalRecords As Integer
Private sqlda As SqlDataAdapter
Private dt As DataTable

Private Function CreateConnection() As SqlConnection
    Dim _connectionString As String = ConfigurationManager.ConnectionStrings("LMSConnectionString").ConnectionString
    Return New SqlConnection(_connectionString)
End Function

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim quiz_id As Integer
    quiz_id = Session("quiz_id")

    Dim query As String = "SELECT COUNT(*) AS TotalRecords FROM tblQuizQuestion WHERE (quiz_id = '" & quiz_id & "')"
    Dim dt As DataTable = GetRecords(query)
    TotalRecords = Convert.ToInt32(dt.Rows(0)("TotalRecords"))

    getQuestions()
End Sub

Public Function GetRecords(ByVal Query As String) As DataTable
    Dim connection As SqlConnection = CreateConnection()
    connection.Open()
    sqlda = New SqlDataAdapter(Query, connection)
    dt = New DataTable()
    sqlda.Fill(dt)
    connection.Close()
    Return dt
End Function

Public Function RandomNumbers(ByVal max As Integer) As ArrayList
    Dim lstNumbers As New ArrayList()
    Dim rndNumber As New Random()
    Dim number As Integer = rndNumber.[Next](1, max + 1)
    lstNumbers.Add(number)
    Dim count As Integer = 0
    Do
        number = rndNumber.[Next](1, max + 1)
        If Not lstNumbers.Contains(number) Then
            lstNumbers.Add(number)
        End If
        count += 1
    Loop While count <= 10 * max
    Return lstNumbers
End Function

Public Function GetRandomNumbersCSV(ByVal max As Integer, ByVal req As Integer) As String
    Dim CSV As String = ""
    Dim lstNumbers As ArrayList = RandomNumbers(max)
    For i As Integer = 0 To req - 1
        CSV += lstNumbers(i).ToString() & ","
    Next
    CSV = CSV.Remove(CSV.Length - 1)
    Return CSV
End Function

Protected Sub buttonNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttonNext.Click
    If Not Session("dt") Is Nothing Then
        getQuestions()
        Try
            ' Save off previous answers
            Dim dr As System.Data.DataRowView
            dr = CType(questionDetails.DataItem, System.Data.DataRowView)

            ' Create Answer object to save values
            Dim a As Answer = New Answer()
            a.CorrectAnswer = dr("answer").ToString()
            a.UserAnswer = answerDropDownList.SelectedValue.ToString()

            Dim al As ArrayList
            al = CType(Session("AnswerList"), ArrayList)
            al.Add(a)

            Session.Add("AnswerList", al)

        Catch ex As Exception

            Response.Redirect("default.aspx")
        End Try
        If questionDetails.PageIndex = questionDetails.PageCount - 1 Then
            ' Go to evaluate answers
            Response.Redirect("results.aspx")
        Else
            questionDetails.PageIndex += 1

        End If

        If questionDetails.PageIndex = questionDetails.PageCount - 1 Then
            buttonNext.Text = "Finished"
        End If
    End If
End Sub

Private Sub getQuestions()
    Dim RequiredRecords As Integer
    RequiredRecords = 5
    Dim CSVData As String, query As String
    Dim quiz_id As Integer
    quiz_id = Session("quiz_id")
    If TotalRecords >= RequiredRecords Then
        CSVData = GetRandomNumbersCSV(TotalRecords, RequiredRecords)

        query = "SELECT distinct question_id,quiz_question, choice1, choice2, choice3, choice4, answer, quiz_id FROM " & _
                "(SELECT question_id,quiz_question, choice1, choice2, choice3, choice4, answer, quiz_id , ROW_NUMBER() OVER(ORDER BY rand()) " & _
                "AS RowID FROM tblQuizQuestion WHERE quiz_id = '" & quiz_id & "') TempTable WHERE RowID IN(" & CSVData & ")"
        dt = GetRecords(query)
        Session("dt") = dt
        questionDetails.DataSource = dt
        questionDetails.DataBind()
    Else
        Response.Write("Required Records must be greater than Requried Records.")
    End If
End Sub

End Class

Answer 1

You should be able to randomize your questions like this

Select Top 5 QuestionID, newID() as RandomString From Questions Order By RandomString

newid() is a keyword that will give you a random guid that you then sort the questions by

Answer 2

Take a look at this answer for a way to randomize entries coming from a database in a predictable manner that allows for resuming where you left off ... Linq Orderby random ThreadSafe for use in ASP.NET

You can seed a Random number generator by a specific value (e.g. user ID, user ID plus date, ...) and then fetch each item from the database easily without knowing anything other than the index of the item you want from the random sequence.