As an expression for npgsql using parameter

Question

I have postgresql DB and I want to query the "Locations" table to get the names of all places that match the name entered by the user. The column name is "LocationName". I am 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 tried to execute the request without using%, but it does not work. I also tried using + and and as below, but that didn't work either:

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

with the line above, I get this exception:

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

Answer 1

you should use

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

You inserted too many quotes: Postgre interprets the line between the double quote as field / table-name. Let the parameter do the escape line job

PS: To concatenate a string in Postgre, you must use the || see here . So your last request should be

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