Hi,

 I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields.

I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is:

 SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept)

so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful.

Cheers,

Tom

Tom,

 you can try this:

SELECT * FROM records WHERE (Name LIKE @Name OR @Name IS NULL) AND (Surname LIKE @Surname OR @Surname IS NULL) AND (Email LIKE @Email) AND (Dept LIKE @Dept OR @Dept IS NULL)

CAUTION:MS does not recommend this, but is the only way I've found without putting if/else

hope this help

Hi,

Thanks for the reply - I've just tried it and it doesn't come up with anything. I'm creating a new method called GetDataByInput and using the above as a search string. When I create the event handler to use it, I input the following:

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

Now when I put just one entry in the search form and click search, it doesn't return anything.

I tried creating another method called GetDataByName which just searched by the Name field. If I enter the following:

Hi tomosap,

Please check whether this SELECT query run correctly in the query analyzer. Also, pleaes add a breakpoint to check if the argument NameTxt.Text is passed with correct value.