Parameter act the same as regular string

Question

When I type ' in my TextBox, it's resulting an error. I know that because the ' is part of SQL Query. How do I avoid that? I've done it with Parameter, but didn't even work.

    private void theFilter(string FilterValue) {
        string thisQuery = "SELECT * FROM [Customer] WHERE CONCAT([Name], [Address], [Discount]) LIKE '%" + @FilterValue + "%'";
        using(SqlConnection thisSqlConnection = new SqlConnection(theConnectionString))
        using(SqlCommand thisSqlCommand = new SqlCommand(thisQuery, thisSqlConnection)) {
            thisSqlCommand.Parameters.AddWithValue("@FilterValue", FilterValue);
            using(SqlDataAdapter thisSqlDataAdapter = new SqlDataAdapter(thisSqlCommand))
            using(DataTable thisDataTable = new DataTable()) {
                thisSqlDataAdapter.Fill(thisDataTable);
                DataGrid_Customer.ItemsSource = thisDataTable.DefaultView;
            }
        }
    }

Validate your TextBox. – ahmed abdelqader Jun 10 '17 at 13:30 — ahmed abdelqader, Jun 10 '17 at 13:30
so where is the issue, you are not one from anyone ? – ahmed abdelqader Jun 10 '17 at 13:38 — ahmed abdelqader, Jun 10 '17 at 13:38
ha! see below, they got the answer! – Lem Bidi Jun 10 '17 at 13:39 — Lem Bidi, Jun 10 '17 at 13:39
Nice to hear getting accepted answer for you. – ahmed abdelqader Jun 10 '17 at 13:41 — ahmed abdelqader, Jun 10 '17 at 13:41

Errol · Accepted Answer · 2017-06-10T13:42:04.003

3

Try using this one

private void theFilter(string FilterValue) {
    string thisQuery = "SELECT * FROM [Customer] WHERE CONCAT([Name], [Address], [Discount]) LIKE @FilterValue";
    using(SqlConnection thisSqlConnection = new SqlConnection(theConnectionString))
    using(SqlCommand thisSqlCommand = new SqlCommand(thisQuery, thisSqlConnection)) {
        thisSqlCommand.Parameters.AddWithValue("@FilterValue", "%" + FilterValue + "%");
        using(SqlDataAdapter thisSqlDataAdapter = new SqlDataAdapter(thisSqlCommand))
        using(DataTable thisDataTable = new DataTable()) {
            thisSqlDataAdapter.Fill(thisDataTable);
            DataGrid_Customer.ItemsSource = thisDataTable.DefaultView;
        }
    }
}

Remember that you are using strings in parameters, not variables. You have to indicate the '%' in the AddWithValue method to be able to use wildcards in your query.

edited Jun 10 '17 at 13:42

answered Jun 10 '17 at 13:32

Errol

46
2

Nope, it didn't work. it is the same as `LIKE '% 'String' %'` – Lem Bidi Jun 10 '17 at 13:33
What is the value of FilterValue you are passing in to test this @LemBidi? Because this code *should* work. – mjwills Jun 10 '17 at 13:41
@ahmedabdelqader it was a coincidence, calm down. – Errol Jun 10 '17 at 13:42
@LemBidi, Nope .. mjwills is the first Man :) choose what you want form to be accepted answers, just i ask you to be fair as you can, thanks. – ahmed abdelqader Jun 10 '17 at 13:44
see this: https://scontent-sit4-1.xx.fbcdn.net/v/t1.0-9/18950944_692523547612123_8805256223480799953_n.jpg?oh=633cb17dc61e0049793433aaa4cfc963&oe=59DD9111 – Lem Bidi Jun 10 '17 at 13:46
He beat me to it fair and square. He did nothing wrong. I hope you find my SqlLikeEscape function useful though. – mjwills Jun 10 '17 at 13:47
Thanks @LemBidi, I love evidence and screenshots, voted it up. – ahmed abdelqader Jun 10 '17 at 13:49

mjwills · Answer 2 · 2017-06-10T13:52:16.917

3

private void theFilter(string FilterValue) {
    string thisQuery = "SELECT * FROM [Customer] WHERE CONCAT([Name], [Address], [Discount]) LIKE @FilterValue";
    using(SqlConnection thisSqlConnection = new SqlConnection(theConnectionString))
    using(SqlCommand thisSqlCommand = new SqlCommand(thisQuery, thisSqlConnection)) {
        thisSqlCommand.Parameters.AddWithValue("@FilterValue", "%" + SqlLikeEscape(FilterValue) + "%");
        using(SqlDataAdapter thisSqlDataAdapter = new SqlDataAdapter(thisSqlCommand))
        using(DataTable thisDataTable = new DataTable()) {
            thisSqlDataAdapter.Fill(thisDataTable);
            DataGrid_Customer.ItemsSource = thisDataTable.DefaultView;
        }
    }
}

// This function is important, since otherwise if there is a % in the table (or other special LIKE characters) then it is hard to search for them
//see https://stackoverflow.com/questions/18693349/how-do-i-find-with-the-like-operator-in-sql-server
public static string SqlLikeEscape(string value)
{
    if (string.IsNullOrEmpty(value)) return value;
    return Regex.Replace(value, @"(?<ch>%|_|\[)", @"[${ch}]");
}

edited Jun 10 '17 at 13:52

answered Jun 10 '17 at 13:38

mjwills

23,389
6
40
63

sorry but his "EDIT" history was the FIRST than yours "CREATE COMMENT" – Lem Bidi Jun 10 '17 at 13:43
No problem at all! I have added my SqlLikeEscape function - I hope that is useful to you! – mjwills Jun 10 '17 at 13:45
https://scontent-sit4-1.xx.fbcdn.net/v/t1.0-9/18950944_692523547612123_8805256223480799953_n.jpg?oh=633cb17dc61e0049793433aaa4cfc963&oe=59DD9111 – Lem Bidi Jun 10 '17 at 13:46
I'll give you +1 – Lem Bidi Jun 10 '17 at 13:46
1

Thanks, that was so useful, prevent further errors. – Lem Bidi Jun 10 '17 at 13:51

Parameter act the same as regular string

2 Answers2