ASP.NET

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

Please support our ASP.NET advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: May 2008

Posts: 35

Reputation: ricksvoid is an unknown quantity at this point

Solved Threads: 0

ricksvoid

Offline

Light Poster

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

Oct 30th, 2009

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
                        <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>
                        <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

Last edited by Ezzaral; Oct 30th, 2009 at 4:15 pm. Reason: Snipped url. Please restrict such links to your site-wide user signature, which can be edited from the user control panel.

•

Join Date: Jun 2009

Posts: 452

Reputation: Ramesh S will become famous soon enough

Solved Threads: 82

Ramesh S

Offline

Posting Pro in Training

Nov 3rd, 2009

Try like this.

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
   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";
 
    }
   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";

    }