Click here to Skip to main content
11,932,461 members (53,938 online)
Rate this:
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 23:14pm
Edited 18-Jan-13 23: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 19-Jan-13 4:26am
5 of course. The additional suggestion was a good one.
OriginalGriff 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 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 19-Jan-13 4:37am
No - you could add them to the query:
(pcode like '%' + @SS + '%')
for example.
a2ulthakur 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 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 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 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 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.151126.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