title: Sorting a DropDownList when using AS in sql
body: <p>I am having trouble sorting a drop down list. </p>
  
  <p>I have tried a few different ways I have found on here using dataviews but cannot get them to work.  </p>
  
  <p>Here is my code that creates the drop down list I want to be sorted by [Last Name].</p>
  
  <pre><code>Dim SqlQuery As String = "SELECT [First Name] + ' ' + [Last Name] + ' ' + [Email] + ' ' + [Phone] AS [FullInfo] FROM [Official]"
  
  con.Close()
  con.Open()
  
  Dim dt = New DataTable
  Using sqlconnecton = New SqlConnection(con.ConnectionString)
      Using da = New SqlDataAdapter(SqlQuery, con)
          da.Fill(dt)
      End Using
  End Using
  
  DropDownList1.DataSource = dt
  DropDownList1.DataTextField = "FullInfo"
  DropDownList1.DataValueField = "FullInfo"
  DropDownList1.DataBind()
  </code></pre>
  
  <p>UPDATE 1</p>
  
  <p>I should not have tried to simplify this question. How my query is being done is much more complicated than I posted above. So what actually happens is this query is inside of a For Each loop and finds one record each time goes through the loop and adds it to the drop down list. Since the query is done like this I cannot Order by the last name since it's only one record at a time being added to the drop down list. </p>
  
  <p>Below is the Full code of what is going on:</p>
  
  <pre><code> For Each row As DataRow In table.Rows 'Check each official for availibility and fill drop down list if needed
  
  
  
                      Dim col1 As Int32 = row.Field(Of Int32)(0)
                      Dim col1string As String = col1
  
  
                      'now get block info for each person
                      Dim SqlQuery2 As String = "Where ([Organization] = '" + Session("ID") + "' OR [Organization 2] = '" + Session("ID") + "' OR [Organization 3] = '" + Session("ID") + "') AND [ID] = '" + col1string + "'"
                      Dim NumOfBlocks As Integer = row.ItemArray.Count - 10
                      Dim strblocks As String = "Blocks"
                      Dim FirstChange As String = "No"
                      For i = 1 To NumOfBlocks
                          If i = NumOfBlocks Then 'Last one dont end with AND
                              Dim Num As String = i
  
                              If String.IsNullOrEmpty(row.Field(Of String)(strblocks + Num)) Then 'dont include
                              Else 'include data
                                  If FirstChange = "Yes" Then 'We have an AND
  
                                      SqlQuery2 = SqlQuery2 &amp; "" + strblocks + Num + " &lt;&gt; '" + Session("DateTxt") + "'"
  
                                  Else 'We dont have an AND
  
                                      SqlQuery2 = SqlQuery2 &amp; " AND " + strblocks + Num + " &lt;&gt; '" + Session("DateTxt") + "'"
  
                                  End If
  
                              End If
                          Else
                              Dim Num As String = i
  
                              If String.IsNullOrEmpty(row.Field(Of String)(strblocks + Num)) Then 'dont include
  
                              Else 'include data
                                  FirstChange = "Yes"
                                  SqlQuery2 = SqlQuery2 &amp; " AND " + strblocks + Num + " &lt;&gt; '" + Session("DateTxt") + "' And "
                              End If
  
  
                          End If
  
                      Next
  
                      'Trim String if it ends with just "and" so an error is not thrown in sql below
                      If SqlQuery2.EndsWith("And ") Then
                          SqlQuery2 = SqlQuery2.Substring(0, SqlQuery2.Length - 5)
                      Else
  
                      End If
  
                      con.Close()
  
                      Dim SqlQuery As String = "SELECT [First Name] + ' ' + [Last Name] + ' ' + [Email] + ' ' + [Phone] AS [FullInfo] FROM [Official]"
  
  
                      con.Close()
                      con.Open()
                      Dim dt = New DataTable
                      Using sqlconnecton = New SqlConnection(con.ConnectionString)
                          Using da = New SqlDataAdapter(SqlQuery &amp; SqlQuery2, con)
  
                              da.Fill(dt)
                          End Using
                      End Using
  
                      DropDownList1.DataSource = dt
                      DropDownList1.DataTextField = "FullInfo"
                      DropDownList1.DataValueField = "FullInfo"
                      DropDownList1.DataBind()
  
                      DropDownList3.DataSource = dt
                      DropDownList3.DataTextField = "FullInfo"
                      DropDownList3.DataValueField = "FullInfo"
                      DropDownList3.DataBind()
  
                      DropDownList4.DataSource = dt
                      DropDownList4.DataTextField = "FullInfo"
                      DropDownList4.DataValueField = "FullInfo"
                      DropDownList4.DataBind()
  
                      DropDownList5.DataSource = dt
                      DropDownList5.DataTextField = "FullInfo"
                      DropDownList5.DataValueField = "FullInfo"
                      DropDownList5.DataBind()
  
                      DropDownList6.DataSource = dt
                      DropDownList6.DataTextField = "FullInfo"
                      DropDownList6.DataValueField = "FullInfo"
                      DropDownList6.DataBind()
  
                      DropDownList7.DataSource = dt
                      DropDownList7.DataTextField = "FullInfo"
                      DropDownList7.DataValueField = "FullInfo"
                      DropDownList7.DataBind()
                      con.Close()
                  Next 
  </code></pre>