search with TextBox and submit button?

Question

0.00/5 (No votes)

See more:

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

a2ulthakur

Updated 18-Jan-13 22:22pm

v3

Add a Solution

3 solutions

Solution 1

Try

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

.

Posted 18-Jan-13 22:22pm

Abhinav S

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

Posted 12-Feb-13 15:15pm

a2ulthakur

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-01-18T22:25:00

Solution 2

Try:

C#

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:

C#

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.

Posted 18-Jan-13 22:25pm

OriginalGriff

Comments

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 !