programmaticly alter the command text of a sqldatasource???anyone?

[ricksvoid]

I have a simple sqlDataSource that populates a GridView control
Nothing out of the ordinary here.

<asp:SqlDataSource ID="personalInfoSource" runat="server" ConnectionString="<%$ appSettings:Maya%>"
                            SelectCommand="SELECT customerID, lname, fname, dateNow FROM dbo.customers ORDER BY lname ASC">
                        </asp:SqlDataSource>

                   <asp:GridView ID="names" runat="server" Width="280px" DataSourceID="personalInfoSource"
                                   AutoGenerateColumns="False" DataKeyNames="customerID" AllowSorting="True">
                            <RowStyle BackColor="#FFBA00" Font-Names="arial" ForeColor="#26354a" />
                            <AlternatingRowStyle BackColor="#D3DCE6" Font-Names="arial" ForeColor="#26354a"/>
                            <HeaderStyle BackColor="#D3DCE6" ForeColor="#26354a" Font-Names="arial" />
                            <Columns>
                                <asp:BoundField DataField="customerID" HeaderText="Id" SortExpression="customerID"/>
                                <asp:BoundField DataField="lname" HeaderText="Last Name" />
                                <asp:BoundField DataField="fname" HeaderText="First Name" />
                                <asp:BoundField DataField="dateNow" HeaderText="Id" Visible="false"/>
                             
                                <asp:CommandField ShowSelectButton="true" ButtonType="Button" 
                                    SelectText="Select" ItemStyle-HorizontalAlign="left"
                                    HeaderText="Details" >
                                <ItemStyle HorizontalAlign="Left" />
                                </asp:CommandField>
                            </Columns>
                        </asp:GridView>

The problem is that my client wants to be able to click a button and refine the querry by only showing certain records instead of all of them. Can i do this programmaticaly? Can I programaidically change the commandtext so that it says :
SELECT customerID, lname, fname, dateNow FROM dbo.customers WHERE rep is null> ORDER BY lname ASC

I beleive that i can but im unsure of what the proper syntax is. Remember, I want the gridview to display all records by defalut and only filter them as the result of some event such as a button click.

Thanks in advance.
Rick Pace

[Ramesh S]

Try like this.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlDataSource1.SelectCommand = "SELECT customerID, lname, fname, dateNow FROM dbo.customers ORDER BY lname ASC";
        }
    }
    protected void SqlDataSource1_Init(object sender, EventArgs e)
    {

        SqlDataSource1.SelectCommand = String.Empty;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        SqlDataSource1.SelectCommand = "SELECT customerID, lname, fname, dateNow FROM dbo.customers WHERE rep is null ORDER BY lname ASC";

    }

[ricksvoid]

Yes, thanks Ramesh. This worked great.

[Ramesh S]

Hi Rick Pace,

Please mark this thread solved if your question is answered.

Regards
Ramesh S