Bare Bones DataReader

'------------------------------------------------------------------------------------------
'           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:   Bare Bones Data Reader
'
' Author:         Joseph L. Bolen
' Date Created:   11 MAY 2016
'
' Description:    This database inquiry uses the DataReader to quickly
'                 retrieve records from a table and display them in a datagridview.
'                 For this demonstration, the Customers table from the MS Access
'                 database Northwind is being used. The database's file location
'                 has been hardcoded.
'
'                 To choose the correct ConnectionString,
'                   see http://www.connectionstrings.com/ .
'
'                 Documentation is at:
'                   App's screen image is at: http://imgur.com/dQEpAZt
'                   App's Visual Basic .NET code is at http://pastebin.com/S7mJtk0Q
'                   Video tutorial at YouTube: http://www.youtube.com/user/bolenpresents
'-------------------------------------------------------------------------------------------

Imports System.Data.OleDb       ' Using a MS Access database

Public Class InquiryForm

    ' Normally, the ConnectionString is retrieved from the App.config file.
    Const CONN_STRING As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                            "Data Source=U:\Databases\AccessDatabases\Northwind.accdb;" &
                            "Persist Security Info=False;"

    Private bs As New BindingSource

    ' Form Load - Initialization and Housekeeping.
    Private Sub InquiryForm_Load(sender As Object, e As EventArgs) _
        Handles MyBase.Load

        ' DataGridView property changes that could be done in the design mode.
        With InquiryDGV
            '.Dock = DockStyle.Fill
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = True
            .AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke
            .Anchor = (AnchorStyles.Left Or AnchorStyles.Top Or
                AnchorStyles.Right Or AnchorStyles.Bottom)
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            .BorderStyle = BorderStyle.Fixed3D
            .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .ReadOnly = True

            ' DataGridView Column Headers Bold – must be set in run mode.
            .ColumnHeadersDefaultCellStyle.Font =
                New Font(.ColumnHeadersDefaultCellStyle.Font, FontStyle.Bold)
        End With

    End Sub

    ' Filter and sort data from the database and display in a datagridview.
    Private Sub SearchToolStripButton_Click(sender As Object, e As EventArgs) _
        Handles SearchToolStripButton.Click

        ' Best Practise is to list fields to be selected. NOT THE WILDCARD!
        ' Dim query As String = "SELECT * " &
        Dim query As String = "SELECT Company, [Last Name], [First Name], " &
                                    "[Job Title], [Business Phone], [Fax Number] " &
                                "FROM Customers " &
                                "WHERE [Last Name] Like @p1 " &
                                "ORDER BY [Last Name], [First Name];"

        Try
            Using con As New OleDbConnection(CONN_STRING)
                Using cmd As New OleDbCommand(query, con)
                    ' ANSI-89 wildcard character is the asterisk (*).
                    ' ANSI-92 wildcard characters is the percent sign (%).
                    cmd.Parameters.AddWithValue("@p1", LastNameToolStripTextBox.Text & "%")

                    con.Open()
                    Using rdr As OleDbDataReader = cmd.ExecuteReader
                        If rdr.HasRows Then
                            bs.DataSource = rdr
                            InquiryDGV.DataSource = bs
                        Else
                            MessageBox.Show("Search criteria yielded no records.",
                                            Me.Text,
                                            MessageBoxButtons.OK,
                                            MessageBoxIcon.Information)
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message,
                            Me.Text,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error)
        End Try

        ' Place cursor back into Textbox for next search.
        LastNameToolStripTextBox.SelectAll()
        LastNameToolStripTextBox.Focus()

    End Sub

End Class