locked
Second Sqldatasource Within Listview Not Returning Value RRS feed

  • Question

  • User1426469546 posted

    Hi -  Need some help.

    I have a ListView that returns rows of data using SqlDataSource1.

    I have a second SqlDataSource (SqlDataSource2) that should return the sum of values from a database table and populate an ASP Label.  However, the Label control doesn't seem to display the value.  Here's the relevant aspx code.  There's nothing in the code behind except a redirect from a button click.

    Here's the relevant aspx code:

    <%@ Page Title="" Language="VB" MasterPageFile="~/site.master" AutoEventWireup="false" CodeFile="ManageSurveys.aspx.vb" Inherits="RegisteredUsers_ManageSurveys" %>
    
    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
        <h1>Manage Surveys</h1>
        
        <asp:LoginView ID="LoginView1" runat="server">
        
            <LoggedInTemplate>
            <div>
                <asp:Button ID="NewSurveySetButton" runat="server" Text="Create New Survey Set" OnClick="NewSurveySetButton_Click"/>
            </div>
        
            <h1>My Survey Sets</h1>
            
                <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                 ConnectionString="<%$ ConnectionStrings:stgiConnectionString %>" 
                 SelectCommand="SELECT SurveySetStatus.idSurveySetStatus, SurveySetStatus.SurveySetName, SurveySetStatusCodes.SurveySetStatusDesc, SurveySetStatus.CreateDateTime, SurveySetStatus.ModifiedDateTime, SurveySetStatus.SubmittedDateTime, SUM(SurveySetTaskItemDetails.TimeSpent) AS 'Time Spent'
                                FROM SurveySetStatus, SurveySetTaskItemDetails, SurveySetStatusCodes
                                WHERE SurveySetStatus.UserId = @MyUserId
                                AND SurveySetStatus.idSurveySet = SurveySetTaskItemDetails.idSurveySet
                                AND SurveySetStatus.idSurveySetStatusCode = SurveySetStatusCodes.idSurveySetStatusCode
                                GROUP BY SurveySetStatus.idSurveySetStatus, SurveySetStatus.SurveySetName, SurveySetStatusCodes.SurveySetStatusDesc, SurveySetStatus.CreateDateTime, SurveySetStatus.ModifiedDateTime, SurveySetStatus.SubmittedDateTime">
          
                    <SelectParameters>
                        <asp:SessionParameter Name="MyUserId" SessionField="MyUserId" Type="String"/>
                    </SelectParameters>
    
                </asp:SqlDataSource>
            
                <asp:SqlDataSource ID="SqlDataSource2" runat="server"
                                   ConnectionString="<%$ ConnectionStrings:stgiConnectionString %>" 
                                   SelectCommand="SELECT SUM(SurveySetTaskItemDetails.TimeSpent) AS 'Total Time'
                                                  FROM SurveySetTaskItemDetails
                                                  WHERE SurveySetTaskItemDetails.UserId = @MyUserId">
                           
                    <SelectParameters>
                        <asp:SessionParameter Name="MyUserId" SessionField="MyUserId" Type="String"/>
                    </SelectParameters>    
            
                </asp:SqlDataSource>
                
                <div>
                <asp:ListView ID="SurveySetList" runat="server" DataSourceID="SqlDataSource1">
            
                    <LayoutTemplate>
    
                        <table border="0" cellpadding="1">
                            <tr style="background-color:#E5E5FE">
                                <th align="left">Survey Set Name</th>
                                <th align="left">Current Status</th>
                                <th align="left">Created</th>
                                <th align="left">Last Modified</th>
                                <th align="left">Submitted</th>
                                <th align="left">Summary of Hours</th>
                            </tr>
                            <asp:PlaceHolder ID="itemPlaceHolder" runat="server" />
                            <tr />
                            <tr>
                                <td /><td /><td /><td />
                                <td align="right"><asp:Label ID="SurveySetTotalHoursLabel" runat="server" Text="My Total: " Font-Bold="True"></asp:Label></td>
                                <td><asp:Label ID="SurveySetTotalHours" runat="server" DataSourceID="SqlDataSource2" Text='<%# Eval("Total Time") %>'></asp:Label></td>
                            </tr>
                            <tr />
                                <td /><td /><td /><td />
                                <td><asp:Label ID="TotalYearHoursLabel" runat="server" Text="Hours in a Year: " Font-Bold="True"></asp:Label></td>
                                <td><asp:Label ID="HoursLabel" runat="server" Text="2080"></asp:Label></td>
                        </table>
                    
                    </LayoutTemplate>
            
                    <ItemTemplate>
                        <tr>
                            <td><%# Eval("SurveySetName") %></td>
                            <td><%# Eval("SurveySetStatusDesc") %></td>
                            <td><%# Eval("CreateDateTime") %></td>
                            <td><%# Eval("ModifiedDatetime") %></td>
                            <td><%# Eval("SubmittedDateTime") %></td>
                            <td><%# Eval("Time Spent") %></td>
                            <td><asp:HyperLink ID="EditSurveySetHyperLink" runat="server" NavigateUrl='<%# String.Format("~/RegisteredUsers/EditSurveySet.aspx?idSurveySetStatus={0}", Eval("idSurveySetStatus")) %>'>View Details</asp:HyperLink></td>                    
                        </tr>
                    </ItemTemplate>
           
                    <EmptyDataTemplate>
                        <asp:Label ID="EmptyDataLabel" runat="server" Text='You have no survey sets. Click the "Create New Survey Set" button to get started.'></asp:Label>
                    </EmptyDataTemplate>
            
                </asp:ListView>
                </div>
            </LoggedInTemplate>
            
            <AnonymousTemplate>
                    You must be logged in to manage your surveys. Click the Login link in the main 
                    navigation bar to sign in. 
            </AnonymousTemplate> 
            
        </asp:LoginView>
       
    </asp:Content>

    Can someone help with this?

    Thanks
    Thursday, June 2, 2011 10:25 AM

Answers

  • User-1838067034 posted

    You need to replace this:

    <asp:Label ID="SurveySetTotalHours" runat="server" DataSourceID="SqlDataSource2" Text='<%# Eval("Total Time") %>'></asp:Label>

    With this:

    <asp:FormView ID="totalHoursFormView" runat="server" DataSourceID="SqlDataSource2" >
    <ItemTemplate>
    <asp:Label ID="SurveySetTotalHours" runat="server" Text='<%# Eval("Total Time") %>'></asp:Label>
    </ItemTemplate>
    </asp:FormView>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 4, 2011 9:12 AM

All replies

  • User-1838067034 posted

    The Label doesn't have a property called DataSourceID.  To accomplish what you are trying to accomplish you would have to use another databound control to display the data from the SqlDataSource2.  I guess to do what you are doing you could use a formview where that textbox is and the formview can have the datasourceid = SqlDataSource2.

     

    Friday, June 3, 2011 4:32 PM
  • User1426469546 posted

    Hi and thanks for responding to my question.

    I replaced the Label with TextBox but it didn't work.  Here's the line I changed:

    <asp:TextBox ID="SurveySetTotalHours" runat="server" DataSourceID="SqlDataSource2" Text='<%# Eval("Total Time") %>'></asp:TextBox>

    The odd thing is when I run the SQL command manually it works and Visual Studio did not indicate that there might be a problem.

    I'm sure I can get the data using other methods, but I'm really interested in why this seemingly straight-forward way does not work.

    Any other ideas?  Thanks for the help.

    Friday, June 3, 2011 10:44 PM
  • User3866881 posted

    Hello:)

    No you cannot do that. Because a TextBox doesn't support this……

    My suggestion——Remove the whole SqlDataSource2 and in the button's click ,try this:

    using (SqlConnection con = new SqlConnection(……))

    {

         using (SqlCommand cmd = new SqlCommand("SELECT SUM(SurveySetTaskItemDetails.TimeSpent) AS 'Total Time'
                                                  FROM SurveySetTaskItemDetails
                                                  WHERE SurveySetTaskItemDetails.UserId = @MyUser",con))

           {

                    TextBox1.Text = cmd.ExecuteScalar().ToString();

           }

    }

     

    Friday, June 3, 2011 11:40 PM
  • User-1838067034 posted

    You need to replace this:

    <asp:Label ID="SurveySetTotalHours" runat="server" DataSourceID="SqlDataSource2" Text='<%# Eval("Total Time") %>'></asp:Label>

    With this:

    <asp:FormView ID="totalHoursFormView" runat="server" DataSourceID="SqlDataSource2" >
    <ItemTemplate>
    <asp:Label ID="SurveySetTotalHours" runat="server" Text='<%# Eval("Total Time") %>'></asp:Label>
    </ItemTemplate>
    </asp:FormView>
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 4, 2011 9:12 AM
  • User1426469546 posted

    Hi - Thanks for the solution.  That was fantastic.  It worked beautifully.

    Sunday, June 5, 2011 5:15 AM
  • User1426469546 posted

    Hi Decker - Thanks for responding to my question.

    This was the method I alluded to in my previous post as another way to get the value.  Although, a solution was provided by bkotvis, I'm going to try this out  to see if works as well.  Will let you know.

    Sunday, June 5, 2011 5:21 AM