Database Parameterized Queries

1
Database Parameterized Queries

• Find records and number of records meeting
  certain criteria

2
User input
3
Search by name
4
Two ways to filter information using 1. Table
Adapter 2. Bindingsource
Database
Project
Form1 Form2
customer
Binding- Source
Table Adapter
part
invoice
SQL
SQL

5
Filter information using Table Adapter
6
(No Transcript)
7
A user-name and password table
8
Create a dataset called dataset1.
9
1. Double-Click
2. Right-Click
3. Click
10
Click Next gt
11
Click Next gt
12
? represents a parameter
Click Next gt
13
Click Next gt
14
Click Finish
15
(No Transcript)
16
Drag a BindingSource object into the component
tray, change its name to UserPasswordBindingSource
. Then, set its DataSource to dataset1, its
DataMember to userPassword.
17
'Note it is necessary to put a
bindingsource object into the component tray
Set its data source as dataset1 and its data
member as userPassword 'This will put a
dataAdapter into the component tray as well
Private Sub Button1_Click() Handles
Button1.Click Me.UserPasswordTableAdapter
.FillByNamePassword _
(Dataset1.userPassword, TextBox1.Text,
TextBox2.Text) If Me.UserPasswordBindingS
ource.Count 0 Then 'no match
MessageBox.Show("User name or password
incorrect", "error") TextBox1.Text
"" TextBox2.Text ""
TextBox1.Focus() Me.Text "please
try again" Else Me.Text
"login successful" End If End Sub
18
Filter information using Binding Source Object
19
User input
20
Search by name
21
BindingSource Object

• Property filter (character string, its value
  where clause of a SQL statement)

22
Private Sub ByName() Handles Button1.Click
Me.INSPECTIONTableAdapter.Fill(Me.Dataset1.INSP
ECTION) Me.INSPECTIONBindingSource.Filter
"name '" TextBox1.Text End Sub
Where name Smith
Name Textbox1.text
23
Search by month (numeric)
24
Private Sub ByMonthEqual() Handles
Button2.Click Me.INSPECTIONTableAdapter.F
ill(Me.Dataset1.INSPECTION)
Me.INSPECTIONBindingSource.Filter "month "
TextBox1.Text End Sub
Where month 6
Month Textbox1.text
25
(No Transcript)
26
(No Transcript)
27
Private Sub Form2_Load() Handles MyBase.Load
Me.INSPECTIONTableAdapter.Fill(Me.Dataset1.I
NSPECTION) Me.CUSTOMERTableAdapter.Fill(Me
.Dataset1.CUSTOMER) ListBox1.SetSelected(
0, True) Me.INSPECTIONBindingSource.Filter
"name '" ListBox1.Text "'" End Sub
Private Sub ListBox1_SelectedIndexChanged()
Handles ListBox1.SelectedIndexChanged
Me.INSPECTIONBindingSource.Filter "name '"
ListBox1.Text "' End Sub
28
Filtering leads to a restricted view of a table
in the dataset. It does not change the table.
29
Private Sub Count1_Click() Handles
Button1.Click Label1.Text
Me.INSPECTIONBindingSource.Count End Sub
Private Sub Count2_Click() Handles
Button2.Click Me.INSPECTIONBindingSource.
Filter "month 6" Label1.Text
Me.INSPECTIONBindingSource.Count End Sub
Private Sub Count3_Click() Handles
Button3.Click Me.INSPECTIONBindin
gSource.Filter "" 'remove filter
Label1.Text Me.INSPECTIONBindingSource.Count
End Sub
30
Structure of datasets
DataTable Collection
DataRelation Collection
31
To reference a cell value

• - General form
• datasetName.tableName(x).fieldName
• where x represents row index
• For example
• dim s as string
• s dataset1.Customer(2).Name

32
DataRelations
33
Connect records using DataRelations
34
Binding for Listbox1
35
Databinding for datagridview1
Bind to a data-relation
36
Connect records using DataRelations
37
Binding for listbox1
38
Binding for DataGridView1
39
Binding for DataGridView2