'Copyright 2002 Brian J. Reithel, Ph.D., CDP

Imports System.Data
Imports System.Data.SqlClient

Public Class InquiryWithDBPopulatedDropDown
    ' 
    ' It is important to notice the classes in use in this object
    ' because those classes provide most of the functionality you
    ' will use as a .NET developer
    '
    Inherits System.Web.UI.Page
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents ResultGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents ClearButton As System.Web.UI.WebControls.Button
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents DropDownNames As System.Web.UI.WebControls.DropDownList

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ' If this is the first time the page is loaded then 
        '         load the drop-down list item
        '         with values from the database
        '
        If Not IsPostBack Then
            Dim sDSN As String
            Dim DS As DataSet
            Dim MyConnection As SqlConnection
            Dim MyCommand As SqlDataAdapter
            Dim MySQL As String

            sDSN = "server=student.bus.olemiss.edu;uid=dbdemo;pwd=dbpass;database=EmailDemo"
            MySQL = "SELECT DISTINCT StudentName from Students ORDER BY StudentName"

            MyConnection = New SqlConnection(sDSN)
            MyCommand = New SqlDataAdapter(MySQL, MyConnection)

            DS = New DataSet()
            MyCommand.Fill(DS, "Students")

            DropDownNames.DataSource = DS.Tables("Students").DefaultView
            DropDownNames.DataTextField = "StudentName"
            DropDownNames.DataValueField = "StudentName"
            DropDownNames.DataBind()
            DropDownNames.Items.Insert(0, "< Select a Name >")

            ResultGrid.Visible = False
            ClearButton.Visible = False

            MyCommand.Dispose()
            MyConnection.Dispose()
            DS.Dispose()
        End If

    End Sub

    Private Sub DropDownNames_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownNames.SelectedIndexChanged
        Dim sDSN As String
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        Dim MySQL As String

        sDSN = "server=student.bus.olemiss.edu;uid=dbdemo;pwd=dbpass;database=EmailDemo"
        MySQL = "Select StudentEmail from Students WHERE StudentName ='" & DropDownNames.SelectedItem.Text & "'"

        MyConnection = New SqlConnection(sDSN)
        MyCommand = New SqlDataAdapter(MySQL, MyConnection)

        DS = New DataSet()
        MyCommand.Fill(DS, "Students")

        ResultGrid.DataSource = DS.Tables("Students").DefaultView
        ResultGrid.DataBind()
        ResultGrid.ShowHeader = False
        ResultGrid.Visible = True

        ClearButton.Visible = True

        MyCommand.Dispose()
        MyConnection.Dispose()
        DS.Dispose()

    End Sub

    Private Sub ClearButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearButton.Click
        ' make the button and grid invisible and then 
        ' reset the dropdown box to the "< Select a Name >" list item
        '
        ResultGrid.Visible = False
        ClearButton.Visible = False
        DropDownNames.SelectedIndex = 0
    End Sub

End Class