Searching - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching

Author

Topic

Born Survivor
Starting Member

3 Posts

Posted - 2010-01-16 : 06:29:01

Hi everyone,

Im having a problem with searching my sql database for a tool im designing in VB. All of my singe text field searches work fine. The problem is trying to do a multiple search on more than one of these fields. So the plan is more than one text box will be filled out on my form and a multi search button would be selected and the valid results with matching data would be returned. Not all of the searching fields in the statement should have to have data in.

The sql code I had is as follows, I realise this doesnt work, its more to help you understand what i'm trying to do.


SELECT        DefectId, TypeOfIssue, RaisedBy, Team, DateRaised, IdentifiedInEnvironment, IdentifiedInVersion, IdentifiedInBuild, Description, BusinessImpact, Product, 
                         TechnicalNotesAndActions, Priority, Assigned, Status, LocationOfScreenPrint
FROM            DefectLogTable
WHERE        (TypeOfIssue LIKE @TypeOfIssue + '%') OR
                         (RaisedBy LIKE @RaisedBy + '%') OR
                         (Team LIKE @Team + '%') OR
                         (DateRaised LIKE @DateRaised + '%') OR
                         (IdentifiedInEnvironment LIKE @Environment + '%') OR
                         (IdentifiedInVersion LIKE @Version + '%') OR
                         (IdentifiedInBuild LIKE @Build + '%') OR
                         (Description LIKE @TitleAndDes + '%') OR
                         (Product LIKE @Product + '%') OR
                         (Priority LIKE @Priority + '%') OR
                         (Assigned LIKE @Assigned + '%') OR
                         (Status LIKE @Status + '%')

Thanks for your help!

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-16 : 07:02:51

I am assuming that if 2 textboxes filled with data then your search results must match both the search condition. If this is the case my script will work.
You need to send blank string to the parameter if the data to the text box is not enterted.

SELECT DefectId, TypeOfIssue, RaisedBy, Team, DateRaised, IdentifiedInEnvironment, IdentifiedInVersion, IdentifiedInBuild, Description, BusinessImpact, Product,
TechnicalNotesAndActions, Priority, Assigned, Status, LocationOfScreenPrint
FROM DefectLogTable
WHERE (@TypeOfIssue='' OR TypeOfIssue LIKE @TypeOfIssue + '%') AND
(@RaisedBy='' OR RaisedBy LIKE @RaisedBy + '%') AND
(@Team='' OR Team LIKE @Team + '%') AND
(@DateRaised='' OR DateRaised LIKE @DateRaised + '%') AND
(@Environment='' OR IdentifiedInEnvironment LIKE @Environment + '%') AND
(@Version='' OR IdentifiedInVersion LIKE @Version + '%') AND
(@Build='' OR IdentifiedInBuild LIKE @Build + '%') AND
(@TitleAndDes='' OR Description LIKE @TitleAndDes + '%') AND
(@Product='' OR Product LIKE @Product + '%') AND
(@Priority='' OR Priority LIKE @Priority + '%') AND
(@Assigned='' OR Assigned LIKE @Assigned + '%') AND
(@Status='' OR Status LIKE @Status + '%')

I hope this will help you to solve the issue.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Born Survivor
Starting Member

3 Posts

Posted - 2010-01-16 : 07:19:04

Thanks, however what I mean't was that the user would fill out any of these searching textboxes. It wont be known which ones they are. It could be 2,3,4 or all them. Then the multi search button would be selected and results matching whats in the boxes would be returned back.

Any suggestions??

Thank you for yoir help!

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 08:11:10

Did you try sql-programmers example?

In what way doesn't it do what you want?

Born Survivor
Starting Member

3 Posts

Posted - 2010-01-16 : 08:24:00

Sorry,

That example did work just as I wanted!! I confused myself. Brilliant!

Thank You!

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 08:49:18

Glad you've got it working