GridView is empty

Question

I have a page (which has a MasterPage) with a GridView and an SqlDataSource. If I set SelectCommand in the markup, the GridView is displayed fine. But if I generate the same SelectCommand dynamically, the GridView is empty. And I know that the query is executed. What can be the reason for it? Thanks.

<asp:SqlDataSource ID="UserSqlDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:BookList %>" DataSourceMode="DataSet" 
    ProviderName="<%$ ConnectionStrings:BookList.ProviderName %>" >
</asp:SqlDataSource>  

        <asp:GridView ID="grdUsers" runat="server" AllowPaging="True" ShowHeader="false" ShowFooter="true" 
            AutoGenerateColumns="false"
            Width="1480px" Height="100%" PageSize="50" DataSourceID="UserSqlDataSource" DataKeyNames="ID">
                <AlternatingRowStyle CssClass="alternatingrowstyle" />
                <Columns>
                     <asp:TemplateField HeaderText="User Name" SortExpression="Name">
                        <ItemTemplate>
                            <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
                        <ItemTemplate>
                            <asp:Label ID="lblLastName" runat="server" Text='<%# Eval("LastName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>                        
                    <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
                        <ItemTemplate>
                            <asp:Label ID="lblFirstName" runat="server" Text='<%# Eval("FirstName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Email" SortExpression="Email">
                        <ItemTemplate>
                            <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>                        

                </Columns>

            </asp:GridView>

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindUsers();
    }
}

protected void BindUsers()
{
    string strSelectClause = 
        @"SELECT ID, Name, FirstName, LastName, EMail, 
      FROM User WHERE Name NOT IN ('*All', 'Admin', 'TPS') ";

    string strQuery = strSelectClause + GenerateWhereClause() + " ORDER BY Name;";

    SqlDataSource UserSqlDataSource = GetControl(this, "UserSqlDataSource") as SqlDataSource;
    UserSqlDataSource.SelectCommand = strQuery;
    grdUsers.DataBind();
}

score 1 · Answer 1 · answered Apr 08 '11 at 14:24

1

User is a reserved keyword, have you tried:

SELECT ID, Name, FirstName, LastName, EMail, 
      FROM [User] WHERE Name NOT IN ('*All', 'Admin', 'TPS') ";

answered Apr 08 '11 at 14:24

Ta01

31,040
13
70
99

No, it is not the problem. As I said, the GridView is populated with this very SelectCommand if it is set in markup. And I tried the query in QueryBrowser successfully. Anyway, I tried to make this change, and it didn't help. – dpreznik Apr 08 '11 at 14:32

score 1 · Answer 2 · answered Apr 08 '11 at 14:30

1

From the MSDN documentation for the SqlDataSource control:

Note
By default, if one of the parameters is null when you execute a Select command, no data will be returned and no exception will be thrown. You can change this behavior by setting the CancelSelectOnNullParameter property to false.

answered Apr 08 '11 at 14:30

Joel C

5,547
1
21
31

Thank you Joel. Unfortunately, this didn't help, either :( – dpreznik Apr 08 '11 at 14:37
Almost 4 years later and this has helped me. I have a stored procedure and the URL for it may not have all the parameters. Apparently the CancelSelectOnNullParameter property was getting in the way. – pixelmeow Jan 12 '15 at 19:42

score 1 · Accepted Answer · answered Apr 08 '11 at 15:23

1

It worked when I removed DataSourceID from GridView's markup and added grdUsers.DataSource = UserSqlDataSource; in code behind. I wonder why it didn't work the way it was before...

answered Apr 08 '11 at 15:23

dpreznik

247
5
18

1

How about marking as correct answer, or editing the question? – Ryan Leonard Apr 09 '11 at 02:06
Click on the check next to the answer. (under the answer's score) You should always mark an answer as accepted (assuming there is one). – Ryan Leonard Apr 11 '11 at 20:53

score 0 · Answer 4 · answered Apr 08 '11 at 14:55

0

Maybe you set the SelectCommand too late in the page life cycle? Do you call grdUsers.DataBind() after setting the SelectCommand?

answered Apr 08 '11 at 14:55

Varga Tamas

66
4

Yes, it is in the code I provided in my question. I don't know how it could be late, if it is in the Page_Load(). – dpreznik Apr 08 '11 at 15:10

GridView is empty

4 Answers4

Linked