I'm trying to search for records in my VSC database through the SQL connection string and display records based on the user's input. I seem to be hitting a wall with the tutorials I've found on how to get things running. At the moment this code is throwing an exception with sda.Fill(dt);. Could anyone point out where I'm going wrong?

protected void GoButton_Click(object sender, EventArgs e)
        {
            SqlCommand command;
                SqlConnection conn;
            String selectTable;

            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            conn.Open();

            selectTable = "SELECT * from Activity where ActivityName LIKE '%' @ActivityName '%'";

            command = new SqlCommand(selectTable, conn);
            command.Parameters.AddWithValue("ActivityName", SearchBox);
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(command);
            sda.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();


            command.Dispose();

            conn.Close();

CodePudding user response：

You missed .Text on SearchBox that's why it won't come and i revamped your code use code always like this

protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        try
        {
            var constring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataTable dt = new DataTable();

            using (con = new SqlConnection(constring))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }

                using (cmd = new SqlCommand("SELECT * from Activity where ActivityName LIKE '%' @ActivityName '%'", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("ActivityName", SearchBox.Text);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        sda.Fill(dt);

                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
            con.Close();
        }
    }

CodePudding user response：

Ok, the approach to adding filter (1 or many) can be attacked like this:

markup:

        <h4>Filters</h4>
        <div style="float:left">
            <asp:Label ID="Label1" runat="server" Text="Search Hotel"></asp:Label>
            <br />
             <asp:TextBox ID="txtHotel" runat="server"></asp:TextBox>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label2" runat="server" Text="Search City"></asp:Label>
            <br />
            <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label3" runat="server" Text="Must Have Description"></asp:Label>
            <br />
            <asp:CheckBox ID="chkDescripiton" runat="server"  />
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Label ID="Label4" runat="server" Text="Show only Active Hotels"></asp:Label>
            <br />
            <asp:CheckBox ID="chkActiveOnly" runat="server"  />
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Button ID="cmdSearch" runat="server" Text="Search" CssClass="btn" OnClick="cmdSearch_Click"/>
        </div>

        <div style="float:left;margin-left:20px">
            <asp:Button ID="cmdClear" runat="server" Text="Clear Fitler" CssClass="btn" OnClick="cmdClear_Click"/>
        </div>



        <div style="clear:both">
                <%-- this starts new line for grid --%>
        </div>

        <asp:GridView ID="GridView1" runat="server" 
            AutoGenerateColumns="False" DataKeyNames="ID" 
            CssClass="table" Width="60%" ShowHeaderWhenEmpty="true">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName"  />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="Province" HeaderText="Province" />
                <asp:BoundField DataField="Description" HeaderText="Description"  />
            </Columns>
        </asp:GridView>
    </div>

Now code to load grid is this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack == false)
        {
            SqlCommand cmdSQL = new
                SqlCommand("SELECT * FROM tblHotels WHERE ID = 0");

            LoadGrid(cmdSQL);
        }
    }

    public void LoadGrid(SqlCommand cmdSQL)
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (cmdSQL)
            {
                cmdSQL.Connection = conn;
                DataTable rstData = new DataTable();
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
                GridView1.DataSource = rstData;
                GridView1.DataBind();
            }
        }
    }

And now we have this:

Now, you might just have ONE filter - one text box. But here is the code and approach for several filters like I have in above.

So, follow this template and design pattern:

    protected void cmdSearch_Click(object sender, EventArgs e)
    {
        string strSQL = "SELECT * FROM tblHotels ";
        string strORDER = " ORDER BY HotelName";
        string strFilter = "";

        SqlCommand cmdSQL = new SqlCommand();

        if (txtHotel.Text != "")
        {
            strFilter = "(HotelName like @HotelName   '%')";
            cmdSQL.Parameters.Add("@HotelName", SqlDbType.NVarChar).Value = txtHotel.Text;
        }

        if (txtCity.Text != "")
        {
            if (strFilter != "") strFilter  = " AND ";
            strFilter  = "(City Like @City   '%'";
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = txtCity.Text;
        }

        if (chkActiveOnly.Checked)
        {
            if (strFilter != "") strFilter  = " AND ";
            strFilter  = "(Active = 1)";
        }
        if (chkDescripiton.Checked)
        {
            if (strFilter != "") strFilter  = " AND ";
            strFilter  = "(Description is not null)";
        }

        if (strFilter != "") strSQL  = " WHERE "   strFilter;

        strSQL  = strORDER;
        cmdSQL.CommandText = strSQL;
        LoadGrid(cmdSQL);

    }

So, we can have with above 1, 2 or 9 filter options.

And our clear filter button code

    protected void cmdClear_Click(object sender, EventArgs e)
    {
        txtCity.Text = "";
        txtHotel.Text = "";
        chkActiveOnly.Checked = false;
        chkDescripiton.Checked = false;
        SqlCommand cmdSQL = new 
            SqlCommand("SELECT * FROM tblHotels ORDER BY HotelName");
        LoadGrid(cmdSQL);
    }

So, I can for example type in K to find all hotels that start with K.

and we get this: