'____________________________________________________________________________________________

'                                              U S I N G   B O U N D   C O N T R O L S
'                                                                  Ron Kessler
'                                                              Created 1/17/07
'____________________________________________________________________________________________

'PURPOSE:
'   This application shows you how to use a combo box to select customers and display their list of purchases in a grid.
'   The program has two dataviewgrids. One shows all the customers when the program loads. Then it displays a selected
'   customer when chosen for a list.

'   The second grid shows records from the Orders table that match the chosen customer.

'DATABASE Notes:

'   This app uses customers-05.mdf. It is a SQL 2005 database with three tables: customers, orders, & products.
'   It was designed within VS2005 using SQL Server 2005 Express.
'   The app uses Integrated security to access the DB.
'   The DB connection string is saved in the app.config file in this project.


'ADO notes:
'1. I use one dataset to manage the customer queries. I use two tables inside of myDS(customers & selected). Customers holds
'    the entire list of records and is used to bind the combo box.
'2. I declare one data adapter (myDA) at class-level but do not instantiate it until needed in each sub. 
'3. Note how I check to see if a DS table exists before clearing it. You must clear the table or you get duplicate data.
'   By the way, this is how you can clear the datagridview control since it has no clear method.
'4. One connection sting is used and stored in app.config
'5. I use one connection object for the entire app

'User Notes:

'1. To test this app, choose Joe Tran or C.Weed in the customer list to see their purchases. Not all customers have data.
'2. Make sure to edit the connection string in app.config to match the name of your SQL server.

Option Explicit On
Option Strict On

Imports System.Data                    'this is used for the dataset
Imports System.Data.SqlClient        'this gives access to SQL ADO stuff and error messages


Public Class frmMain

    Dim myConnString As String = My.Settings.customers_05     'conn string is in app.config file
    Dim Conn As New SqlConnection(myConnString)

    Dim myDS As New DataSet     'this controls the combobox list and is used for all customer queries. I use 2 tables(customers & selected).
    '
    Dim sqlString As String = "Select * From Customers Order by Name"
    Dim myDA As SqlDataAdapter

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

        InitializeDB()


    End Sub

    ''' <summary>
    ''' This fills the combo box and customer grid upon loading the form
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub InitializeDB()
        '---fill the listbox with customer names 
        Try

            sqlString = "Select * From Customers Order by Name"
            myDA = New SqlDataAdapter(sqlString, Conn)

            myDS.Clear()                           'start fresh
            myDA.Fill(myDS, "customers")

            '---bind the combo box here for the entire program
            cboCustomers.DataSource = myDS.Tables("customers")
            cboCustomers.DisplayMember = "Name"                        'this is what is displayed
            cboCustomers.ValueMember = "CustID"                        'this is the key field I use to ID which customer ID is selected

            '---now bind the grid
            dgCustomers.DataSource = myDS
            dgCustomers.DataMember = "customers"                      'data grids bind to the name of the DS table

        Catch ex As Exception
            MsgBox("I could not fill the listbox of customers..." & Err.Description, MsgBoxStyle.Critical, "System Message")
        End Try

    End Sub

    Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
        SelectCustomer()
        ShowPurchases()

    End Sub

    ''' <summary>
    ''' This sub displays the selected customer in the top grid. Notice I use a different Dataset table (selected) for this query.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SelectCustomer()
        Try

            '---get the customer they chose in the combo box. We grab the CustID because it is assigned to the combobox.ValueMember prop.
            Dim currentRecord As Integer = CInt(cboCustomers.SelectedValue)

            '---build the query string using the SelectedValue (which is the CustID) for the item chosen in the combo box
            Dim sql As String = "Select * From Customers Where Customers.CustID =  " & currentRecord & " "
           
            myDA = New SqlDataAdapter(sql, Conn)

            '---if this is the first time through, the table = nothing so we get an error
            If Not myDS.Tables("selected") Is Nothing Then
                myDS.Tables("selected").Clear()
            End If


            myDA.Fill(myDS, "selected")                   'make it so!

            dgCustomers.DataSource = myDS
            dgCustomers.DataMember = "selected"     'bind to the entire table

        Catch ex As Exception
            MsgBox("I could not fill the listbox of customers..." & Err.Description, MsgBoxStyle.Critical, "System Message")
        End Try

    End Sub

    ''' <summary>
    ''' Gets records from the Orders table based on the Customer chosen in the combo box
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ShowPurchases()

        Try

            '---get the customer they chose in the combo box. We grab the CustID because it is assigned to the combobox.ValueMember prop.
            Dim currentRecord As Integer = CInt(cboCustomers.SelectedValue)

            '---build the query string using the SelectedValue (which is the CustID) for the item chosen in the combo box
            Dim sql As String = "Select * From Orders Where Orders.CustID = " & currentRecord & "  "


            '---if this is the first time through, the table = nothing so we get an error
            If Not myDS.Tables("orders") Is Nothing Then
                myDS.Tables("orders").Clear()
            End If

            myDA = New SqlDataAdapter(sql, Conn)
            myDA.Fill(myDS, "orders")

            '---bind grid to the correct DS table
            dgOrders.DataSource = myDS
            dgOrders.DataMember = "orders"

            '---make some alignment formatting changes in the grid
            dgOrders.Columns("Price").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgOrders.Columns("Tax").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgOrders.Columns("TotalSale").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

            '---to show the $ sign, use c or c2 instead of n2. The n2 means format as number with 2 decimals & no $ sign
            dgOrders.Columns("Price").DefaultCellStyle.Format = "n2"
            dgOrders.Columns("Tax").DefaultCellStyle.Format = "n2"
            dgOrders.Columns("TotalSale").DefaultCellStyle.Format = "n2"

        Catch ex As Exception
            MsgBox("I could not display the orders for this customer..." & Err.Description, MsgBoxStyle.Critical, "System Message")
        End Try

    End Sub
End Class