clear dataset

The following code would manipulate data on a dataset.

This code clears the dataset:

myDataset.Clear


This code adds table to the dataset:

myDataset.Tables.Add("myTable")


This code will remove table from a dataset:

myDataset.Tables.Remove("myTable")


dynamically adding items to a dropdownlist

There are two ways to add items to a dropdownlist:

a. inserting items extracted from database one at a time

command = New Data.SqlClient.SqlCommand("SELECT * FROM TABLE", connection)
reader = command.ExecuteReader()
Dim items As New ListItem()
Do While reader.Read()
items = New ListItem()
items.Text = reader("TABLE_COLUMN")
items.Value = reader("TABLE_PK")
dropdownlist.Items.Add(items)
Loop
reader.Close()

b. by binding it to a dataset datasource

command = New Data.SqlClient.SqlCommand("SELECT * FROM TABLE", connection)
reader = command.ExecuteReader()
If reader.Read() Then
reader.Close()
adapter.SelectCommand = New Data.SqlClient.SqlCommand("SELECT * FROM TABLE", connection)
adapter.Fill(dataSet)
dropdownlist.DataSource = dataSet.Tables(0)
dropdownlist.DataBind()
End If
reader.Close()


The advantage of the first option is that, a default selected value can be displayed. This can be done with this code:

dropdownlist.Items.Insert(0, "Select")

While on the second option, data retrieved are the ones displayed on the dropdownlist. The first to be extracted is the one displayed as default value.

Both actions would require you to open your connection by:

connection.Open()

And then closing it after using:

connection.Close()