Click here to Skip to main content
15,897,273 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
now i use thx code to make the filter in datagridview i want to add 2 textbox to make a filter between age like between 20 and 30 how i can do that i try muny thing bout it didn't work with me


C#
private void Button1_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("status", comboBox1, selectCommand, false),
        CreateSqlFilter("username", txtusername, selectCommand, false),
        CreateSqlFilter("City", comboBoxCity, selectCommand, false),
        CreateSqlFilter("Governorate", comboBoxGovernorate, selectCommand, false),
        CreateSqlFilter("confirmation", comboBox2, selectCommand, false),
        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["my"].ConnectionString))
            {
                selectCommand.Connection = connection;
                selectCommand.CommandText = filterCondition == null ? "SELECT * FROM tabl2" : "SELECT * FROM tabl2 WHERE " + filterCondition;
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                DataTable dataSource = new DataTable();
                adapter.Fill(dataSource);
                dataGridView1.DataSource = dataSource;
            }
        }

        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;
            }
        }


What I have tried:

look i try this and it work good bout what i want to i want to merge it with my frist code to make it work in one button

C#
SqlConnection con = new SqlConnection("Data Source=DESKTOP-J7D5POF;Initial Catalog=ilswork;Persist Security Info=True;User ID=****;Password=*****;connect timeout=900");
           SqlDataAdapter sdf = new SqlDataAdapter("select * from tabl2 where age between'" + txt1.text + "'and'" + txt2.text + "'", con);
           DataTable sd = new DataTable();
           sdf.Fill(sd);
           dataGridView1.DataSource = sd;
Posted
Updated 8-Jan-19 22:26pm
v3

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?
 
Share this answer
 
Comments
Maciej Los 9-Jan-19 4:29am    
5ed!
Quote:
a filter between age like between 20 and 30 how i can do that i try muny thing bout it didn't work with me


Take a look at your sql command:
SQL
select * from tabl2 where age between'" + txt1.text + "'and'" + txt2.text + "'"

Imagine, when you pass values into it, it will take a form:
SQL
select * from tabl2 where age between'20'and'30'"


I see 2 issues:
1. there's no space between values and parts of where statement:
SQL
between[here]'20'[here]and[here]'30'

2. passed values not match to its original data type!
A [20] (integer value) is not equal to a ['20'] (string value)!

OriginalGriff have already pointed you out how to fix that!
 
Share this answer
 
Comments
el_tot93 9-Jan-19 4:25am    
sir the code work bout i want to merge it with my frist code
Maciej Los 9-Jan-19 4:31am    
Are you sure? You mentioned earlier that it isn't work.
el_tot93 9-Jan-19 4:37am    
no i didn't say that the problem here to merge thim
Maciej Los 9-Jan-19 4:42am    
Merge what?
I don't get you...
el_tot93 9-Jan-19 4:48am    
look to my frist code it make a filter in the datagridview i want to add the command the the saceand code to my first one

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900