Search code examples
gridviewsqldatasourcesqlparameter

SqlDatasource select parameters


Here is my sql datasource details

  <asp:SqlDataSource ID="dsMoodleQuiz" runat="server" 
    ConnectionString="<%$ ConnectionStrings:OnlineMeetingConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:OnlineMeetingConnectionString.ProviderName %>" 

    SelectCommand="SELECT Name, UserID, Grade, FirstName, LastName, Email, TimeModified, IDNumber FROM tbMoodleQuiz WHERE (FirstName = @FirstName) AND (LastName = @LastName)" 
    onselecting="dsMoodleQuiz_Selecting">
    <SelectParameters>
        <asp:Parameter Name="FirstName" />
        <asp:Parameter Name="LastName" />
    </SelectParameters>
</asp:SqlDataSource>

A gridview by name gvDetails is attached to dsMoodleQuiz . On button click I would like

gridview to get populated.

Here is the code on button click

 protected void btnSearch_Click(object sender, EventArgs e)
 {
    dsMoodleQuiz.SelectParameters.Add("@FirstName", System.Data.DbType.String, "Jhon");
    dsMoodleQuiz.SelectParameters.Add("@LastName", System.Data.DbType.String, "Wald");

    GridView1.DataBind();
}

Why is this not working ...?? Am I missing any code ...?? Appreciate the help


Solution

  • This is sample example on how to search grid view and populating results on that search criteria using sqldatasource.....

    grid view binding .....

         <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
    AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10">
    <Columns>
        <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" />
        <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
            <ItemStyle Width="120px" HorizontalAlign="Left" />
            <ItemTemplate>
                <asp:Label ID="lblFirstname" Text='<%# HighlightText(Eval("FirstName")) %>' 
                    runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
            <ItemStyle Width="120px" HorizontalAlign="Left" />
            <ItemTemplate>
                <asp:Label ID="lblLastname" Text='<%# HighlightText(Eval("LastName")) %>' 
                runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Department" HeaderText="Department" 
            SortExpression="Department" ItemStyle-Width="130px" />
        <asp:BoundField DataField="Location" HeaderText="Location" 
            SortExpression="Location" ItemStyle-Width="130px" />
    </Columns>
    </asp:GridView>
    

    and sql datasource is like this ...

       <asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM People"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        FilterExpression="firstname like '%{0}%' or lastname like '%{1}%'">
        <FilterParameters>
            <asp:ControlParameter Name="firstname" ControlID="txtSearch" PropertyName="Text" />
            <asp:ControlParameter Name="lastname" ControlID="txtSearch" PropertyName="Text" />
        </FilterParameters>
      </asp:SqlDataSource>
    

    adding text box for searching ..

        <asp:TextBox ID="txtSearch" runat="server" />
    <asp:ImageButton ID="btnSearch" ImageUrl="images/searchbutton.png" runat="server" />
    <asp:ImageButton ID="btnClear" ImageUrl="images/clearbutton.png" runat="server" />
    

    and this is code for binding and entering text into text box

        using System.Text.RegularExpressions;
    Partial;
    class GridviewwithHighlightedSearch : System.Web.UI.Page {
    
        //  Create a String to store our search results
        private string SearchString = "";
    
        string HighlightText(string InputTxt) {
            //  This function is called whenever text is displayed in the FirstName and LastName 
            //  fields from our database. If we're not searching then just return the original 
            //  input, this speeds things up a bit
            if ((SearchString == "")) {
                return InputTxt;
            }
            else {
                //  Otherwise create a new regular expression and evaluate the FirstName and 
                //  LastName fields against our search string.
                Regex ResultStr;
                ResultStr = new Regex(SearchString.Replace(" ", "|"), RegexOptions.IgnoreCase);
                return ResultStr.Replace(InputTxt, new MatchEvaluator(new System.EventHandler(this.ReplaceWords)));
            }
        }
    
        public string ReplaceWords(Match m) {
            //  This match evaluator returns the found string and adds it a CSS class I defined 
            //  as 'highlight'
            return ("<span class=highlight>" 
                        + (m.ToString + "</span>"));
        }
    
        protected void btnClear_Click(object sender, System.Web.UI.ImageClickEventArgs e) {
            //  Simple clean up text to return the Gridview to it's default state
            txtSearch.Text = "";
            SearchString = "";
            Gridview1.DataBind();
        }
    
        protected void btnSearch_Click(object sender, System.Web.UI.ImageClickEventArgs e) {
            //  Set the value of the SearchString so it gets 
            SearchString = txtSearch.Text;
        }
    }
    

    and this is the css style for hilighting..

    and this is teh image for the above grid view

        <style type="text/css">
       .highlight {text-decoration: none;color:black;background:yellow;}
    </style>
    

    i hope it will helps you...