Hii

I've implemented a method for searching the "City" table by a partial city name match using a wildcard search in the SQL query. The objective is to retrieve the associated "CountryId" based on a partial match of the city name provided.

However, upon deployment, an error occurred, preventing the successful execution of the query. The code snippet is structured to perform a wildcard search using the Like clause, but it seems there might be an issue with its implementation.

Here is the code snippet in question:



public string SelectCountryIdByCityName(string CityName)
{
    var result = "";
    var sel = new Select(UserConnection)
            .Column("CountryId")
        .From("City")
        .Where("Name").Like(Column.Parameter("%" + CityName + "%")) as Select;
    result = sel.ExecuteScalar().ToString();
    return result;
}

I am seeking guidance or any insights you may have regarding the correct implementation of a wildcard search in SQL queries for partial city name matches.

Your assistance or suggestions on rectifying this issue would be immensely appreciated.

 

Like 0

Like

2 comments

Hi,

 

Any updates on this?

Hi Satyam,

To implement a wildcard search in the SQL query correctly, you need to make some changes in the provided code:

  1. change “Like” to “IsLike”;
  2. replace the “ExecuteScalar” method (which returns a single record) with “DBExecutor” (which allows the return of multiple records). Here are some examples of using it - https://academy.creatio.com/docs/8.x/dev/development-on-creatio-platform/back-end-development/data-operations-back-end/direct-access/examples/retrieve-data .

 

Best regards,

Natalia

Show all comments