Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try:
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.
  Permalink  
Comments
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
good
Please Sign up or sign in to vote.

Solution 1

Try
string query = "select pcode,fundingagency from project where (pcode like '" + TextBox1.Text) + "' or (fundingagency like '" + TextBox1.Text + "')" ;
.
  Permalink  
Rate this: bad
good
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();
        adp.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
 
this worked for me
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 499
1 OriginalGriff 461
2 sanket saxena 325
3 Tadit Dash 285
4 Abhinav S 275
0 Sergey Alexandrovich Kryukov 12,159
1 OriginalGriff 7,421
2 Peter Leow 5,061
3 Abhinav S 4,073
4 Maciej Los 3,575


Advertise | Privacy | Mobile
Web04 | 2.8.140421.2 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid