like statement for npgsql using parameter

Question

I have a postgresql DB and i want to query the table "Locations" to retrieve the names of all the locations that match the name that's entered by the user. The column name is "LocationName". I'm using ASP.net with C#.

NpgsqlConnection con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ToString());
NpgsqlCommand cmd = new NpgsqlCommand("Select * from \"Locations\" where \"LocationName\" LIKE \"%@loc_name%\"", con);
cmd.Parameters.AddWithValue("@loc_name", Location_Name);
NpgsqlDataReader reader = cmd.ExecuteReader();

I get this exception:

Npgsql.NpgsqlException: ERROR: 42703: column "%((E'My place'))%" does not exist

I've tried running the query without using %, but it doesn't work. I've also tried using + and & like given below but that didn't work either:

string query = "Select \"LocationName\" from \"Locations\" where \"LocationName\" LIKE '%'+ :loc_name +'%'";

with the above line, i get this exception:

Npgsql.NpgsqlException: ERROR: 42725: operator is not unique: unknown + unknown

Solution

you should use

NpgsqlCommand cmd = new NpgsqlCommand("Select * from \"Locations\" where \"LocationName\" LIKE @loc_name", con);
cmd.Parameters.AddWithValue("@loc_name", "%" + Location_Name + "%");

you were inserting too much quotes: Postgre interpretes the string between double quote as a field/table-name. Let the parameter do the escape-string job

P.S.: To concatenate string in Postgre you should use the || operator, see here. So your last query should be

string query = "Select \"LocationName\" from \"Locations\" where \"LocationName\" LIKE '%' || :loc_name || '%'";

Comments

[NamrataP]

It worked!! Now its returning all the matching rows. Thanks @trippino for the help and for such a quick response.

[Tobia Zambon]

Glad to help you. Welcome in stack overflow :)