Transcript Slide 1

Chapter 4
How to work with
bound controls and
parameterized queries
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
 Format the data in a bound text box by setting the properties for the
control.
 Apply custom formatting to the data in a control by using the Format
event of the Binding object for the control, and remove the formatting
by using the Parse event of the Binding object for the control.
 Bind a combo box to a data source.
 Use the properties and methods of the BindingSource class to
navigate and modify the rows in a dataset.
 Create and use a parameterized query with a data source.
 Customize a ToolStrip control by adding controls to it, deleting
controls from it, and formatting the controls that are on it. Then, code
the event handlers for making these controls work.
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 2
Objectives (continued)
 Customize the appearance and operation of a DataGridView control.
 Use a DataGridView control as part of a Master/Detail form.
 Use the properties and methods of the DataGridView,
DataGridViewRow, and DataGridViewCell classes to work with the
data in a DataGridView control.
Knowledge
 Explain why you might want to use code to work directly with the
binding source object for a data source.
 Describe the use of a parameterized query and the ToolStrip control
that gets generated for the query.
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 3
The dialog box for formatting to a column
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 4
A procedure that formats a phone number
Private Sub FormatPhoneNumber(ByVal sender As Object,
ByVal e As ConvertEventArgs)
If e.Value.GetType.ToString = "System.String" Then
Dim s As String = e.Value.ToString
If IsNumeric(s) Then
If s.Length = 10 Then
e.Value = s.Substring(0, 3) & "." &
s.Substring(3, 3) & "." &
s.Substring(6, 4)
End If
End If
End If
End Sub
Wiring the procedure to the Format event of a text box
Dim b As Binding = PhoneTextBox.DataBindings("Text")
AddHandler b.Format, AddressOf FormatPhoneNumber
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 5
A procedure that removes the formatting
Private Sub UnformatPhoneNumber(ByVal sender As Object,
ByVal e As ConvertEventArgs)
If e.Value.GetType.ToString = "System.String" Then
Dim s As String = e.Value.ToString
e.Value = s.Replace(".", "")
End If
End Sub
Wiring the procedure to the Parse event
of a Binding object
AddHandler b.Parse, AddressOf UnformatPhoneNumber
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 6
A combo box that’s bound to a data source
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 7
Combo box properties for binding
Property
DataSource
Description
The data table that contains the data displayed in
the list.
DisplayMember The data column whose data is displayed in the list.
ValueMember The data column whose value is stored in the list.
SelectedValue Gets the value of the currently selected item.
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 8
Common properties of the BindingSource class
Property
Position
Count
ADO.NET 4 VB, C4
Description
The index of the current row in the data source.
The number of rows in the data source.
© 2011, Mike Murach & Associates, Inc.
Slide 9
Common methods of the BindingSource class
Method
AddNew()
EndEdit()
CancelEdit()
RemoveCurrent()
MoveFirst()
MovePrevious()
MoveNext()
MoveLast()
ADO.NET 4 VB, C4
Description
Adds a new, blank row to the data source.
Saves changes to the current row.
Cancels changes to the current row.
Removes the current row from the data source.
Moves to the first row in the data source.
Moves to the previous row in the data source, if
there is one.
Moves to the next row in the data source, if there
is one.
Moves to the last row in the data source.
© 2011, Mike Murach & Associates, Inc.
Slide 10
A statement that adds a new row to a data source
Me.VendorsBindingSource.AddNew()
A statement that saves the changes to the current
row and ends the edit
Me.VendorsBindingSource.EndEdit()
A statement that cancels the changes to the
current row
Me.VendorsBindingSource.CancelEdit()
A statement that removes the current row from a
data source
Me.VendorsBindingSource.RemoveCurrent()
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 11
Code that moves to the next row and displays the
position and count
Private Sub btnNext_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnNext.Click
Me.VendorsBindingSource.MoveNext()
Dim position As Integer =
VendorsBindingSource.Position + 1
txtPosition.Text =
position & " of " &
VendorsBindingSource.Count
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 12
The dialog box for creating a parameterized query
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 13
The Vendor Maintenance form
with a query toolbar
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 14
The generated code for a parameterized query
Private Sub FillByVendorIDToolStripButton_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles FillByVendorIDToolStripButton.Click
Try
Me.VendorsTableAdapter.FillByVendorID(
Me.PayablesDataSet.Vendors,
CType(VendorIDToolStripTextBox.Text, Integer))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 15
The same code after it has been cleaned up
Private Sub FillByVendorIDToolStripButton_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles FillByVendorIDToolStripButton.Click
Try
Dim vendorID As Integer =
CInt(VendorIDToolStripTextBox.Text)
Me.VendorsTableAdapter.FillByVendorID(
Me.PayablesDataSet.Vendors, vendorID)
Catch ex As InvalidCastException
MessageBox.Show("Vendor ID must be an integer.",
"Entry Error")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " & ex.Number &
": " & ex.Message, ex.GetType.ToString)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 16
The syntax for filling a table
with a parameterized query
TableAdapter.QueryName(DataSet.TableName,
param1 [,param2]...)
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 17
The Items Collection Editor for a ToolStrip control
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 18
Common properties of ToolStrip items
Property
DisplayStyle
Image
Text
Width
ADO.NET 4 VB, C4
Description
Indicates whether a button displays an image, text,
or both an image and text.
The image that’s displayed on a button if you select
Image or ImageAndText for the DisplayStyle
property.
The text that’s displayed on a button if you select
Text or ImageAndText for the DisplayStyle
property.
The width of the item.
© 2011, Mike Murach & Associates, Inc.
Slide 19
Customized toolbars
for a Vendor Maintenance application
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 20
The event handler for the Cancel button on the
BindingNavigator ToolStrip control
Private Sub BindingNavigatorCancelItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles BindingNavigatorCancelItem.Click
VendorsBindingSource.CancelEdit()
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 21
The event handler for the Get Vendor button on
the FillByVendorID ToolStrip control
Private Sub FillByVendorIDToolStripButton_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles FillByVendorIDToolStripButton.Click
Try
Dim vendorID As Integer =
CInt(VendorIDToolStripTextBox.Text)
Me.VendorsTableAdapter.FillByVendorID(
Me.PayablesDataSet.Vendors, vendorID)
Catch ex As InvalidCastException
MessageBox.Show("Vendor ID must be an integer.",
"Entry Error")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " &
ex.Number & ": " & ex.Message,
ex.GetType.ToString)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 22
The Vendor Maintenance application
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 23
The code for the Vendor Maintenance application
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim b As Binding = PhoneTextBox.DataBindings("Text")
AddHandler b.Format, AddressOf FormatPhoneNumber
Try
Me.StatesTableAdapter.Fill(
Me.PayablesDataSet.States)
Me.VendorsTableAdapter.Fill(
Me.PayablesDataSet.Vendors)
Catch ex As SqlException
MessageBox.Show("SQL Server error # " &
ex.Number & ": " & ex.Message,
ex.GetType.ToString)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 24
The Vendor Maintenance application (cont.)
Private Sub FormatPhoneNumber(ByVal sender As Object,
ByVal e As ConvertEventArgs)
If e.Value.GetType.ToString = "System.String" Then
Dim s As String = e.Value.ToString
If IsNumeric(s) Then
If s.Length = 10 Then
e.Value = s.Substring(0, 3) & "." &
s.Substring(3, 3) & "." &
s.Substring(6, 4)
End If
End If
End If
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 25
The Vendor Maintenance application (cont.)
Private Sub FillByVendorIDToolStripButton_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles FillByVendorIDToolStripButton.Click
Try
Dim vendorID As Integer =
CInt(VendorIDToolStripTextBox.Text)
Me.VendorsTableAdapter.FillByVendorID(
Me.PayablesDataSet.Vendors, vendorID)
Catch ex As InvalidCastException
MessageBox.Show("Vendor ID must be an integer.",
"Entry Error")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " &
ex.Number & ": " & ex.Message,
ex.GetType.ToString)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 26
The Vendor Maintenance application (cont.)
Private Sub BindingNavigatorCancelItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles BindingNavigatorCancelItem.Click
VendorsBindingSource.CancelEdit()
End Sub
Private Sub VendorsBindingNavigatorSaveItem_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs)
Handles VendorsBindingNavigatorSaveItem.Click
If IsValidData() Then
Try
Me.VendorsBindingSource.EndEdit()
Me.VendorsTableAdapter.Update(
Me.PayablesDataSet.Vendors)
Catch ex As ArgumentException
' This block catches exceptions such as a
' value that's beyond the maximum length for
' a column in a dataset.
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 27
The Vendor Maintenance application (cont.)
MessageBox.Show(ex.Message,
"Argument Exception")
VendorsBindingSource.CancelEdit()
Catch ex As DBConcurrencyException
MessageBox.Show("A concurrency error " &
"occurred. The row was not updated.",
"Concurrency Exception")
Me.VendorsTableAdapter.Fill(
Me.PayablesDataSet.Vendors)
Catch ex As DataException
MessageBox.Show(ex.Message,
ex.GetType.ToString)
VendorsBindingSource.CancelEdit()
Catch ex As SqlException
MessageBox.Show("SQL Server error # " &
ex.Number & ": " & ex.Message,
ex.GetType.ToString)
End Try
End If
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 28
The Vendor Maintenance application (cont.)
Private Function IsValidData() As Boolean
If VendorsBindingSource.Count > 0 Then
Return IsPresent(NameTextBox, "Name") AndAlso
IsPresent(Address1TextBox, "Address1") AndAlso
IsPresent(CityTextBox, "City") AndAlso
IsPresent(StateComboBox, "State") AndAlso
IsPresent(ZipCodeTextBox, "Zip code")
Else
Return True
End If
End Function
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 29
The Vendor Maintenance application (cont.)
Private Function IsPresent(ByVal control As Control,
ByVal name As String) As Boolean
If control.GetType.ToString =
"System.Windows.Forms.TextBox" Then
Dim textBox As TextBox = CType(control, TextBox)
If textBox.Text = "" Then
MessageBox.Show(
name & " is a required field.",
"Entry Error")
textBox.Select()
Return False
Else
Return True
End If
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 30
The Vendor Maintenance application (cont.)
ElseIf control.GetType.ToString =
"System.Windows.Forms.ComboBox" Then
Dim comboBox As ComboBox =
CType(control, ComboBox)
If comboBox.SelectedIndex = -1 Then
MessageBox.Show(
name & " is a required field.",
"Entry Error")
comboBox.Select()
Return False
Else
Return True
End If
End If
End Function
End Class
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 31
The smart tag menu for a DataGridView control
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 32
The dialog box for editing a DataGridView control
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 33
Common properties of a column
Property
HeaderText
Width
Description
The text that’s displayed in the column header.
The number of pixels that are used for the width of
the column.
DefaultCellStyle The style that’s applied to the cell.
Visible
Determines if the column is visible in the control.
ReadOnly
Determines if the data in the column can be
modified.
SortMode
Determines if the data in the grid can be sorted by
the values in the column and how the sorting is
performed.
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 34
The CellStyle Builder dialog box
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 35
The Format String dialog box
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 36
The Add Column dialog box
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 37
Common properties for button columns
Text
UseColumnTextForButtonValue
A DataGridView control
with an unbound button column
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 38
A DataGridView control that displays data
from a related table
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 39
Two BindingSource properties
for displaying data from a related table
DataSource
DataMember
The property settings for the
InvoicesBindingSource object
Property
DataSource
DataMember
ADO.NET 4 VB, C4
Setting
VendorsBindingSource
FK_Invoices_Vendors
© 2011, Mike Murach & Associates, Inc.
Slide 40
Properties and methods for working with a
DataGridView control
Class
DataGridView
DataGridView
DataGridView
DataGridViewRow
DataGridViewCell
DataGridViewCell
ADO.NET 4 VB, C4
Property/Method
Rows
CurrentCell
Refresh
Cells
Value
RowIndex
© 2011, Mike Murach & Associates, Inc.
Slide 41
How to get the index of the currently selected row
If InvoicesDataGridView.Rows.Count > 0 Then
Dim rowIndex As Integer =
InvoicesDataGridView.CurrentCell.RowIndex
End If
How to get the value of a cell from a row
Dim row As DataGridViewRow =
InvoicesDataGridView.Rows(rowIndex)
Dim cell As DataGridViewCell = row.Cells(0)
Dim invoiceID As Integer = CInt(cell.Value)
How to set the value of the first cell in the
selected row
Dim invoiceID As Integer = 10
Dim row As DataGridViewRow =
InvoicesDataGridView.Rows(rowIndex)
row.Cells(0).Value = invoiceID
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 42
How to loop through all rows in the grid
Dim grandTotal As Decimal
For Each row As DataGridViewRow _
In InvoicesDataGridView.Rows
Dim cell As DataGridViewCell = row.Cells(3)
Dim invoiceTotal As Decimal = CDec(cell.Value)
grandTotal += invoiceTotal
Next
How to refresh the data grid
InvoicesDataGridView.Refresh()
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 43
A method that handles the CellDoubleClick event
Private Sub InvoicesDataGridView_CellDoubleClick(
ByVal sender As System.Object,
ByVal e As _
System.Windows.Forms.DataGridViewCellEventArgs) _
Handles InvoicesDataGridView.CellDoubleClick
Dim rowIndex As Integer = e.RowIndex
Me.DisplayLineItems(rowIndex)
End Sub
How to delete a row
InvoicesDataGridView.Rows.RemoveAt(rowIndex)
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 44
The Invoice Maintenance form
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 45
The Line Items form
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 46
The dataset schema
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 47
The code for the Invoice Maintenance form
Imports System.Data.SqlClient
Public Class frmInvoiceMaintenance
Private Sub FillByVendorIDToolStripButton_Click(
ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles FillByVendorIDToolStripButton.Click
Try
Dim vendorID As Integer =
CInt(VendorIDToolStripTextBox.Text)
Me.VendorsTableAdapter.FillByVendorID(
Me.PayablesDataSet.Vendors, vendorID)
Me.InvoicesTableAdapter.FillByVendorID(
Me.PayablesDataSet.Invoices, vendorID)
Catch ex As InvalidCastException
MessageBox.Show("Vendor ID must be an integer.",
"Entry Error")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " & ex.Number &
": " & ex.Message, ex.GetType.ToString)
End Try
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 48
The code for the Invoice Maintenance form (cont.)
Private Sub InvoicesDataGridView_DataError(
ByVal sender As System.Object,
ByVal e As _
System.Windows.Forms.DataGridViewDataErrorEventArgs) _
Handles InvoicesDataGridView.DataError
Dim row As Integer = e.RowIndex + 1
Dim errorMessage As String =
"A data error occurred." &
vbCrLf & "Row: " & row & vbCrLf &
"Error: " & e.Exception.Message
MessageBox.Show(errorMessage, "Data Error")
End Sub
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 49
The code for the Invoice Maintenance form (cont.)
Private Sub InvoicesDataGridView_CellContentClick(
ByVal sender As System.Object,
ByVal e As _
System.Windows.Forms.DataGridViewCellEventArgs) _
Handles InvoicesDataGridView.CellContentClick
If e.ColumnIndex = 8 Then
' View Line Items button clicked
' Get the ID of the selected invoice
Dim i As Integer = e.RowIndex
Dim row As DataGridViewRow =
InvoicesDataGridView.Rows(i)
Dim cell As DataGridViewCell = row.Cells(0)
Dim invoiceID As Integer = CInt(cell.Value)
' Display the Line Items form
Dim lineItemsForm As New frmLineItems
lineItemsForm.Tag = invoiceID
lineItemsForm.ShowDialog()
End If
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 50
The code for the Invoice Maintenance form (cont.)
Private Sub btnUpdate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
Me.InvoicesBindingSource.EndEdit()
Me.InvoicesTableAdapter.Update(
Me.PayablesDataSet.Invoices)
Catch ex As DBConcurrencyException
MessageBox.Show("A concurrency error occurred.",
"Concurrency Error")
Me.InvoicesTableAdapter.Fill(
Me.PayablesDataSet.Invoices)
Catch ex As DataException
MessageBox.Show(ex.Message, ex.GetType.ToString)
Me.InvoicesBindingSource.CancelEdit()
Catch ex As SqlException
MessageBox.Show("Database error # " &
ex.Number & ": " & ex.Message,
ex.GetType.ToString)
End Try
End Sub
End Class
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 51
The code for the Line Items form
Imports System.Data.SqlClient
Public Class frmLineItems
Private Sub frmLineItems_Load(
ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Try
' Get the invoice ID
Dim invoiceID As Integer = CInt(Me.Tag)
' Fill the InvoiceLineItems table
Me.InvoiceLineItemsTableAdapter.FillByInvoiceID(
Me.PayablesDataSet.InvoiceLineItems,
invoiceID)
Catch ex As InvalidCastException
MessageBox.Show("Invoice ID not an integer.",
"Property Error")
Catch ex As SqlException
MessageBox.Show("SQL Server error # " & ex.Number &
": " & ex.Message, ex.GetType.ToString)
End Try
End Sub
End Class
ADO.NET 4 VB, C4
© 2011, Mike Murach & Associates, Inc.
Slide 52