'____________________________________________________________________________________________
' 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
'''
''' This fills the combo box and customer grid upon loading the form
'''
'''
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
'''
''' This sub displays the selected customer in the top grid. Notice I use a different Dataset table (selected) for this query.
'''
'''
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
'''
''' Gets records from the Orders table based on the Customer chosen in the combo box
'''
'''
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