searching in database based on multiple values which may/maynot be supllied

Question

0.00/5 (No votes)

See more:

I have five text boxes - a,b,c,d,e abd a search button

Now the user can provide input in any of the text boxes (either in a or b or c or abc or any possible combinations)

there is a possibility of few textboxes being blank. So a empty ( "" ) string goes into the where clause.

I want a select query in such a way that it should search the data in database according to the values entered by the user, and ignore the the values not entered

select * from tbl_Results where ...

what should b there after where()

thanks in advance

Posted 23-Jun-13 19:33pm

maverick12131

Add a Solution

2 solutions

Solution 2

Check the code Below

C#

private void PrepareSql()
        {
            string sqlstring = "select * from tblResults where 1=1 ";
            if (!txta.equals(""))
                sqlstring += "and column1 like '% " + txta.text + "%' ";
            if (!txtb.equals(""))
                sqlstring += "and column2 like '% " + txtb.text + "%' ";
            if (!txtc.equals(""))
                sqlstring += "and column4 like '% " + txtc.text + "%' ";
            if (!txtd.equals(""))
                sqlstring += "and column5 like '% " + txtd.text + "%' ";

        }

Thanks
--RA

Posted 23-Jun-13 20:26pm

Rajesh Anuhya

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)

Suman Zalodiya · Accepted Answer · 2013-06-23T20:16:00

Hi Maverick,

You can do one thing

SQL

string query="select * from tblResults ";
string sql=string.Empty;
if(!txta.equals(""))     
        sql+="where column1 like '% " + txta.text + "%' ";

SQL

if(!txtb.equals(""))
     if(sql.equals(""))
        sql+="where column2 like '% " + txtb.text + "%' ";
     else
        sql+="and column2 like '% " + txtb.text + "%' ";

SQL

if(!txtc.equals(""))
     if(sql.equals(""))
        sql+="where column3 like '% " + txtc.text + "%' ";
     else
        sql+="and column3 like '% " + txtc.text + "%' ";

SQL

if(!txtd.equals(""))
     if(sql.equals(""))
        sql+="where column4 like '% " + txtd.text + "%' ";
     else
        sql+="and columnd like '% " + txtd.text + "%' ";

SQL

query +=sql;

Then run the query in "query" variable

Please make it resolved if it resolves your issue

Thanks and Regards
Suman Zalodiya