ASP.NET Datasource Select Command

Question

I have a local DB table that is displayed in a GridView. In that table, I have a column called: "Completed".

When I first display the table, I am displaying it without completed records (Completed=false). Here is the SqlDataSource select command:

"SELECT * FROM [CERecord] WHERE [Completed]='false' ORDER BY [Priority]";

I have a checkbox which causes postback. I want to toggle the display of Completed records by ticking / unticking it:

protected void cbShowCompletedRecords_CheckedChanged(object sender, EventArgs e)
    {
        if (cbShowCompletedRecords.Checked)
            CEDatabaseSource.SelectCommand = "SELECT * FROM [CERecord] ORDER BY [Priority]";
        else
        {
            CEDatabaseSource.SelectCommand = "SELECT * FROM [CERecord] WHERE [Completed]='false' ORDER BY [Priority]";
        }
    }

Currently, when I check the box, I get all the records. But when I uncheck it, the GridView doesn't update, even though the code above executes. What I am missing?

Try using the Rebind method after the if..else block Grid.Rebind() — Ron
– Ron, Commented Mar 14, 2013 at 14:16
I have already tried CEDatabaseSource.DataBind() and it doesn't change anything. Completed is bool in the db (well bit). — sd_dracula
– sd_dracula, Commented Mar 14, 2013 at 14:20

Josh Darnell · Accepted Answer · 2013-03-14 14:48:29Z

2

Try using your GridViews DataBind method after the if...else block

protected void cbShowCompletedRecords_CheckedChanged(object sender, EventArgs e)
    {
        if (cbShowCompletedRecords.Checked)
            CEDatabaseSource.SelectCommand = "SELECT * FROM [CERecord] ORDER BY [Priority]";
        else
        {
            CEDatabaseSource.SelectCommand = "SELECT * FROM [CERecord] WHERE [Completed]='false' ORDER BY [Priority]";
        }

        yourGrid.DataBind();
    }

This will make sure the results shown in your grid reflect the changes you made to the select statement.

edited Mar 14, 2013 at 14:48

Josh Darnell

11.5k9 gold badges40 silver badges66 bronze badges

answered Mar 14, 2013 at 14:24

Ron

9281 gold badge14 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Darnell Over a year ago

Added some more context, and an explanation of the solution. You should always try to post as complete of an answer as you can. It will help future visitors more, and help you get more upvotes =)

Paolo Tedesco · Accepted Answer · 2013-03-14 14:22:52Z

1

The SqlDataSource allows you to bind parameters to control properties:

<asp:SqlDataSource ID="myds" runat="server" 
  SelectCommand="SELECT * FROM [CERecord] WHERE [Completed]=:COMPLETED ORDER BY [Priority]">
  <SelectParameters>
    <asp:ControlParameter ControlID="MyCheckbox" Name="COMPLETED" PropertyName="Checked" Type="Boolean" />
  </SelectParameters>
</asp:SqlDataSource>

This will modify your select command according to the "Checked" property of your checkbox.

answered Mar 14, 2013 at 14:22

Paolo Tedesco

57.6k34 gold badges153 silver badges199 bronze badges

3 Comments

Josh Darnell Over a year ago

That is not quite the logic the OP is looking for. They either want all records, or just uncompleted records. Very good point about the ControlParameters, though.

Josh Darnell Over a year ago

Also, that syntax looks a little funky. Does [Completed]=:COMPLETED work? I've always used [Completed]=@COMPLETED.

Paolo Tedesco Over a year ago

@jadarnel27: sorry, I misread the question, thanks for pointing this out. By the way, I always used the : syntax and it worked for me...

Eugene Pavlov · Accepted Answer · 2013-03-14 14:23:44Z

-1

If Completed is bit type in DB change your query to:

"SELECT * FROM [CERecord] WHERE [Completed]=0 ORDER BY [Priority]";

answered Mar 14, 2013 at 14:23

Eugene Pavlov

6985 silver badges19 bronze badges

1 Comment

Josh Darnell Over a year ago

This answer doesn't really answer the question, but it is better to practice to write your query this way @sd_dracula.

Collectives™ on Stack Overflow

ASP.NET Datasource Select Command

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related