Change Gridview Datasource or Select statement ijn code?

Question

I have a gridview with sqldatasource, etc. For the grid, I also have a search textbox. If the user needs to filter the records, I want to dynamically adjust the SELECT statement for the SqlDataSource using a sql procedure with a parametre can take the value from the textbox . I like all the automatic capabilities for paging, sorting, etc., so I don't want to just bind the old way.

Any clues?

thanks,

 <form id="form1" runat="server">
    <div>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
            SelectCommand="SelectCategorie" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="CategorieName" 
                    PropertyName="Text" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
            SelectCommand="SELECT [ProductName], [ProductID] FROM [Alphabetical list of products]">
        </asp:SqlDataSource>
        <br />
        <br />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <asp:Button ID="Button3" runat="server" Text="Button" />
        <br />
        <br />
        <asp:DropDownList ID="DropDownList1" runat="server" 
            DataSourceID="SqlDataSource3" DataTextField="ProductName" 
            DataValueField="ProductName">
        </asp:DropDownList>
        <br />
        <br />
                <asp:Button ID="Button2" runat="server" Text="Change the datasource" />
        <br />
        <br />
                <asp:GridView ID="GridView1" runat="server" 
                    DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
                    <Columns>
                        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
                            InsertVisible="False" ReadOnly="True" SortExpression="ProductID" 
                            DataFormatString="[Yassine {0}]" />
                        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
                            SortExpression="ProductName" />
                        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
                            SortExpression="SupplierID" />
                        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
                            SortExpression="CategoryID" />
                        <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" 
                            SortExpression="CategoryName" />
                        <asp:BoundField DataField="Description" HeaderText="Description" 
                            SortExpression="Description" />
                        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" 
                            SortExpression="QuantityPerUnit" />
                        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
                            SortExpression="UnitPrice" />
                        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
                            SortExpression="UnitsInStock" />
                        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" 
                            SortExpression="UnitsOnOrder" />
                        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" 
                            SortExpression="ReorderLevel" />
                        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" 
                            SortExpression="Discontinued" />
                    </Columns>
                </asp:GridView>
        <br />
        <br />

    </div>
    </form>

and the code behind the scene :

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        SqlDataSource1.SelectCommand = "SelectCategorieParDescription"
        SqlDataSource1.SelectParameters.Add("@ProductName", DropDownList1.DataValueField)
    End Sub
End Class

and this is the erreur i got :

Procedure or function SelectCategorieParDescription has too many arguments specified

I believe in the designer view you have already defined SqlDataSource1 with a parameter. in your Button2 click event you are changing the select command and adding a parameter. since your data source is the same the previous parameter is retained. Hence you get the too many arguments error. — Prashanth Thurairatnam, May 08 '12 at 23:36
I've put some of my thoughts on approaching this. check my answer; hope it helps you with finding a solution. — Prashanth Thurairatnam, May 09 '12 at 00:18

score 1 · Accepted Answer · answered May 08 '12 at 22:45

We use a similar approach on what you want to do. The hard way is the following (you can see an article about this here:

Create a grammar for your search syntax
Create a parser for that grammar
Create an interpreter along with the parser that will convert your search syntax into a SQL (the WHERE part)
Create a stored procedure that can take this whereSql string and concatenate with the complete query and call EXEC(@sqlQuery)

However, all this might take you a while, another option would be to restraint your search capabilities to single strings. Example:

Say you have your query with the possibility to search on 2 columns: name, socsecNumber
Whenever you read the string john you convert this to: name like '%john%' or (convert socsecNumber to string) like '%john%'
Up to you to use the % all the time or maybe only when the user's input is something like: joh*

The stored procedure receiving the where query is a little bit like this:

ALTER PROCEDURE [dbo].[usp_SearchForObjectsForDashboard]
    @searchTerm as varchar(250)
BEGIN
    ...
    SET @sql = 'SELECT ...
        ...
        WHERE someConditionOfYourOwn = whatever
         AND ' + @searchTerm

    EXEC(@sql) 
END

Hope it helps

Prashanth Thurairatnam · Answer 2 · 2012-05-09T00:23:28.367

I will try to elaborate as much as I can. Providing a coding solution for your question is little bit beyond the scope. I will provide some of my thoughts on how to approach this. Hope others too would add some valuable points to this.

Search can be implemented in several ways. It all depends on your requirements and the efficiency of the search. From your question I believe what you are trying achive is to have a search and several filtering options

Say for example say you want to search employees (and say your want to search by firstname/ lastname/ department/ manager)

To start simple you can have (in addition to the GridView & SqlDataSource)

1) a textbox for search box.

2) a search by option (either 4 RadioButtons or a DropDownList with firstname, lastname, department, manager)

3) a button

4) a stored procedure with two parameters (searchText, searchBy)

in your stored procedure depending on your SearchBy type you can construct your select search query by searchText

pseudo code

if (searchBy == firstname)
{
    set @sql = select ...... where firstName = searchText
}
else if (searchBy == lastname)
{
    set @sql = select ...... where lastName = searchText
}
else if (searchBy == department)
{
    set @sql = select ...... where department = searchText
}
else if (searchBy == manager)
{
    set @sql = select ...... where manager = searchText
}

exec(@sql)

in your button_click you will change the sqlDataSource Select Parameters searchText from the TextBox searchBy from DropDownList or RadioButton

Again this is minimalisting. You can extend this based on needs. For example

1) using like clause in the select queries to get similar matches rather than exact matches (in the UI you can give user the option of choosing between exact, similar match)

2) You can give user the option of searching in multiple columns

and so forth (options are endless; again it depends on your requirements)

Change Gridview Datasource or Select statement ijn code?

2 Answers2