ASP.NET database search

Question

I'm trying to create a search box on my web application, to search by company name in my database, and bind the result to the GridView. Some of the names in the DB are written in uppercase, some are in lowercase, some are mixed.

The query I have written returns result only if I spell the name in the same way that is in the DB, e.g. if I search "companyname" it wont find anything, but "COMPANYNAME" will.

string find = "select idKorisnik, Korisnik_naziv, Pravni_oblik, Web from tblKorisnici where (Korisnik_naziv like '%' + @find +'%' )";

string CS = ConfigurationManager.ConnectionStrings["CRMdbConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(CS);
SqlCommand cmd = new SqlCommand(find, con);
cmd.Parameters.Add("@find", SqlDbType.NVarChar).Value = txtSearch.Text;

con.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Korisnici_naziv");
GridView2.DataSource = ds;
GridView2.DataBind();
con.Close();

Answer 1

Change this row:

string find = "select idKorisnik, Korisnik_naziv, Pravni_oblik, Web from tblKorisnici where Korisnik_naziv COLLATE Croatian_100_CI_AI like '%' + @find +'%' ";

There is no subquery.

Answer 2

Try adding COLLATE to the column on which your are filtering so that SQL Server performs a case-insensitive collation such as Croatian_100_CI_AI.

SELECT idKorisnik, Korisnik_naziv, Pravni_oblik, Web
FROM
(
  SELECT
    idKorisnik, 
    Korisnik_naziv COLLATE Croatian_100_CI_AI AS Korisnik_naziv,
    Pravni_oblik, 
    Web
  FROM tblKorisnici
)
WHERE Korisnik_naziv LIKE '%' + @find +'%';

Or without the derived table as per Rusian K's answer:

SELECT idKorisnik, Korisnik_naziv, Pravni_oblik, Web
FROM tblKorisnici
WHERE Korisnik_naziv LIKE '%' + @find +'%' COLLATE Croatian_100_CI_AI;