I am using the following command to list some names based on what shift the supervisor is in charge of. If the management person is over the entire area I want them to see all of the employees. in access I used to use the like statement to do this and if the parameter for shift was null it would return everything. In this statement the employees are returned for the shift if the parameter isn't null. If the parameter is null or "" it will not populate my dropdown box.
AND (shift LIKE '%' + @Shift + '%')
SelectCommand="SELECT [Associate] FROM [tblAssocLineList] WHERE (line = @Line AND farmedLine IS NULL OR farmedLine = @Line) AND (shift LIKE '%' + @Shift + '%') AND (Leave=0) AND ([Associate] NOT IN (SELECT [safetyAssoc] FROM [tblSafetyAssessment] WHERE [safetyDate] = @SafetyDate))">
Any help is greatly appreciated.
JoeyDIdeally, you should create a base class to manage the details for who is logged in, from which all the 'management' types inherit - this will allow you to have discreet queries / stored procedures for each 'type' of manager that is logged in.
However, if that's not your bag, a quick and dirty way would be to build your sql dynamically by using if statements to provide the "AND" clauses. This could also be done using a CASE statement.
I wouldn't advise putting wildcard text searches in your criteria - your app should be a bit more solid and use an identifing id for each shift type / nameHi JoeyD,
I think you'll find this will work for you...
AND (shift LIKE '%' + ISNULL(@Shift, '') + '%')
Gav
AND (shift LIKE '%' + @Shift + '%')
SelectCommand="SELECT [Associate] FROM [tblAssocLineList] WHERE (line = @Line AND farmedLine IS NULL OR farmedLine = @Line) AND (shift LIKE '%' + @Shift + '%') AND (Leave=0) AND ([Associate] NOT IN (SELECT [safetyAssoc] FROM [tblSafetyAssessment] WHERE [safetyDate] = @SafetyDate))">
Any help is greatly appreciated.
JoeyDIdeally, you should create a base class to manage the details for who is logged in, from which all the 'management' types inherit - this will allow you to have discreet queries / stored procedures for each 'type' of manager that is logged in.
However, if that's not your bag, a quick and dirty way would be to build your sql dynamically by using if statements to provide the "AND" clauses. This could also be done using a CASE statement.
I wouldn't advise putting wildcard text searches in your criteria - your app should be a bit more solid and use an identifing id for each shift type / nameHi JoeyD,
I think you'll find this will work for you...
AND (shift LIKE '%' + ISNULL(@Shift, '') + '%')
Gav