Click here to Skip to main content
11,803,861 members (76,990 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
i am making a search page in where i am putting a textbox and submit button. when the user enters a text then on button click what should happen is that the keyword should be binded with sql query and it should look for keywords matching that query in database.

i tried this query in sql server management studio and it was working:

SELECT * from project WHERE (pcode LIKE 'a%') OR (fundingagency LIKE 'r%')

what i want is to bind the textbox data in both the columns like what i want is

string query = "select pcode,fundingagency from project where=" ('pcode' like TextBox1.Text) or ('fundingagency' like TextBox1.Text) ;
SqlDataAdapter adp = new SqlDataAdapter(query,con);

can somebody format this query for me. casue i m getting errors saying "the multi-part identifiesr "textbox.text"could not be found.
Posted 18-Jan-13 22:14pm
Edited 18-Jan-13 22:22pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

string query = "select pcode,fundingagency from project where (pcode like '" + TextBox1.Text) + "' or (fundingagency like '" + TextBox1.Text + "')" ;
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

string query = "select pcode,fundingagency from project where (pcode like '" + TextBox1.Text+ "') or (fundingagency like '" + TextBox1.Text + "')";
SqlDataAdapter adp = new SqlDataAdapter(query,con);
But seriously, you shouldn't do it like that at all! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:
string query = "select pcode,fundingagency from project where (pcode like @SS) or (fundingagency like @SS)";
SqlDataAdapter adp = new SqlDataAdapter(query,con);
adp.SelectCommand.Parameters.AddWithValue("@SS", TextBox1.Text);
Both of these assume that the user has added the '%' character to his text box.
Abhinav S at 19-Jan-13 4:26am
5 of course. The additional suggestion was a good one.
OriginalGriff at 19-Jan-13 4:31am
Yeah - a web site search box that feeds directly into an SQL query? Brrrr! It's enough to make me feel very nervous indeed...
BTW: one of the brackets is in the wrong place in your solution.
a2ulthakur at 19-Jan-13 4:29am
but its at the front end of the form. so should i ask my users to enter % before or after keyword cause that will be messy
OriginalGriff at 19-Jan-13 4:37am
No - you could add them to the query:
(pcode like '%' + @SS + '%')
for example.
a2ulthakur at 19-Jan-13 4:30am
plus in the second query that u have mentioned we are not taking the user entered text in the textbox how will the query fetch that value
OriginalGriff at 19-Jan-13 4:36am
It does - it passes it as a parameter to the SQL in the final line so that the text content cannot affect the actual query, which is the whole point. If you user types
xxx');DROP TABLES project;--
in his text box, the second version looks for it as a string - the first version looks for anything containing "xxx" and then deletes your database table - a classic SQL injection attack. Google for "Bobby Tables" and don't assume it is a joke - it is, but it's also very, very true and very, very real.
BTW: DO NOT TRY THIS ON YOUR DATABASE UNLESS IT IS WELL BACKED UP. And don't come crying to me if you do try it...:laugh:
a2ulthakur at 19-Jan-13 4:50am
griff thanks its working but isnt there anywasy to make the query work on the front end without entering % before or after the query
OriginalGriff at 19-Jan-13 4:59am
LIKE requires the '%' for pattern matching is the same ways that Windows wants '*' in files names "*.*" matches any file with any extension.
Either the user adds it, or you do! :laugh:
a2ulthakur at 19-Jan-13 4:50am
because looking for a way to add it in client end wont help it will only make the process difficult !
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

protected void Button1_Click(object sender, EventArgs e)
        string query = "select pcode, pname, pi, copi, fundingagency, total from project where (pcode like'%'+ @SS +'%')  or (fundingagency like '%' + @SS + '%')";
        SqlDataAdapter adp = new SqlDataAdapter(query, con);
        adp.SelectCommand.Parameters.AddWithValue("@SS", TextBox1.Text);
       DataTable dt = new DataTable();
        GridView1.DataSource = dt;

this worked for me

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 450
1 CPallini 320
2 F-ES Sitecore 240
3 Sergey Alexandrovich Kryukov 234
4 CHill60 220
0 OriginalGriff 3,000
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,755
4 Richard MacCutchan 1,187

Advertise | Privacy | Mobile
Web03 | 2.8.151002.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100