How do I list all Users and their results?

Hi again,

The code below allows management to select a particular survey from the dropdown.

Once a survey is selected from the dropdown, questions and the answers to the those questions are listed below it.

Here is example of current results layout:

Survey:__________This is dropdown

Question:1:_____

Choices:______

Question 2: _____

Choices:___________

Question N:__________

Choices:_________

This works fine. Here is that code for above:

<fieldset style="margin-bottom: 20px;">
   <legend style="font-weight: bold;">Survey Statistics</legend>
    <table style="width: 100%">
        <tr>
            <td align="center">
                <asp:Label ID="Label1" runat="server" Text="Select Survey :"></asp:Label>
                <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
                    DataTextField="Title" DataValueField="SurveyID">
                </asp:DropDownList></td>
        </tr>
        <tr>
            <td>
                <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource2" Width="100%">
                    <ItemTemplate>
                        <table style="width: 100%">
                            <tr>
                                <td style="height: 21px">
                                    <asp:Label ID="Label3" runat="server" Font-Bold="True" Text='<%# Eval("QuestionID") %>'></asp:Label>
                                    <strong>)</strong>
                                    <asp:Label ID="Label2" runat="server" Font-Bold="True" Text='<%# Eval("Question") %>'></asp:Label></td>
                            </tr>
                            <tr>
                                <td>
                                    <asp:BulletedList ID="BulletedList1" runat="server" BulletStyle="Square">
                                    </asp:BulletedList>
                                </td>
                            </tr>
                        </table>
                    </ItemTemplate>
                    <HeaderTemplate>
                        <asp:Label ID="Label4" runat="server" Font-Bold="True" Text="Here are the survey results :"></asp:Label>
                    </HeaderTemplate>
                </asp:DataList></td>
        </tr>
    </table>
    <br />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
        SelectCommand="SELECT [SurveyID], [Title] FROM [Survey]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
        SelectCommand="SELECT * FROM [SurveyQuestions] WHERE (([SurveyID] = @SurveyID) AND ([AnswerType] <> @AnswerType))">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="SurveyID" PropertyName="SelectedValue"
                Type="Int32" />
            <asp:Parameter DefaultValue="T" Name="AnswerType" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

We would like to add to this page by listing all users of the survey and their overall scores.

The is the expected new layout:

Survey:__________This is dropdown

Question:1:_____

Choices:______

Question 2: _____

Choices:___________

Question N:__________

Choices:_________

John Doe’s score:

Total Questions:____  Total Correct:___________ With Multiple:_________ %Correct:________%

Then next user until all users and scores are listed.

The code below lists the scores correctly but doesn’t list the users.

 

Here is what I have: All I needed help with is to add the logic that lists scores one user after another user.

                <tr>
            <td>
                <asp:DataList ID="DataList2" runat="server" DataSourceID="SqlDataSource3" Width="100%">
                    <ItemTemplate>
                        <table style="width: 100%">
                            <tr>
               <td>
                Total Questions:
                <asp:Label ID="CourseNameLabel" runat="server" Text='<%# Eval("TotalQuestions") %>' 
                    ForeColor="#B22222" font-weight="bold" />
                </td>
                <td>
                Total Correct:
                <asp:Label ID="TrainingDatesLabel" runat="server" Text='<%# Eval("TotalCorrect") %>' 
                   ForeColor="#B22222" font-weight="bold" />
                </td>
                <td>
                 With Multiple:
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("TotalCorrect2") %>' 
                   ForeColor="#B22222" font-weight="bold" />
                </td>
                <td>
                % Correct:
                <asp:Label ID="InstructorNameLabel" runat="server" Text='<%# Eval("PercentCorrect2") %>' 
                   ForeColor="#B22222" font-weight="bold" /><span style="color:#B22222">%</span>
                </td>
                            </tr>
                        </table>
                    </ItemTemplate>
                </asp:DataList></td>
        </tr>
  
           <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
          SelectCommand="WITH Questions AS
            ( SELECT SQ.QuestionID,
              CorrectChoices = COUNT(NULLIF(SC.IsCorrect, 0)),
              ChoicesGiven = COUNT(SA.ChoiceID),
              CorrectChoicesGiven = COUNT(CASE WHEN SA.ChoiceID IS NOT NULL AND SC.IsCorrect = 1 THEN 1 END),
              ExtraChoicesGiven = CASE WHEN COUNT(SA.ChoiceID) > COUNT(NULLIF(SC.IsCorrect, 0)) THEN COUNT(SA.ChoiceID) - COUNT(NULLIF(SC.IsCorrect, 0)) ELSE 0 END
              FROM SurveyQuestions SQ
              INNER JOIN SurveyChoices SC 
                  ON SQ.QuestionId = SC.QuestionID
              LEFT JOIN SurveyAnswers SA 
                  ON SA.QuestionId = SC.QuestionID 
                  AND SA.ChoiceID = SC.ChoiceID
      WHERE   SQ.SurveyID = @SurveyId
      GROUP BY SQ.QuestionID
  ), QuestionScores AS
  (   SELECT  QuestionID,
              Score = CASE WHEN CorrectChoicesGiven - ExtraChoicesGiven < 0 THEN 0
                          ELSE CAST(CorrectChoicesGiven - ExtraChoicesGiven AS FLOAT) / CorrectChoices
                      END,
              Score2 = ISNULL(CAST(CorrectChoicesGiven AS FLOAT) / NULLIF(CASE WHEN ChoicesGiven > CorrectChoices THEN ChoicesGiven ELSE CorrectChoices END, 0), 0)
      FROM    Questions
  )
  SELECT  TotalQuestions = COUNT(*),
          TotalCorrect = SUM(Score),
          PercentCorrect = CAST(100.0 * SUM(Score) / COUNT(*) AS DECIMAL(5, 2)),
          TotalCorrect2 = SUM(Score2),
          PercentCorrect2 = CAST(100.0 * SUM(Score2) / COUNT(*) AS DECIMAL(5, 2))
  FROM    QuestionScores;">
          <SelectParameters>
              <asp:ControlParameter ControlID="DropDownList1" Name="SurveyID" PropertyName="SelectedValue"
                  Type="Int32" />
              <asp:Parameter DefaultValue="T" Name="AnswerType" Type="String" />
          </SelectParameters>
  
</asp:SqlDataSource>

Thanks a lot in advance

 

Hi,

refer this
link

hi simflex ,

there is a demo below:

http://www.c-sharpcorner.com/UploadFile/rohatash/nested-datalist-in-Asp-Net/

hope it can help you.

Leave a Reply