Solved

Sort Bound Drop Drop List

Posted on 2010-11-24
5
226 Views
Last Modified: 2012-05-10
I have a drop down list that is bound to a SQL table. I'd like for the list to appear as being sorted in order to make it easier for the user.

In the following statement:

sComm = New Data.SqlClient.SqlCommand("SELECT Contacts From Table where DeptName = '" & DropDownList1.SelectedItem.Text & "'", sConn)

I changed it to:

sComm = New Data.SqlClient.SqlCommand("SELECT Contacts From Table ORDER BY DeptName where DeptName = '" & DropDownList1.SelectedItem.Text & "'", sConn)

But it's had no effect.
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        If DropDownList1.SelectedIndex = 0 Then
            TextBox1.Text = ""
        Else

            intcount = 0
            Dim sConn As Data.SqlClient.SqlConnection
            Dim sComm As Data.SqlClient.SqlCommand
            Dim sAdapt As Data.SqlClient.SqlDataAdapter


            sConn = New Data.SqlClient.SqlConnection("server=SQLServer;database=Datatbase;UID=UserID;PWD=Password")
            sComm = New Data.SqlClient.SqlCommand("SELECT Contacts From Table ORDER BY DeptName where DeptName = '" & DropDownList1.SelectedItem.Text & "'", sConn)
            sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
            sConn.Open()

            Dim r As Data.SqlClient.SqlDataReader = sComm.ExecuteReader()
            While r.Read()
                Dim strEmail As String = CStr(r("Contacts"))
                TextBox5.Text = strEmail
            End While
            r.Close()

Open in new window

0
Comment
Question by:JB4375
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34206717
The SqldataAdapter that have the dropdown is sorted??? Because On this line you only sort the list to search for a text when  a department is selected but you must Modify the SqlDataadapter query that is binding with the dropdown
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34206787
Check if the select query that have the SqlDataAdapter that is binding to the Dropdownlist is sorted by DeptName (Maybe you query must be like this if only shows the departments on the Dropdown :
SalDataAdapter select query ->  SELECT IdDpt,DeptName from Departaments_table ORDER BY DeptName) an this will show your Dropdown list order by Dept.
0
 
LVL 1

Author Comment

by:JB4375
ID: 34206967
K-Des,

Thanks for the quick response, but this flew completely over my head. Are you referring to this line:

sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

that needs to be edited or ???
0
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 500 total points
ID: 34207053
Nope my friend, If you have a Dropdownlist control that is binding to the SqlDatadapter1 then. on design time you must chek the query of this sqlDataAdapter and add the order by clausule to this query and then your dropdown list will be sorted. I illustrated to you by images down here

 1 Step2.png
3.png
0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 34207719
Thanks for the assist!!!
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question