I have 2 drop down menus, I select a value from the 1st menu and the 2nd drop down menu correctly snaps to the correct values based on the foreign key of the 1st drop down menu. I then select a value from the 2nd drop down but the value does not stay selected.

<asp:DropDownList id="ddlCategory" runat="server" AutoPostBack="True" />

Sub LoadDropDown()

    Dim strConnection As String = MM_Sherry_String
    Dim strSQLforListBox As String

    If NOT IsPostBack Then

    Dim objConnection2 As New SqlConnection(MM_Sherry_String)
      Dim strSQL As String = "SELECT * FROM Category ORDER BY CatName DESC"

      Dim objAdapter As New SqlDataAdapter(strSQL, objConnection2)

      Dim objDataSet As New DataSet()
      objAdapter.Fill(objDataSet, "Category")

     Dim strResultsHolder As String

      Dim r As DataRow

      For Each r In objDataSet.Tables("Category").Rows

    strSQLforListBox = "SELECT SubCat_Name, SubCatID " & _
                           "FROM Sub_Category WHERE SubCat_CatID = " & r("CatID") & " ORDER BY SubCat_Name"
    Next

    Else

    strSQLforListBox = "SELECT SubCat_Name, SubCatID " & _
                           "FROM Sub_Category WHERE SubCat_CatID = " & Request.Form("ddlCategory2") & " ORDER BY SubCat_Name"

    End If

    Dim objConnection As New SqlConnection(strConnection)
    Dim objCommand As New SqlCommand(strSQLforListBox, objConnection)

    objConnection.Open()
    ddlCategory.DataSource = objCommand.ExecuteReader()
    ddlCategory.DataTextField = "SubCat_Name"
    ddlCategory.DataValueField = "SubCatID"
    ddlCategory.DataBind()
    objConnection.Close()

End Sub

Another way around the problem would be to have two DataTextField's from a view in a single drop down menu? Anyone know how to do that?

Sub LoadDropDown2()
  If Not IsPostBack Then
    Dim strConnection As String = MM_Sherry_String

    Dim strSQLforListBox As String = "SELECT CatName, CatID " & _
                           "FROM Category ORDER BY CatName"

    Dim objConnection As New SqlConnection(strConnection)
    Dim objCommand As New SqlCommand(strSQLforListBox, objConnection)

    objConnection.Open()
    ddlCategory2.DataSource = objCommand.ExecuteReader()
    ddlCategory2.DataTextField = "CatName"
    ddlCategory2.DataValueField = "CatID"
    ddlCategory2.DataBind()
    objConnection.Close()
  End If
End Sub

is EnableViewState set to TRUE on the second drop down?

There must be a way to get two database fields in one drop down menu, please help!
---------------------------------------------------------------------

Another way around the problem would be to have two DataTextField's from a view in a single drop down menu? Anyone know how to do that?

Sub LoadDropDown2()
  If Not IsPostBack Then
    Dim strConnection As String = MM_Sherry_String

    Dim strSQLforListBox As String = "SELECT CatName, CatID " & _
                           "FROM Category ORDER BY CatName"

    Dim objConnection As New SqlConnection(strConnection)
    Dim objCommand As New SqlCommand(strSQLforListBox, objConnection)

    objConnection.Open()
    ddlCategory2.DataSource = objCommand.ExecuteReader()
    ddlCategory2.DataTextField = "CatName"
    ddlCategory2.DataValueField = "CatID"
    ddlCategory2.DataBind()
    objConnection.Close()
  End If
End Sub

Richard,

How are you calling the load for the second DDL? I'm not really sure what the code is in the original post because I can't see where it's being called from.

The rule of thumb with dependant DDLs is this:
1. Load first on initial page load.
2. Load second on a change of the first.

If you are calling the load for the second DDL on every page hit then you'll loose its selected value.

-Peter

I'm not going to use 2 DDL's now, but I want to have a DDL with 2 DataTextField's, in Classic ASP the code would look like this, notice Season_Start and Season_End are different database columns:

<select name="Season" id="Season">
                          <%
While (NOT RS_Season.EOF)
%>
                          <option value="online-snooker.asp?leagueid=<%=Request("leagueid")%>&Coun ty=<%=Request("County")%>&SeasonID=<%=RS_Season("S easonID")%>"><%= (RS_Season.Fields.Item("Season_Start").Value) %> - <%= (RS_Season.Fields.Item("Season_End").Value)%></option>
                          <%
  RS_Season.MoveNext()
Wend
If (RS_Season.CursorType > 0) Then
  RS_Season.MoveFirst
Else
  RS_Season.Requery
End If
%>
</select>

You'll need to do two things:

1. Create a select query that includes the necessary data for display:
    SELECT Field1+' - '+Field2 AS CombinedFields ...
2. On selected index change, save the selected index of the DDL, rebind with new query, re-select the selected item.

-Peter

I hope your on a good salary Peter, thanks again! heres the code if anyone has the same problem.

Sub LoadDropDown()
  If Not IsPostBack Then
    Dim strConnection As String = MM_Sherry_String

    Dim strSQLforListBox As String = "SELECT ProdRangeID, CatName+' '+ProdRange_Name as CombinedFields FROM Cat_ProdRange ORDER BY CatName"

    Dim objConnection As New SqlConnection(strConnection)
    Dim objCommand As New SqlCommand(strSQLforListBox, objConnection)

    objConnection.Open()
    ddlCategory.DataSource = objCommand.ExecuteReader()
    ddlCategory.DataTextField = "CombinedFields"
    ddlCategory.DataValueField = "ProdRangeID"
    ddlCategory.DataBind()
    objConnection.Close()
  End If
End Sub