Dropdownlist - SELECT ALL - almost there!!!???

[eb24]

I have an ASP.NET page in which a user can search via some controls. I have a dropdownlist that gets its values via a stored procedure in a SQL 2K database. This dropdownlist only has 3 values, but I would like to implement an ALL option.


if (ddlStatus.Items[ddlStatus.SelectedIndex].Value = "ALL&quot
{
strSQL += " A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value; ???????????????
}
else
{
strSQL += " A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value;
}
[/blue]
I just added the top statement (first strSQL), but don't know how to change it. The bottom strSQL statement is the original statement without the ALL option.

What is the best way to do this? Should I change the stored procedure or how can I change the above code to allow selecting from all 3 options?

Greatly appreciate your help.

 

[obislavu]

Using stored procedures is a good way.
What are the input parameters of the stored procedure ?
-obislavu-

 

[SHelton]

Try using the following subquery syntax:

{
    strSQL += " A.StatusCodeID IN (SELECT colname FROM tablename WHERE whereclause)";
}

Replace 'colname', 'tablename' and 'whereclause' with the required SQL to retrieve the same records that populated the combo.  This will then give you your ALL option.

 

[obislavu]

Given the above statements I see that A.StatusCodeID is an Integer field and the value selected from the ddlStatus combobox are passed also as integer in order to work the existing statement:
strSQL += " A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value;
So, I assume you have a drop list where you show StatusIds , like here:
0 which refer to NEW status
1 which refer to WIP status
2 which refer to RESOLVED status
3 which refer to CLOSED status
and depending on the user selection you want to build a where clause string like :
strSQL += " A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value;

which will be :
strSQL+= "A.StatusCodeID = 1" when WIP was selected
strSQL+= "A.StatusCodeID = 3" when CLOSED was selected

Now you want to add another one entry in the drop list , ALL that means to select all existing StatusCodeID.
In this case the drop list will be :
0
1
2
3
ALL
and the SQL statements will be :

if (ddlStatus.Items[ddlStatus.SelectedIndex].Value = "ALL&quot
{
strSQL += " A.StatusCodeID >= 0 "; // ???????????????
}
else
{
strSQL += " A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value;
}

***
The best way to deal with that is to have a Table in your database, let be it TStatus whith StatusCodeID (int), Description (varchar)
0 NEW
1 WIP
2 RESOLVED
3 CLOSED


When you populate the drop list you bind it from the Description column of this table. So the droplist will show:
NEW
WIP
RESOLVED
CLOSED
ALL
When you want to retrive a record set from the database depending on the user selection use a STORED PROCEDURE which get as parameters the user selections and returns the right record set.
In this case your strSQL will resume to :

strSQL = "EXEC sp_GetStatuses '"+ ddlStatus.Items[ddlStatus.SelectedIndex].Value + "','"+m_Var1+"','"+m_Var2+"'";
where m_Var1 & m_Var2 could be table names , column names etc...

The Select and the logic will be put in the sp_GetStatuses() procedure and will be safe and performant.

CREATE PROCEDURE dbo.sp_GetStatuses
( @SelectedStatus varchar(50), @Var1 varchar(50, @Var2 varchar(50))
AS

DECLARE @StatusName varchar(50)
DECLARE @Cmd varchar(1024)
DECLARE @WhereClause varchar(255)
SET NOCOUNT ON

IF ( @SelectedStatus IS NOT NULL )
BEGIN
/* Build the select depending on other parameters as Var1 , Var2 */
Set @Cmd='SELECT * from ????? '
/* Build the whereclause string depending on the @SelectedStatus parameter
if ( @SelectedStatus = 'NEW' ) Set @WhereClause=' StatusCodeID = 0)'
if ( @SelectedStatus = 'WIP') Set @WhereClause=' StatusCodeID = 1)'
if ( @SelectedStatus = 'RESOLVED') Set @WhereClause=' StatusCodeID = 2)'
if ( @SelectedStatus = 'CLOSED') Set @WhereClause=' StatusCodeID = 3)'
if ( @SelectedStatus = 'ALL') Set @WhereClause=' StatusCodeID IN (0,1,2,3)'
Set @Cmd=@Cmd + @WhereClause
EXEC(@Cmd)
END
RETURN 0
GO
Improvment: The above If could be replaced by retrieving the StatusCodeID from the TStatus table.

-obislavu-

 

[phinoppix]

Correct me if I'm wrong, you have a dropdown combo with integer values, plus "ALL" caption. strSQL is dynamic. I believe you're trying to append the correct expression into the "WHERE " (w/c I think also is what ends strSQL). So, maybe you can rephrase strSQL so that you can do...
if (ddlStatus.Items[ddlStatus.SelectedIndex].Value != "ALL&quot
{
strSQL += " WHERE A.StatusCodeID = " + ddlStatus.Items[ddlStatus.SelectedIndex].Value;
}

This means that strSQL by default is implementing ALL. When user selects an item from ddlStatus, only then you append a WHERE clause.

Hope this helps