locked
Search parameters not being applied to Gridview.DataBind() RRS feed

  • Question

  • User-1041810558 posted

    I have 2 search boxes and a submit button in an ASPX page.  The page loads and shows all the table data.  I enter the search parameters in textbox1 ("Sname") and textbox2 ("Fname") - on clicking SEARCH button it writes the value of the filter to a new textbox so I can see the parameter query.  It then should apply the query, but instead the gridview refreshes showing all the data.  Can anyone help please?

    Code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    namespace FIRSTTEST
    {
    public partial class MOESearch : System.Web.UI.Page
    {
    public string ConnectionString { get; private set; }
    public string SelectCommand { get; private set; }
    public bool SelectCommandType { get; private set; }
    public int SelectParameters { get; private set; }
    public string PropertyName { get; private set; }
    public int asp { get; private set; }

    protected void Page_Load(object sender, EventArgs e)

    {
    String Sname = txtMOESname.Text;
    String Fname = txtMOEFname.Text;
    String Filter = "";

    if (Sname != "") { Filter = Filter + "Surname like '%" + Sname + "%' and "; }
    if (Fname != "") { Filter = Filter + "Forename like '%" + Fname + "%' and "; }

    if (Filter.Length > 0)

    {
    String FinalFilter = Filter.Remove(Filter.Length - 4, 3);
    TextBox1.Text = FinalFilter;

    SqlDataSourceMOE.FilterExpression = FinalFilter;
    }
    else
    {
    GridView1.DataBind();
    }
    }

    }
    }

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MOESearch.aspx.cs" Inherits="FIRSTTEST.MOESearch" EnableEventValidation="false"%>

    <!DOCTYPE html>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    </head>
    <body style="width: 955px">
    <a href="http://localhost:51537/home.html"> Home </a>
    <br />
    <form id="form1" runat="server">
    <div>
    <br />
    <br />
    Surname&nbsp;&nbsp;&nbsp; <asp:TextBox ID="txtMOESname" runat="server" Width="237px"></asp:TextBox>
    <br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Search" />
    <asp:TextBox ID="TextBox1" runat="server" Width="383px"></asp:TextBox>
    <br />
    Forename&nbsp; <asp:TextBox ID="txtMOEFname" runat="server" Width="236px"></asp:TextBox>
    </div>
    <br />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSourceMOE">
    <Columns>
    <asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
    <asp:BoundField DataField="Forename" HeaderText="Forename" SortExpression="Forename" />
    <asp:BoundField DataField="PrimaryEmail" HeaderText="PrimaryEmail" SortExpression="PrimaryEmail" />
    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSourceMOE" runat="server" ConnectionString="<%$ ConnectionStrings:EverestConnectionString %>" SelectCommand="SELECT * FROM MOEContacts"></asp:SqlDataSource>
    <br />
    </form>
    </body>
    </html>

    Friday, February 28, 2020 3:11 PM

All replies

  • User-1716253493 posted

    Remove any code behind you have then modify sqldatasource like this (no need code behind)

    <asp:SqlDataSource ID="SqlDataSourceMOE" runat="server" ConnectionString="<%$ ConnectionStrings:EverestConnectionString %>" 
        SelectCommand="SELECT * FROM MOEContacts WHERE Surname LIKE '%' + @SName + '%' AND Forename  LIKE '%' + @FName + '%'">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtMOESname" DefaultValue="%" Name="SName" PropertyName="Text" />
            <asp:ControlParameter ControlID="txtMOEFname" DefaultValue="%" Name="FName" PropertyName="Text" />
        </SelectParameters>
    </asp:SqlDataSource>

    Saturday, February 29, 2020 3:15 AM
  • User288213138 posted

    Hi jmshillito,

    on clicking SEARCH button it writes the value of the filter to a new textbox so I can see the parameter query.  It then should apply the query, but instead the gridview refreshes showing all the data

    In your code behind, I did not find the logic code of the button click.

    If you want to parameter query, you can refer to below code:

    Search Customer:
    <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
    <asp:Button Text="Search" runat="server" OnClick="Search"/>
    <hr />
    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" AllowPaging="true"
        OnPageIndexChanging="OnPaging">
        <Columns>
            <asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />
            <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />
            <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
        </Columns>
    </asp:GridView>
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.SearchCustomers();
        }
    }
     
    private void SearchCustomers()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                string sql = "SELECT CustomerId, ContactName, City, Country FROM Customers";
                if (!string.IsNullOrEmpty(txtSearch.Text.Trim()))
                {
                    sql += " WHERE ContactName LIKE @ContactName + '%'";
                    cmd.Parameters.AddWithValue("@ContactName", txtSearch.Text.Trim());
                }
                cmd.CommandText = sql;
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
    
    protected void Search(object sender, EventArgs e)
    {
        this.SearchCustomers();
    }
    
    protected void OnPaging(object sender, GridViewPageEventArgs e)
    {
        gvCustomers.PageIndex = e.NewPageIndex;
        this.SearchCustomers();
    }

    Best regards,

    Sam

    Monday, March 2, 2020 6:29 AM
  • User-1041810558 posted

    Hi - Thanks for the reply.  I tried removing the .cs page and modified the .aspx page as follows - (Rem'd out with the <%--    --%>), your code in green

    <%-- <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MOESearch.aspx.cs" Inherits="FIRSTTEST.MOESearch" EnableEventValidation="false"%>

    <%@ Page Language="C#" AutoEventWireup="true" Inherits="FIRSTTEST.MOESearch" EnableEventValidation="false"%>
    --%>

    <!DOCTYPE html>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    </head>
    <body style="width: 955px">
    <a href="http://localhost:51537/home.html"> Home </a>
    <br />
    <form id="form1" runat="server">
    <div>
    <br />
    <br />
    Surname&nbsp;&nbsp;&nbsp; <asp:TextBox ID="txtMOESname" runat="server" Width="237px"></asp:TextBox>
    <br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
    <asp:Button ID="Button1" runat="server" Text="Search" />
    <asp:TextBox ID="TextBox1" runat="server" Width="383px"></asp:TextBox>
    <br />
    Forename&nbsp; <asp:TextBox ID="txtMOEFname" runat="server" Width="236px"></asp:TextBox>
    </div>
    <br />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSourceMOE">
    <Columns>
    <asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
    <asp:BoundField DataField="Forename" HeaderText="Forename" SortExpression="Forename" />
    <asp:BoundField DataField="PrimaryEmail" HeaderText="PrimaryEmail" SortExpression="PrimaryEmail" />
    </Columns>
    </asp:GridView>
    <%-- <asp:SqlDataSource ID="SqlDataSourceMOE" runat="server" ConnectionString="<%$ ConnectionStrings:EverestConnectionString %>"
    SelectCommand="SELECT * FROM MOEContacts"></asp:SqlDataSource>
    --%>
    <br />
    <asp:SqlDataSource ID="SqlDataSourceMOE" runat="server" ConnectionString="<%$ ConnectionStrings:EverestConnectionString %>"
    SelectCommand="SELECT * FROM MOEContacts WHERE Surname LIKE '%' + @SName + '%' AND Forename LIKE '%' + @FName + '%'">
    <SelectParameters>
    <asp:ControlParameter ControlID="txtMOESname" DefaultValue="%" Name="SName" PropertyName="Text" />
    <asp:ControlParameter ControlID="txtMOEFname" DefaultValue="%" Name="FName" PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>

    </form>
    </body>
    </html>

    I got this error:

    Server Error in '/' Application.


    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    [SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
       System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) +907
       System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +334
       System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +38
       System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +699
       System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +89
       System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +426
       System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +78
       System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +191
       System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +154
       System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +21
       System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +90
       System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +443
       System.Data.SqlClient.SqlConnection.Open() +96
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +120
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +136
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1474
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +22
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
       System.Web.UI.WebControls.GridView.DataBind() +9
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +114
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
       System.Web.UI.Control.EnsureChildControls() +92
       System.Web.UI.Control.PreRenderRecursiveInternal() +42
       System.Web.UI.Control.PreRenderRecursiveInternal() +160
       System.Web.UI.Control.PreRenderRecursiveInternal() +160
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +883

    Sunday, March 8, 2020 8:02 PM
  • User-1041810558 posted

    I have tried to use the example code but get errors;  in the .cs file I began:

    protected void Page_Load(object sender, EventArgs e)
    {
    if (!this.IsPostBack)
    {
    this.SearchMOE();
    }
    }

    private void SearchMOE()
    {
    string constr = ConfigurationManager.ConnectionStrings["EverestConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
    using (SqlCommand cmd = new SqlCommand())

    but get the error at line 1: "A namespace cannot directly contain members such as fields or methods"

    Any help appreciated!

    Sunday, March 8, 2020 8:45 PM
  • User288213138 posted

    Hi jmshillito,

    but get the error at line 1: "A namespace cannot directly contain members such as fields or methods"

    Note that in C#, methods and variables must be declared and defined within a struct or class. For more information on program structure in C#, see the General Structure of a C# Program article. To fix this error, rewrite your code such that all methods and fields are contained within either a struct or a class.

    Best regards,

    Sam

    Monday, March 9, 2020 2:36 AM