SQL Querying Multiple Wild Card Fields In a Table

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

SQL Querying Multiple Wild Card Fields In a Table

Author

Topic

david2010
Starting Member

7 Posts

Posted - 2010-03-12 : 13:34:04

Hi everyone,

For the current project i am creating i have a team form i wish to search. One thing this allows the user to do is a multiple search on all the team fields and the search will bring back what ever has been inputed in the textboxes on that form. However I am struggling with the sql to create the multiple search.

I have the following so far:


SELECT        TeamName, ForeName, Surname, WorkEmail, WorkTelephone, StaffID
FROM            Staff
WHERE        (TeamName = '' OR
                         TeamName LIKE @TeamName + '%') AND (ForeName = '' OR
                         ForeName LIKE @ForeName + '%') AND (Surname = '' OR
                         Surname LIKE @Surname + '%') AND (WorkEmail = '' OR
                         WorkEmail LIKE @WorkEmail + '%') AND (WorkTelephone = '' OR
                         WorkTelephone LIKE @WorkTelephone + '%')

Was wondering if anybody could help.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 13:38:39

shouldnt that be?


SELECT        TeamName, ForeName, Surname, WorkEmail, WorkTelephone, StaffID
FROM            Staff
WHERE        (@TeamName = '' OR
                         TeamName LIKE @TeamName + '%') AND (@ForeName = '' OR
                         ForeName LIKE @ForeName + '%') AND (@Surname = '' OR
                         Surname LIKE @Surname + '%') AND (@WorkEmail = '' OR
                         WorkEmail LIKE @WorkEmail + '%') AND (@WorkTelephone = '' OR
                         WorkTelephone LIKE @WorkTelephone + '%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

david2010
Starting Member

7 Posts

Posted - 2010-03-12 : 13:47:45

Yes it was originally that, but that didn't work, so I tried changing it before I posted it on the forum.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 13:56:13

whats your reqmnt by the way?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

david2010
Starting Member

7 Posts

Posted - 2010-03-12 : 14:09:34

Requirement?

Its to allow the searching of multiple data from the search team form. My project is in visual basic. For example if a user wished to search for a staff member and wanted to find thier staff id but only knew their forename and surname they would perform a multiple search after typing data into the two text fields.

I am calling the search query on a button click event with the following code.




    Private Sub MultiSearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MultiSearchButton.Click

        Try
            StaffTableAdapter.MultiSearch(Me.DefectManagementToolDatabaseDataSet.Staff, TeamNameSearchTextbox.Text, ForeNameSearchTextbox.Text, SurnameSearchTextbox.Text, WorkEmailSearchTextbox.Text, WorkTelephoneTextBox.Text)
        Catch ex As Exception
            MsgBox("Invalid search properties!", MsgBoxStyle.Exclamation + MsgBoxStyle.DefaultButton1 + MsgBoxStyle.OkOnly, "Search Failed")
        End Try

    End Sub

Thanks.

david2010
Starting Member

7 Posts

Posted - 2010-03-12 : 14:10:59

My single search queries work perfectly doing them this way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:06:17

so are you looking for VB soln?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

david2010
Starting Member

7 Posts

Posted - 2010-03-13 : 05:16:33

No its the sql that isn't working properly. The vb aspect works fine on all my other search funtions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 11:04:51

what are default values you pass for parameters? is it '' or NULL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources