CODEWITHSUNDEEP
asp.netsqlgridview
user49722
user49722

Reputation: 49

Wildcard in query for ASP.NET GridView

I am using GridView in ASP.NET 2.0. I am want to show the details from 3 tables (SQL2005) in the GridView per my search crieteria (Name of Visitor,Passport Number,Name of Company). It is working, but I want to use a wildcard for searching by first letter of "Name of Visitor". I have my code in the QueryBuilder in GridView (using Configure Datasource). The query is as follows:

SELECT FormMaster.NameofCompany, VisitorMaster.NameofVisitor,
VisitorMaster.PassportNumber, FormMaster.FormID,  
VisitorMaster.VisitorID FROM VisitorMaster INNER JOIN VisitorDetails ON
VisitorMaster.VisitorID = VisitorDetails.VisitorID INNER JOIN FormMaster ON
VisitorDetails.FormID = FormMaster.FormID WHERE (FormMaster.FormStatusID = 1) AND
(VisitorMaster.PassportNumber = @PassportNumber ) OR
(VisitorMaster.NameofVisitor = @NameofVisitor) OR
(FormMaster.NameofCompany = @NameofCompany )

Upvotes: 1

Views: 1641

Answers (3)

Benjamin Autin
Benjamin Autin

Reputation: 4171

Your query is:

SELECT
  FormMaster.NameofCompany,
  VisitorMaster.NameofVisitor,
  VisitorMaster.PassportNumber,
  FormMaster.FormID,
  VisitorMaster.VisitorID
FROM
  VisitorMaster INNER JOIN VisitorDetails ON
    VisitorMaster.VisitorID = VisitorDetails.VisitorID
  INNER JOIN FormMaster ON VisitorDetails.FormID = FormMaster.FormID
WHERE
  (FormMaster.FormStatusID = 1) AND
  (VisitorMaster.PassportNumber = @PassportNumber ) OR
  (VisitorMaster.NameofVisitor = @NameofVisitor) OR
  (FormMaster.NameofCompany = @NameofCompany )

Are you sure it is working correctly? When FormMaster.FormStatusID doesn't equal 1 and one of the last two criteria is true, it will still return that row. AND has higher precedence than OR in TSQL.

Properly, your WHERE clause should be:

FormMaster.FormStatusID = 1 AND (
  VisitorMaster.PassportNumber = @PassportNumber OR
  VisitorMaster.NameofVisitor LIKE @NameofVisitor OR
  FormMaster.NameofCompany LIKE @NameofCompany)

And append a % to the @NameofVisitor and @NameofCompany parameters you pass in. Doing it the way Shawn or WebMatrix suggest will have you matching everything if you don't pass in something whereas LIKE '' only matches an empty string.

Upvotes: 1

WebMatrix
WebMatrix

Reputation: 1601

try" NameOfVisiotr Like 'A%' instead

Upvotes: 0

Shawn
Shawn

Reputation: 19793

VisitorMaster.NameofVisitor like @NameofVisitor + '%'

Upvotes: 0

Related Questions