Dataset query with alias column and allow searches

I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.
In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)

Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet

If InStr(Trim(searchClientNameText), "%")>0 Then
searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"
Else
searchStatement = "WHERE ClientName = @.searchClientNameText"
End If

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
searchStatement

........
End Function

(1) You would search on each of the columns that comprise the "ClientName".
searchValue = "%" & searchvalue & "%"

Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _
"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _
"[Event].[Event_Ref]"& _
"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _
searchStatement + " "& _
"UNION SELECT [Bus].[Organisation_Name],"& _
"[Event].[NumEvents], [Event].[Event_Ref]"& _
"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _
WHERE
([People].[Given_Name] IS NULL OR [People].[Given_Name] LIKE @.searchvalue)
OR ([People].[Family_Name] IS NULL OR [People].[Family_Name] LIKE @.searchvalue)
OR ( [Bus].[Organisation_Name] IS NULL OR [Bus].[Organisation_Name] LIKE @.searchvalue)
(2) You should be using Parameterized Query instead of hardcoding the values into the SQL Statement to prefent your sever from SQL Injection attack. Google for more info.

|||I don't see any reason why you should not go for a stored procedure for this type of situation. I would highly recommend that.
Thanks

Datagrid dataset problem

For a datagrid dataset, I'm trying to take the amount of all the sales by a salesperson
in two days and create an alias for sum(saleamount) (which would become
allsales) to put the grand total in.

select
thedate, sum(saleamount) as allsales , salesperson, orderID
from
transactions
WHERE (thedate IN ('6/1/2005', '6/2/2005'))
GROUP BY Salesperson
HAVING SUM(allsales > 0)

I'm getting "invalid syntax near SUM (on the Having clause)" when trying to run this
query. And I know there is data in there meeting this criteria.
Thanks
chumley

Try moving the closign parenthesis behnd he column you are summing:
HAVING SUM(allsales) > 0