We are familiar with the "Select distinct <field> from <table>".This SELECT statement is used to filter out duplicate results from a query's output. Lets consider the handy Database Northwind and Table Suppliers. The field Country has duplicate records. Normally the Stored Procedures are already created and we are supposed to work on it.
The Stored Procedure is as follows:
Create Procedure GetSuppliers AS
SELECT * FROM Suppliers
GO
Our task is to display Country. Calling just this stored Procedure would give us all the Data. Even binding this with any of the server control would give duplicated countries. .NET Framework Class Libraries has System.Collections namespace that contains interfaces and classes that define various collection objects such as lists, queues, bit arrays, HashTables and dictionaries.
The appropriate Class that can be used to tackle with the issue would be SortedList. The SortedList represents a collection of key-and-value pairs that are sorted by the keys and are accessible by key and by index. For more details refer SortedList. We woould not go in details of the SortedList but take what's required for us to do the coding. In Our case we have the field Country which has duplicate values. So when we try to add the values in the SortedList we'll first check if the value exists.
Moving to the coding. We'll have a Webform with a DropDownlist.
The steps are as follows:
Step 1: Populate the DataSet with the DataAdapter
Dim
sqlStmt As String = "GetSuppliers"
Dim conString As String = "server=localhost;database=Northwind;uid=sa;pwd=;"
Dim myda As SqlDataAdapter = New SqlDataAdapter(sqlStmt, conString)
myda.SelectCommand.CommandType = CommandType.StoredProcedure
myda.Fill(ds, "Table")
Step 2: Populating the SortedList based on the values in the Field Country
Dim
slist As SortedList = New SortedList
Dim i As Integer
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
If Not slist.ContainsValue(dr("Country")) Then
slist.Add(dr("Supplierid"), dr("Country"))
End If
Next
Step 3: Populate the DropDownList with the values in SortedList.
DropDownList1.DataSource = slist.GetValueList
DropDownList1.DataBind()
Note : In case of Populating a Datalist
In webform1.aspx
<asp:DataList id="DataList1" runat="server">
<ItemTemplate>
<%#Container.DataItem %>
</ItemTemplate>
</asp:DataList>
In webform1.aspx.vb
DataList1.DataSource = slist.GetValueList
DataList1.DataBind()