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

Try changing your SQL to LIKE @find and change your parameter value to "%" + txtSearch.Text + "%". — Dai
– Dai, Commented Mar 1, 2016 at 10:53
@Chris The collation to this speciffic database is Croatian_100_BIN — Mihovil Lalić
– Mihovil Lalić, Commented Mar 1, 2016 at 10:57

Ruslan K. · Accepted Answer · 2016-03-01 11:20:08Z

2

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.

edited Mar 1, 2016 at 11:20

answered Mar 1, 2016 at 11:11

Ruslan K.

2,0011 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ɖiamond ǤeezeƦ · Accepted Answer · 2016-03-01 21:03:57Z

1

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;

edited Mar 1, 2016 at 21:03

answered Mar 1, 2016 at 11:02

Ɖiamond ǤeezeƦ

3,3413 gold badges32 silver badges42 bronze badges

Comments

NeuDav · Accepted Answer · 2016-03-01 11:13:08Z

0

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

answered Mar 1, 2016 at 11:13

NeuDav

313 bronze badges

Collectives™ on Stack Overflow

ASP.NET database search

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest