CODEWITHSUNDEEP
c#asp.netsql-server
Mihovil Lalić
Mihovil Lalić

Reputation: 33

ASP.NET database search

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();

Upvotes: 3

Views: 379

Answers (3)

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

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;

Upvotes: 1

Ruslan K.
Ruslan K.

Reputation: 1981

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.

Upvotes: 2

NeuDav
NeuDav

Reputation: 31

You could make both (the sql data and the textbox input) lowercase or uppercase. For SQL data in the query you can use "UCASE() or UPPER()" and for the texbox you can do the following, txtSearxh.Text.ToUpper().

Upvotes: 0

Related Questions