Insert specific item at the first position and other items in alphabetically order in asp.net DropDownList

Hi All!
I have a dropDownList DD1 and I would like to put the item "ALL" at the first position and other items should be sorted in alphabetically order in  code behind file using C#. The DropDownList is bound to a datasource whit the SelectCommand:
Select * from Art WHERE ArtName = 'art2' Order by ArtName ASC

Open in new window


How could I achieve this?
Thank you
Cony TNAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you know the value for the item that always has the first position? if yes, then you can try modify your select SQL to something like this:

Select * from Art WHERE ArtName = 'art2' and valueField <> 'first item value' Order by ArtName ASC

Open in new window


and then in the page_load event, we manually add the first item into the top list after the dropdown list was binded.

Do you think this make sense to you?

if it's not, we probably need do a customize sorting in your query, but this also depends on what backend database you're using.
PortletPaulEE Topic AdvisorCommented:
If this is a drop down list, why would you be using "select *"???????

Specify the column by name I used "NeededSingleColumn" to highlight the intention

SELECT
      NeededSingleColumn
FROM Art
WHERE ArtName = 'art2'

UNION ALL
SELECT 'ALL'

ORDER BY
      CASE
            WHEN NeededSingleColumn = 'ALL' THEN 1
            ELSE 2
      END
    , NeededSingleColumn ASC
Cony TNAuthor Commented:
Thank you for your suggestions.

@Paul Maxwell
When using your sql statement I become the following error message:
Msg 104, Level 16, State 1, Line 15
ORDER BY items must appear in the select list if the statement contains a UNION operator

So I change the sql statement as follow:
SELECT DISTINCT
      NeededSingleColumn
FROM Art
WHERE ArtName = 'art2'

UNION ALL
SELECT 'ALL' AS [ NeededSingleColumn]

ORDER BY
      CASE
            WHEN NeededSingleColumn = 'ALL' THEN 1
            ELSE 2
      END
    , NeededSingleColumn ASC

But still have the same error message ...
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

PortletPaulEE Topic AdvisorCommented:
You need to choose a column by its actual name

Don't  use the string NeededSingleColumn
Change it to the real column nsme

Is it artname that you list in the drop fown?????
Cony TNAuthor Commented:
I didn't use use the string NeededSingleColumn
but the real name of the column.
No Artname is not listed in the dropdown but something else (description).
I did replace in the sql statement NeededSingleColumn by description and I got the error message described above ...
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how's your table design and can you provide us some test data as well?
PortletPaulEE Topic AdvisorCommented:
That error message is saying you are trying to sort the rows by a field that isn't present in the select clause - which you cannot do when using UNION / UNION ALL, and there are also similar restrictions to sorting when you use SELECT DISTINCT as well.


e.g. something like this should work:
SELECT DISTINCT
      [artist]
FROM Art
WHERE ArtName = 'art2'

UNION ALL
SELECT 'ALL' as [artist]

ORDER BY
      CASE
            WHEN [artist] = 'ALL' THEN 1
            ELSE 2
      END
    , [artist] ASC
Cony TNAuthor Commented:
I've attached a file to  show you how my table (Name: Motorarts) looks like.
The sql statement:

SELECT DISTINCT
  [Description]
FROM Motorarts
WHERE Art = 'art1'

UNION ALL
SELECT 'all' as [Description]

ORDER BY
      CASE
            WHEN [Description] = 'all' THEN 1
            ELSE 2
      END
    , [Description] ASC

I'm still getting the error message 104 ... even as I removed the keyword Distinct it still doesn't work ...MotorArts_Table.PNG
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

select 
1 seq, a.id, a.description, a.art 
from Motorarts a
WHERE a.Art = 'art1'
and a.description = 'all'
union
Select 
2 seq, b.id, b.description, b.art 
from Motorarts b
WHERE b.Art = 'art1'
and b.description <> 'all'
Order by 1, a.description

Open in new window

PortletPaulEE Topic AdvisorCommented:
what does the image show?
the source table Motorarts? or a query result?

why do you have rows with "all" in the description?

sorry, doesn't make sense to me

{+edit} seems Ryan follows it, so I'll bow out
Cony TNAuthor Commented:
The image shows the source table Motorarts and the DropDownList should contain the Description from Motorarts where art='art1' . The top item in the DropDownlist should be 'all' and other items should follow in alphabetically order that's mean:
- Fist Postion: all
- 2nd Position: abc
-3rd Position: Motor000
-4th position : Motor123

@Ryan
I changed your sql statement like this:

select
1 seq, a.description
from Motorarts a
WHERE a.Art = 'art1'
and a.description = 'all'
union
Select
2 seq,b.description
from Motorarts b
WHERE b.Art = 'art1'
and b.description <> 'all'
Order by  1, a.description

Since I only need the description values (where art='art1') in the DropdownList
how I can change the above statement?
Thank you
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Since I only need the description values (where art='art1') in the DropdownList
how I can change the above statement?
try...

Select a.description
from
(
select 
1 seq, a.id, a.description, a.art 
from Motorarts a
WHERE a.Art = 'art1'
and a.description = 'all'
union
Select 
2 seq, b.id, b.description, b.art 
from Motorarts b
WHERE b.Art = 'art1'
and b.description <> 'all'
) a
order by a.seq, a.description

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.