DropDown List with ALL indexed and SQL Statement 2

[net123]

I have a web form with a dropdown list bounded to a data source. This page is a search page that displays the results in a datagrid. I would like the dropdown list defaulted to one of the values of the database, but also in the list, I would like the value of 'ALL' to be in there. I seem to have figured out the code as follows.

So far I have this and I can't seem to get around it:

strSQL="SELECT CodeName FROM dbo.Codes Order By CodeID"
Cmd1=New SqlCommand(strSQL,Conn1)
Conn1.Open()
Rdr1=Cmd1.ExecuteReader()
ddlCode.DataSource = Rdr1
ddlCode.DataTextField = "CodeName"
ddlCode.DataBind()
ddlCode.Items.Insert(3, "All&quot
ddlCode.SelectedIndex = 0
Rdr1.close()
[/blue]
My ddl, on page_load should look something like this:

Name1 <-- default
Name2
Name3
All
[/blue]
The ddl looks fine with the above code, but how can I implement this logic into the SQL SELECT statement?

Greatly appreciate any help.

 

[BoulderBum]

One approach would be to simply change the CommandText property of the SqlCommand for the special case (to retrieve all record instead of some):

StringBuilder sbSQL = new StringBuilder();
sbSQL.append( "SELECT * FROM dbo.Codes" );

if( !ddlCode.SelectedItem.Text.equals( "All" ) )
{
     sbSQL.append( " WHERE CodeName = '" ); 
     sbSQL.append( ddlCode.SelectedItem.Text );
     sbSQL.append( "'" );
}

sbSQL.append( " Order By CodeID" );

Cmd1=New SqlCommand(sbSQL.ToString(),Conn1);

Is this what you wanted? If you're talking about adding the &quot;Add&quot; value somehow through SQL, I wouldn't worry about it. It's easier just to add the option to the end of the Items Collection.