How to apply National Character set to dynamic query

Question

I am trying to apply N before variable name for Unicode as mentioned in How to use 'LIKE' statement with unicode strings?

With the following code I am getting following error. What need to be corrected here?

Exception: Invalid column name 'N@input'.

string commandText = @"SELECT AccountType,* 
                          FROM Account 
                          WHERE AccountType LIKE N@input ";

CODE

    static void Main(string[] args)
    {
        string result = DisplayTest("Daily Tax Updates:  -----------------        Transactions");

    }

    private static string DisplayTest(string searchValue)
    {
        string test = String.Empty;
        string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string commandText = @"SELECT AccountType,* 
                          FROM Account 
                          WHERE AccountType LIKE N@input ";
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.AddWithValue("@input", "%" + searchValue + "%");

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {

                            test = reader.GetString(0);
                        }
                    }
                }
            }
        }

        return test;

    }

Did your last question not get answered? Or did you just not like the answer? — Kermit, Feb 12 '13 at 16:07
In [this question](http://stackoverflow.com/questions/14836054/use-of-replace-in-sql-query-for-newline-carriage-return-characters) you stated that the data type is `varchar`. Therefore, you don't need the `N` prefix. That said, you shouldn't need it in this case anyway, since .NET will pass it as Unicode by default. Can you try to limit questions to problems you're actually trying to solve? — Aaron Bertrand, Feb 12 '13 at 16:12

score 4 · Answer 1 · answered Feb 12 '13 at 16:09

I see a few issues.

   string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE N@input";

should be

   string commandText = @"SELECT AccountType,* 
                              FROM Account 
                              WHERE AccountType LIKE @input";

...

 command.Parameters.Add("@input",System.Data.SqlDbType.NVarChar,<<size>>);
 command.Parameters[0].Value = "%" + searchValue + "%";

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

Taken from this stack Stack overflow

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;

edited May 23 '17 at 12:12

Community

1
1

answered Feb 12 '13 at 16:03

CR41G14

5,464
5
43
64

1

size the parameter to prevent "issues" – swasheck Feb 12 '13 at 16:11

score 1 · Accepted Answer · answered Feb 12 '13 at 16:08

I see you're trying to use a nvarchar parameter. I think .net does that by default with .AddWithValue

I'm not sure why do you need the typecast to nvarchar, you should be fine without the 'N' part.

That part you need when you want to specify that a string literal should be treated as nvarchar not as varchar, as in SELECT * from Table where field like N'%VALUE%'

Otherwise, you just declare your variable/parameter as nvarchar

score 0 · Answer 4 · answered Apr 24 '13 at 05:24

Try this one -

private static string DisplayTest(string searchValue)
{
    string connectionString = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string commandText = @"SELECT AccountType,* FROM Account WHERE AccountType LIKE @input";

        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            command.CommandType = System.Data.CommandType.Text;

            command.Parameters.Add("@input", SqlDbType.NVarChar);
            command.Parameters["@input"].Value = string.Format("%{0}%", searchValue);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        return reader.GetString(0);
                    }
                }
            }
        }
    }

    return String.Empty;
}

How to apply National Character set to dynamic query

4 Answers4