Advanced search in ADO. Net Windows form application

Question

I have the below ADO.Net code written in c# to get the detail of the student from the student table when the user puts either the roll number or the student name. The problem is that, it was all fine when the search was being performed using only one column but I want to use either condition on columns. Does anybody have any idea if this thing is possible in the below code.The below code is throwing me an error saying:

incorrect syntax near OR.

comm = new SqlCommand("select * from student_detail where (roll_no = '%" + txtRollNumber.Text + "%') OR (s_name = '"+ txtStudentName.Text +"')", conn);

What is either condition ?? also, try using stored procedure to do that. — User2012384, Oct 27 '15 at 09:56
I want to search using either of the two textboxes i.e. either by using s_name or by roll_no. Is it not possible in this command object to use both columns — Sumit Dwivedi, Oct 27 '15 at 11:09
http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Steve, Oct 27 '15 at 23:51

codersl · Answer 1 · 2015-10-27T23:40:24.680

0

You have a % in your sql query which is a special wildcard character that is only use in LIKE clauses.

Try this:

comm = new SqlCommand("select * from student_detail 
                       where (roll_no = '" + txtRollNumber.Text + "') 
                       OR (s_name = '" + txtStudentName.Text + "')"
                       ,conn);

edited Oct 27 '15 at 23:40

answered Oct 27 '15 at 12:54

codersl

2,222
4
30
33

You had a % in your sql query which is a special wildcard character that is only use in LIKE clauses. – codersl Oct 27 '15 at 23:39
No. The thing which worked is by using roll_no = '" + txtRollNumber.Text + "' in place of roll_no = ' + txtRollNumber.Text + ". But how it worked is still out of the box because generally we put integer field inside " " and not inside ' " " ' – Sumit Dwivedi Oct 28 '15 at 13:37
what will be the code if i want to put the search result based on the value i am putting into textbox into datagrid?? – Sumit Dwivedi Oct 28 '15 at 13:43
That would be a totally different question and answer :) – codersl Oct 28 '15 at 15:16

score -1 · Answer 2 · answered Oct 28 '15 at 14:50

Just be aware, that you're not sanitising your inputs in your example. So, if someone puts a backtick in txtStudentName they can effectively run commands on your database. It's called SQL Injection. I suggest you look into how you can use Parameterized Queries in ADO.NET (it's really very, very easy) - this will make your code (well actually your database) much safer.

Advanced search in ADO. Net Windows form application

2 Answers2