'Populating a dropdown list with unique values' Thread RSS Feed

Populating a dropdown list with unique values

Wed, 16 Jun 2004 10:44:45 -0700

Hello, I am trying to find some help on displaying UNIQUE values in a dropdown list populated from a SQL server database.

I am using .NET framework with VB as the language and SQL server for data. I have the following code for populating the dropdown field CategoryList:

<asp:dropdownlist id="CategoryList" runat="server" Height="26px" Width="139px"></asp:dropdownlist>

------------------------------------------------------------------------------------------------------

Dim strconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim ds As DataSet

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
strconnection = New SqlConnection("Server=localhost;uid=sa;password=;d atabase=somename;")
myda = New SqlDataAdapter("Select * from tblDocs ", strconnection)
ds = New DataSet
myda.Fill(ds, "AllTables")

CategoryList.DataSource = ds
CategoryList.DataSource = ds.Tables(0)
CategoryList.DataTextField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
CategoryList.DataValueField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
CategoryList.DataBind()

End Sub

-------------------------------------------------------------------------

I can get the values into the dropdown list correctly but they appear repetitively in the list as they do in the SQL databases, for eg:

Category:

Van
Van
Van
SUV
Sedan

What can I do to make the drop down list unique so that values do not appear several times?

Thanks for any suggestions or help.

Re: Populating a dropdown list with unique values

Wed, 16 Jun 2004 12:08:48 -0700

: Hello, I am trying to find some help on displaying UNIQUE values in a dropdown list populated from a SQL server database.
:
: I am using .NET framework with VB as the language and SQL server for data. I have the following code for populating the dropdown field CategoryList:
:
: <asp:dropdownlist id="CategoryList" runat="server" Height="26px" Width="139px"></asp:dropdownlist>
:
: ------------------------------------------------------------------------------------------------------
:
: Dim strconnection As SqlConnection
: Dim myda As SqlDataAdapter
: Dim ds As DataSet
:
: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
: 'Put user code to initialize the page here
: strconnection = New SqlConnection("Server=localhost;uid=sa;password=;d atabase=somename;")
: myda = New SqlDataAdapter("Select * from tblDocs ", strconnection)
: ds = New DataSet
: myda.Fill(ds, "AllTables")
:
: CategoryList.DataSource = ds
: CategoryList.DataSource = ds.Tables(0)
: CategoryList.DataTextField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
: CategoryList.DataValueField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
: CategoryList.DataBind()
:
: End Sub
:
: -------------------------------------------------------------------------
:
: I can get the values into the dropdown list correctly but they appear repetitively in the list as they do in the SQL databases, for eg:
:
: Category:
:
: Van
: Van
: Van
: SUV
: Sedan
:
: What can I do to make the drop down list unique so that values do not appear several times?
:
: Thanks for any suggestions or help.
:
:

Perhaps??? It is hard to tell what you database looks like.

select distinct category from tblDocs

\\--||--//
<NATLOZ>
//--||--\\

Re: Populating a dropdown list with unique values

Wed, 16 Jun 2004 12:25:17 -0700

: : Hello, I am trying to find some help on displaying UNIQUE values in a dropdown list populated from a SQL server database.
: :
: : I am using .NET framework with VB as the language and SQL server for data. I have the following code for populating the dropdown field CategoryList:
: :
: : <asp:dropdownlist id="CategoryList" runat="server" Height="26px" Width="139px"></asp:dropdownlist>
: :
: : -----------------------------------------
: :
: : Dim strconnection As SqlConnection
: : Dim myda As SqlDataAdapter
: : Dim ds As DataSet
: :
: : Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
: : 'Put user code to initialize the page here
: : strconnection = New SqlConnection("Server=localhost;uid=sa;password=;d atabase=somename;")
: : myda = New SqlDataAdapter("Select * from tblDocs ", strconnection)
: : ds = New DataSet
: : myda.Fill(ds, "AllTables")
: :
: : CategoryList.DataSource = ds
: : CategoryList.DataSource = ds.Tables(0)
: : CategoryList.DataTextField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
: : CategoryList.DataValueField = ds.Tables(0).Columns("Category").ColumnName.ToStri ng()
: : CategoryList.DataBind()
: :
: : End Sub
: :
: : -------------------------------------------------------------------------
: :
: : I can get the values into the dropdown list correctly but they appear repetitively in the list as they do in the SQL databases, for eg:
: :
: : Category:
: :
: : Van
: : Van
: : Van
: : SUV
: : Sedan
: :
: : What can I do to make the drop down list unique so that values do not appear several times?
: :
: : Thanks for any suggestions or help.
: :
------------------------------------------------------------
:
: Perhaps??? It is hard to tell what you database looks like.
:

: select distinct category from tblDocs
:

: \\--||--//
: <NATLOZ>
: //--||--\\
:
------------------------------------------------------------

Thanks, "distinct" did the trick!

Do you know how I can make the dropdown list field editable on the .aspx form so that when the users create a new record, they have a choice of selecting a category, but if one does not exist, they should be able to type a new category in the field itself and submit the form?

Is this possible?

Thanks again!

Re: Populating a dropdown list with unique values

Wed, 16 Jun 2004 12:51:20 -0700

I would think that all you would have to do is save to your database whatever cboWhatever.text is (this will be whether or not they select it from the drop-down or manually type it in to the dropdown), and when you refresh the combo box it will have the newly saved record in your distinct select. This should work since you are not basing anything on a primary key in the database for the Category.

\\--||--//
<NATLOZ>
//--||--\\