' ASPX_OleDbDataReader_DropDownList_from_Northwind_Customer_Country.aspx.vb.txt
'
' Written By Dan Garlen 2/04
'
' Create a new WebForm
' Add the following Imports to top of page
    Imports db = System.Data.OleDb

' Add DropDown List Control (Leave default name: "DropDownList1")
' Add Label (Change name to: "lblCountry")
'
'
' Add the following three Subroutines to page
'   Page_Load
'   NorthwindDataReader
'   DropDownList1_SelectedIndexChanged
'
'

' -------------------------------------------------------------------------------------------
    Private Sub Page_Load(ByVal sender As System.Object, _
				ByVal e As System.EventArgs) _
				Handles MyBase.Load
        If Not Page.IsPostBack Then
            NorthwindDataReader()
        End If

    End Sub
' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

' -------------------------------------------------------------------------------------------
    Private Sub NorthwindDataReader()
        Dim strSQL As String = "SELECT DISTINCT Country FROM [Customers] ORDER BY [Country]"
        Dim strMDB As String = "C:\TestData\Northwind.mdb"
        Dim strDB As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" + strMDB + ";"

        Dim cn As New db.OleDbConnection
        cn.ConnectionString = strDB

        Try

            Dim cmd As New db.OleDbCommand ' Setup Command
            cmd.CommandText = strSQL
            cmd.Connection = cn

            Dim dr As db.OleDbDataReader
            cn.Open()
            dr = cmd.ExecuteReader

            Do While dr.Read
                Dim strCountry As String = dr.Item("Country").ToString
                If strCountry.Trim.Length > 0 Then
                    DropDownList1.Items.Add(strCountry)
                End If
            Loop
            dr.Close()
            cn.Close()
        Catch ex As Exception
            DropDownList1.Items.Add(ex.Message)
        End Try
    End Sub
' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

' -------------------------------------------------------------------------------------------
    Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, _
						ByVal e As System.EventArgs) _
						Handles DropDownList1.SelectedIndexChanged
        lblCountry.Text = DropDownList1.SelectedItem.Text
    End Sub

' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^