How to write search query with multiple filter inputs in asp.net web form for ms sql

Question

I need to write a SQL Query for Advance search form shown as Image below.

I wrote this partial sql query but i cant make it work correctly as it also get syntax error if user select second check-box and leaves the first check-box.

strSql = "SELECT ArticleID, ArticleTitle, ArticleDesc, ArticlePublishDate FROM art_Articles WHERE ";
strSql += "( ArticleVisible = 1 AND ArticleActive =1 AND LanguageID =" + LangID +" )";
strSql += " AND ";
strSql += " ( ";

if (cbArchiveTitle.Checked)
{ strSql += "  ArticleTitle LIKE N'%" + search + "%'  "; }
if (cbArchiveDesc.Checked)
{ strSql += " OR ArticleDesc LIKE N'%" + search + "%'  "; }
if (cbArchiveSummary.Checked)
{ strSql += " OR ArticleBodyDesc LIKE N'%" + search + "%'  "; }

strSql += ")";

Advanced Search Form If i design my query something like this

 SELECT ArticleID, ArticleTitle, ArticleDesc, ArticlePublishDate FROM art_Articles WHERE 
 ( ArticleVisible = 1 AND ArticleActive =1 AND LanguageID =1 ) AND  
 (   ArticleTitle LIKE N'%Jobs%'   OR ArticleDesc LIKE N'%%'  )

And pass null value to fields which are not selected then i got all the row selected.

I would appreciate any help in designing this query to work for the form as show in the image

paramosh · Accepted Answer · 2012-02-08 11:42:49Z

3

You can add little trick to you code to resolve the issue with not selected first check box:

strSql = "SELECT ArticleID, ArticleTitle, ArticleDesc, ArticlePublishDate FROM 

art_Articles WHERE ";
strSql += "( ArticleVisible = 1 AND ArticleActive =1 AND LanguageID =" + LangID +" )";
strSql += " AND ";
strSql += " ( 0 = 1 ";

if (cbArchiveTitle.Checked)
{ strSql += " OR ArticleTitle LIKE N'%" + search + "%'  "; }
if (cbArchiveDesc.Checked)
{ strSql += " OR ArticleDesc LIKE N'%" + search + "%'  "; }
if (cbArchiveSummary.Checked)
{ strSql += " OR ArticleBodyDesc LIKE N'%" + search + "%'  "; }

strSql += ")";

But in general it's not good approach to compose SQL statement in this way. You can consider to use either technologies like Linq or at least some query builder like SelectQueryBuilder

answered Feb 8, 2012 at 11:42

paramosh

2,2581 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Learning Over a year ago

SelectQueryBuilder Seems like an option i also did it by using a little trick if (cbArchiveTitle.Checked) { strSql += " ArticleTitle LIKE N'%" + search + "%' "; } else { strSql += " ArticleTitle NOT LIKE N'%%' "; }

Collectives™ on Stack Overflow

How to write search query with multiple filter inputs in asp.net web form for ms sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related