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?
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 ChongCommented:
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.
0
PortletPaulfreelancerCommented:
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
0
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 ...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

PortletPaulfreelancerCommented:
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?????
0
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 ...
0
Ryan ChongCommented:
how's your table design and can you provide us some test data as well?
0
PortletPaulfreelancerCommented:
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
0
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
0
Ryan ChongCommented:
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

0
PortletPaulfreelancerCommented:
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
0
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
0
Ryan ChongCommented:
>>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

0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.