Question how to write a Sql query in VB 2008 Query builder

[kbsudhir]

I have created a search form where users can serach the movie collection as per the genre.

Here query searchs for the records where Genre starts with the entered characters for testing purposes. Below is my query:

SELECT     Sl_No, Titles, MediaType, Genre, Brief_desc, Cost, Language, Rating, Starring, Disc_No, Current_Status
FROM         CD_DVDCollection
WHERE     (Genre LIKE @Genre + '%')

Now I want to do the below.

1. I want all the records having the entered characters anywhere in the genre not just in the starting to be displayed.

2. I want to create a option when user leases the filter "Genre blank" then all the records should be displayed, right now if left blank then no records is displayed.

Please guide

Rgards
Sudhir

 

[jmcilhinney]

You can kill two birds with one stone. Simply change this:

WHERE     (Genre LIKE @Genre + '%')

to this:

WHERE     (Genre LIKE '%' + @Genre + '%')

and it will work as you want. That will return every record where Genre starts with zero or more arbitrary characters, followed by the value entered by the user, followed by zero or more arbitrary characters. If the user enters an empty string, that matches every record.

 

[kbsudhir]

Thanks jmcilhinney

Below is the query which I created

SELECT     Sl_No, Titles, MediaType, Genre, Brief_desc, Cost, Language, Rating, Starring, Disc_No, Current_Status
FROM         CD_DVDCollection
WHERE     (Titles LIKE N'%' + @Titles + N'%') AND (MediaType LIKE N'%' + @MediaType + N'%') AND (Genre LIKE N'%' + @Genre + N'%') AND 
                      (Brief_desc LIKE N'%' + @Brief_desc + N'%') AND (Language LIKE N'%' + @Language + N'%') AND (Starring LIKE N'%' + @Starring + N'%') AND 
                      (Disc_No LIKE N'%' + @Disc_No + N'%') AND (Current_Status LIKE N'%' + @Current_Status + N'%')

While testing in the query builder this query is wroking absolutely fine. But When I am executing the datagrid the data is not getting extracted. There is no error but no info is pulled.

So I tested the query in the dataset by clicking preview data but at this point I am getting an error but no error when I test the same in the query builder.

I am attaching the screenshot of the error. Slide one shows the error while testing from dataset while second slide shows testing inthe query builder where data is extracted without any error.

Where I am going wrong..???

Please guide.

Regards
Sudhir