like statement for npgsql using parameter

18,028

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 || '%'";
Share:
18,028
NamrataP
Author by

NamrataP

Updated on June 24, 2022

Comments

  • NamrataP
    NamrataP over 1 year

    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
    
  • NamrataP
    NamrataP almost 11 years
    It worked!! Now its returning all the matching rows. Thanks @trippino for the help and for such a quick response.
  • Tobia Zambon
    Tobia Zambon almost 11 years
    Glad to help you. Welcome in stack overflow :)