Random Questions using DetailsView (Help)

Question

Greetings everyone, i would just like to ask how do i retrieve data rows in my table in my database randomly... i was able to create an online quiz wherein it displays the question,choices in consecutive order but what i want is, every time when user will start a quiz it will show questions in random order. I am using mssql 2005 as my database below is my code.. any advice or suggestions is highly sought.. thank you and have a great day..

QuizPage.aspx

<asp:DetailsView ID="questionDetails" runat="server" AutoGenerateRows="False" 
                CellPadding="4" ForeColor="#333333" 
                GridLines="None" Height="50px" Width="550px">
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
                <RowStyle BackColor="#F7F6F3" CssClass="generaltext" ForeColor="#333333" />
                <FieldHeaderStyle BackColor="#E9ECF1" CssClass="boldtext" Font-Bold="True" 
                    Width="80px" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <Fields>
                    <asp:TemplateField HeaderText="Question ID">           
                        <ItemTemplate>
                                       <asp:Label ID="question_id" runat="server" Text='<%# Bind("question_id") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField> 
                         <asp:TemplateField HeaderText="Question:">           
                        <ItemTemplate>
                                       <asp:Label ID="quiz_question" runat="server" Text='<%# Bind("quiz_question") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField> 
                         <asp:TemplateField HeaderText="Choice 1:">           
                        <ItemTemplate>
                                       <asp:Label ID="choice1" runat="server" Text='<%# Bind("choice1") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField> 
                         <asp:TemplateField HeaderText="Choice 2:">           
                        <ItemTemplate>
                                       <asp:Label ID="choice2" runat="server" Text='<%# Bind("choice2") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField> 
                         <asp:TemplateField HeaderText="Choice 3:">           
                        <ItemTemplate>
                                       <asp:Label ID="choice3" runat="server" Text='<%# Bind("choice3") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField> 
                         <asp:TemplateField HeaderText="Choice 4:">           
                        <ItemTemplate>
                                       <asp:Label ID="choice4" runat="server" Text='<%# Bind("choice4") %>'></asp:Label>
                        </ItemTemplate>                                                                             
                        </asp:TemplateField>
                </Fields>
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <EditRowStyle BackColor="#999999" />
                <AlternatingRowStyle BackColor="White" CssClass="generaltext" 
                    ForeColor="#284775" />
            </asp:DetailsView>

    Your Answer:&nbsp;
            <asp:DropDownList ID="answerDropDownList" runat="server" 
                style="margin-bottom: 0px">
                <asp:ListItem Value="1">Answer 1</asp:ListItem>
                <asp:ListItem Value="2">Answer 2</asp:ListItem>
                <asp:ListItem Value="3">Answer 3</asp:ListItem>
                <asp:ListItem Value="4">Answer 4</asp:ListItem>
            </asp:DropDownList>

  <asp:Button ID="buttonNext" runat="server" Text="Next" />

---

QuizPage.aspx.vb

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
    If Not IsPostBack Then
        getQuestions()
    End If
End Sub
Private Sub getQuestions()
    Dim quiz_id As Integer
    quiz_id = Session("quiz_id")
    Dim connection As SqlConnection = CreateConnection()
    Dim command As SqlCommand = Nothing
    Dim dt As DataTable = New DataTable()
    command = New SqlCommand("SELECT question_id,quiz_question, choice1, choice2, choice3, choice4, answer, quiz_id FROM tblQuizQuestion WHERE (quiz_id = @quiz_id)", connection)
    command.Parameters.AddWithValue("@quiz_id", quiz_id)
    Dim ad As SqlDataAdapter = New SqlDataAdapter(command)
    ad.Fill(dt)
    questionDetails.DataSource = dt
    questionDetails.DataBind()
End Sub
Protected Sub buttonNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttonNext.Click

    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.QuestionID = dr("QuestionOrder").ToString()
        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 Sub

Answer 1

While searching for a solution for your problem, I stumbled across this post:

http://haacked.com/archive/2004/06/21/658.aspx

By using ORDER BY NEWID() in the Select SQL statement you can randomize the result every single time you retrieve the records. I tried it on SQL Server 2008 and works brilliantly for more than 100 records. So all you need to do is modify your Select SQL to:

SELECT question_id,quiz_question, choice1, choice2, choice3, choice4, answer, quiz_id FROM tblQuizQuestion WHERE (quiz_id = @quiz_id) ORDER BY NEWID()

The other alternative is to create a RandomizeDataTable function that will randomize the order of rows. If the above solution does not work for you then we can take a look at that.