C# multiple search textbox (error)

Question

1.00/5 (1 vote)

See more:

, +

Dear:
friends

please if you have time to solve my problem
i have many textbox in my form with one button and one datagridview
i use this code to make the search

C#

<pre>DataTable dt = new DataTable();

            
            if (txtCIVILIDD.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from Tabl1 where  CIVILIDD = '" + txtCIVILIDD.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (txtName_Arabic.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where Name_Arabic like '%" + txtName_Arabic.Text + "%'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (txtusername.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from Tabl1 where  username = '" + txtusername.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (comboBox1.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where status = '" + comboBox1.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (comboBox2.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where confirmation = '" + comboBox2.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (CBgender.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where gender like '%" + CBgender.Text + "%'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (CBNATIONALITY.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where NATIONALITY like '" + CBNATIONALITY.Text + "%'", con);
                sda.Fill(dt);
                con.Close();
            }
            else if (comboBoxGovernorate.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where Governorate = '" + comboBoxGovernorate.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();

            }
            else if (comboBoxCity.Text.Length > 0)
            {
                con.Open();
                SqlDataAdapter sda = new SqlDataAdapter("select * from tabl1 where City = '" + comboBoxCity.Text.Trim() + "'", con);
                sda.Fill(dt);
                con.Close();
            }
            dataGridView1.DataSource = dt;
        }

What I have tried:

What if i want to perform a search using values from 2 or more text boxes. what if I typed in "r" in the Name text box then also typed "NY" in the city text box. I want to see the gridview give me the results of that.

that what i try to find and i didn't find anything

the code is working if i search in one textbox only

warm regards

Posted 24-Dec-18 1:10am

el_tot93

Updated 25-Dec-18 0:27am

Add a Solution

3 solutions

Solution 1

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Other than that, it's a case of creating the appropriate query:

C#

string select = "SELECT * FROM Tabl1 WHERE Name_Arabic LIKE '%' + @NM + '%' AND City LIKE '%' + @CT + '%'";

Build it using a StringBuilder to AND conditions together based on what textboxes have content.

Posted 24-Dec-18 1:19am

OriginalGriff

Solution 2

You need to check all your textboxes first. Collect the text entries and build your search statement by combining the values to match the relevant fields in the database. For example you could do something like:

SQL

StringBuilder sqlcommand = "SELECT * FROM tabl1 WHERE ";
if (!string.IsNullOrEmpty(CBgender.Text))
{
    sqlcommand.Append("GENDER LIKE '%");
    sqlcommand.Append(CBgender.Text);
    sqlcommand.Append("%'");
}
// repeat for other textbox fields

When all fields have been checked you submit the command.

[edit]
And as OriginalGriff rightly points out (and I should have): ALWAYS use parameterized queries.
[/edit]

Posted 24-Dec-18 1:23am

Richard MacCutchan

Updated 24-Dec-18 2:36am

v2

Comments

Kraule 24-Dec-18 8:41am

A little change to Solution2:

StringBuilder sqlcommand = "SELECT * FROM tabl1 WHERE 1=1 ";
if (!string.IsNullOrEmpty(CBgender.Text))
{
sqlcommand.Append(" and GENDER LIKE '%");
sqlcommand.Append(CBgender.Text);
sqlcommand.Append("%'");
}
// repeat for other textbox fields

Richard MacCutchan 24-Dec-18 8:47am

Or you could just add the where clause at the first non blank field. Start with a string containing "WHERE ", and when you add it change it to " ".

el_tot93 24-Dec-18 11:34am

why it gave me error in "SELECT * FROM tabl1 WHERE 1=1 "

Richard MacCutchan 24-Dec-18 17:52pm

No idea; what error?

el_tot93 25-Dec-18 0:04am

it gave me red line under "SELECT * FROM tabl1 WHERE 1=1 "; ???????

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

el_tot93 · Accepted Answer · 2018-12-25T00:28:00

i get the solution with this code i hope it help anyone

C#

<pre>private string CreateSqlFilter(string fieldName, Control userInputControl, SqlCommand command, bool exactMatch)
{
    string searchValue = null;
    if (userInputControl is TextBox) searchValue = ((TextBox)userInputControl).Text;
    if (userInputControl is ComboBox) searchValue = ((ComboBox)userInputControl).Text;
    if (String.IsNullOrWhiteSpace(searchValue)) return null;

    if (exactMatch)
    {
        command.Parameters.Add(new SqlParameter("@" + fieldName, searchValue));
        return fieldName + " = @" + fieldName;
    }
    else
    {
        command.Parameters.Add(new SqlParameter("@" + fieldName, "%" + searchValue + "%"));
        return fieldName + " LIKE @" + fieldName;
    }
}

private void button2_Click(object sender, EventArgs e)
{
    SqlCommand selectCommand = new SqlCommand();

    var filterConditions = new[] {
        CreateSqlFilter("Name_Arabic", txtName_Arabic, selectCommand, false),
        CreateSqlFilter("gender", CBgender, selectCommand, false),
        CreateSqlFilter("CIVILIDD", txtCIVILIDD, selectCommand, true),
        CreateSqlFilter("NATIONALITY", cbNationality, selectCommand, false)
        // etc.
    };

    string filterCondition = filterConditions.Any(a => a != null) ? filterConditions.Where(a => a != null).Aggregate((filter1, filter2) => String.Format("{0} AND {1}", filter1, filter2)) : (string)null;

    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myDatabase"].ConnectionString))
    {
        selectCommand.Connection = connection;
        selectCommand.CommandText = filterCondition == null ? "SELECT * FROM tabl1" : "SELECT * FROM tabl1 WHERE " + filterCondition;
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataTable dataSource = new DataTable();
        adapter.Fill(dataSource);
        dataGridView1.DataSource = dataSource;
    }
}

C# multiple search textbox (error)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

C# multiple search textbox (error)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us