ComboBox Load From Database Demo

'------------------------------------------------------------------------------------------
'           Notice of My Copyright and Intellectual Property Rights
'
' Any intellectual property contained within the program by Joseph L. Bolen remains the
' intellectual property of the Joseph L. Bolen. This means that no person may distribute,
' publish or provide such intellectual property to any other person or entity for any
' reason, commercial or otherwise, without the express written permission of Joseph L. Bolen.
'
'                 Copyright © 2016. All rights reserved.
'        All trademarks remain the property of their respective owners.
'-------------------------------------------------------------------------------------------
' Program Name:   ComboBox Load From Database Demo
'
' Author:         Joseph L. Bolen
' Date Created:   09 AUG 2016
'
' Description:    Show how to load ComboBox using DataReader (or DataAdapter).
'
'                 Documentation is at:
'                   App's Visual Basic .NET code is at http://pastebin.com/Em4nrNx6
'
'-------------------------------------------------------------------------------------------

'Note: Add Reference to project - System.Configuration and code connection string in the
'      app.config file.

Imports System.Configuration
Imports System.Data.SqlClient

Public Class MainForm

    Private Sub MainForm_Load(sender As Object, e As EventArgs) _
        Handles MyBase.Load

        LoadComboBox()
    End Sub

    Private Sub LoadComboBox()

        Dim conn As String = ConfigurationManager.ConnectionStrings("SimpleDB").ConnectionString

        ' Since FirstName is nullable in this table, the build of FullName expression examines
        ' FirstName for null. See http://www.w3schools.com/sql/sql_isnull.asp for SQL NULL Functions.

        Dim query As String = "SELECT ContactId, LastName + ', ' + ISNULL(FirstName,'') as FullName " &
                              "FROM Contacts " &
                              "ORDER BY LastName, FirstName;"
        Dim dt As New DataTable

        Try
            Using con As New SqlConnection(conn)
                Using cmd As New SqlCommand(query, con)
                    con.Open()
                    Using rdr As SqlDataReader = cmd.ExecuteReader
                        dt.Load(rdr)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try

        ' OR ...
        ' Using a DataAdapter

        'Try
        '    Using adapter As New SqlDataAdapter(query, conn)
        '        adapter.Fill(dt)
        '    End Using
        'Catch ex As Exception
        '    Throw ex
        'End Try

        With ComboBox1
            .IntegralHeight = False
            .MaxDropDownItems = 8
            .DataSource = dt
            .DisplayMember = "FullName"
            .ValueMember = "ContactId"
            .SelectedIndex = -1
        End With

        AddHandler ComboBox1.SelectedIndexChanged, AddressOf ComboBox1_SelectedIndexChanged
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs)

        If ComboBox1.SelectedIndex <> -1 Then
            TextBox1.Text = ComboBox1.GetItemText(ComboBox1.SelectedItem)

            ' To Retreive the data, use the SelectedValue of the combobox.
            ' Dim id As Integer = CType(ComboBox1.SelectedValue, Integer)
            ' DisplayContact(id)

            ComboBox1.SelectedIndex = -1
        End If
    End Sub
End Class

' Also, modify the app.config file to include:
    <connectionStrings>
        <add name="SimpleDB"
            connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=SimpleDB;Integrated Security=True;"
            providerName="System.Data.SqlClient"/>
    </connectionStrings>