Trying To Make Search Database by UserName in textbox by Paramaters and Oledb

Question

Hey guys i am working on a Search button that will search Users in database by their UserName or Their Cities Or anything else. I Tried to make a paramater of Username That inside i putted a Textbox and if he write something in the Textbox and click On the button it will show only the users with the UserName that he wrote inside the textbox... But its not working guys, Please help me to find out how to make it...

Codes:

1 HTML(VeryShort):

            <asp:TextBox ID="txtUserNameFooter" runat="server" />
        <asp:Button ID = "SearchButton" Text = "חפש" runat="server" onclientclick="SearchButton_Click" onclick="SearchButton_Click"  />

2 Asp.net (C#) Database

    protected void SearchButton_Click(object sender, EventArgs e)
{
    using (OleDbConnection sqlCon = new OleDbConnection(connectionStr))
    {
        sqlCon.Open();
        string query = "SELECT * FROM Users WHERE UserName=@UserName";
        OleDbCommand sqlCmd = new OleDbCommand(query, sqlCon);
        sqlCmd.Parameters.AddWithValue("@UserName", txtUserNameFooter.Text);
        sqlCmd.ExecuteNonQuery();
        PopulateGridView();
        sqlCon.Close();
    }
}

3 PopulateGridView Code

 void PopulateGridView()
{
    DataTable dtbl = new DataTable();
    using (OleDbConnection sqlCon = new OleDbConnection(connectionStr))
    {
        sqlCon.Open();
        OleDbDataAdapter sqlDa = new OleDbDataAdapter("SELECT * FROM Users", sqlCon);
        sqlDa.Fill(dtbl);
    }
    if (dtbl.Rows.Count > 0)
    {
        AdminBook.DataSource = dtbl;
        AdminBook.DataBind();
    }
    else
    {
        dtbl.Rows.Add(dtbl.NewRow());
        AdminBook.DataSource = dtbl;
        AdminBook.DataBind();
        AdminBook.Rows[0].Cells.Clear();
        AdminBook.Rows[0].Cells.Add(new TableCell());
        AdminBook.Rows[0].Cells[0].ColumnSpan = dtbl.Columns.Count;
        AdminBook.Rows[0].Cells[0].Text = "No Data Found";
        AdminBook.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
    }
}

Picture of The Site Click Here to Look for it

I want it to make when you write in textbox and press on button it will Search if their a username called like the textbox and then if it have it will show only the data of this username... Please guys help me i will be really happy to get help :)

Answer 1

Update PopulateGridView method to accept an input parameter like this:

void PopulateGridView(string searchTerm)
{
    DataTable dtbl = new DataTable();
    using (OleDbConnection sqlCon = new OleDbConnection(connectionStr))
    {

        string query = "SELECT * FROM Users";

       OleDbCommand sqlCmd = new OleDbCommand(query, sqlCon);

       if(!String.IsNullOrWhiteSpace(searchTerm)) {
                 query+=" WHERE UserName=@UserName";
                 sqlCmd.CommandText = query;
                sqlCmd.Parameters.AddWithValue("@UserName", searchTerm);
         }

        sqlCon.Open();
        OleDbDataAdapter sqlDa = new OleDbDataAdapter(sqlCmd);

        sqlDa.Fill(dtbl);
    }

    if (dtbl.Rows.Count > 0)
    {
        AdminBook.DataSource = dtbl;
        AdminBook.DataBind();
    }
    else
    {
        dtbl.Rows.Add(dtbl.NewRow());
        AdminBook.DataSource = dtbl;
        AdminBook.DataBind();
        AdminBook.Rows[0].Cells.Clear();
        AdminBook.Rows[0].Cells.Add(new TableCell());
        AdminBook.Rows[0].Cells[0].ColumnSpan = dtbl.Columns.Count;
        AdminBook.Rows[0].Cells[0].Text = "No Data Found";
        AdminBook.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
    }
}

Update SearchButton_Click method like this:

protected void SearchButton_Click(object sender, EventArgs e)
{
   PopulateGridView(txtUserNameFooter.Text);        
}