Transcript Database Connectivity - Computing A level Paper 1

Visual Basic
Database Programming
Topic & Structure of the lesson
Database Programming
•Introduction
•Database Concepts
•Active X ADODB
•Add Records
•Update
•Delete Records
Visual Basic
Slide 2 of 75
Learning Outcomes
Database Programming
At the end of this lecture, you should be able to :
1. Create a database table
2. Use Active X Data Objects (ADODB)
3. Write Structured Query Language (SQL)
4. Add Records
5. Update Records
Visual Basic
Slide 3 of 75
Key Words used in this lecture
Database Programming
If you have mastered this topic, you should be able to use
the following terms correctly in your assignments and
exams:
1. ADODB
2. Add New
3. Update
4. Open
5. Close
6. EOF
Visual Basic
Slide 4 of 75
Creating a Database Table
Database Programming
1.
2.
3.
4.
5.
Visual Basic
Start Microsoft Access
Create a table
Enter the field names
Ensure that you have a primary key field
Save the table as tblstudent
Slide 5 of 75
Active X Data Objects (ADODB)
Database Programming
•
•
•
•
Visual Basic
Newest Database Connectivity method in VB6.0.
Supports Hierarchical Flexi Grid Control.
Easier to program and debug.
Allows Connection String method to connect to a
database.
Slide 6 of 75
Steps to Connect To A Database
Database Programming
1. Select Project References
2. Select Microsoft ActiveX Data Objects 2.1
Library
3. Write the connection string to connect to a
database
Visual Basic
Slide 7 of 75
Database Connection
Database Programming
Dim cn as adodb.connection
Private Sub Form_Load()
Dim connstring As String
Set cn = New ADODB.Connection
connstring = “Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\newadodb\devin.mdb"
cn.Open connstring
End Sub
Visual Basic
Slide 8 of 75
Group Exercise
Database Programming
• Write a procedure to connect to a
database called library.mdb
Visual Basic
Slide 9 of 75
Adding A New Record
Database Programming
•
•
•
Visual Basic
Execute the recordset’s AddNew method.
This adds a new, blank record to the end of
the recordset.
Assign values to the new record.
Write the record to the database using the
recordset’s Update method.
Slide 10 of 75
Adding A New Record
Database Programming
Private Sub cmdadd_Click()
dim rs as adodb.recordset
set rs = new adodb.recordset
Dim strsql As String
strsql = "select * from login where firstname = null"
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields("id") = Val(Txtid.text)
rs.Fields("firstname") = txtfirstname.text
rs.Fields("lastname") = txtlastname.text
rs.Fields("age") = Val(txtage.text)
rs.Update
rs.Close
End Sub
Visual Basic
Slide 11 of 75
Quick Review Question
Database Programming
• Write a procedure to accept Student-ID,
student name, address, phone-number
from text boxes.
• Write the record to a student-table.
Visual Basic
Slide 12 of 75
Basic SQL Statements
Database Programming
SELECT * from TableName WHERE criteria
(*) indicates all fields from a Table
Select * From Student Where name = ‘John’
Select StudentID, Name from tblstudent
Select * from Student where name = ‘”& txtname.text & “’”
Select * from tblstudent order by name – To Sort by Name
Visual Basic
Slide 13 of 75
Quick Review Question
Database Programming
1.
Write a SQL statement to select all fields from
tblpassenger.
2.
Write a SQL statement to select passengerID, name,
address and phone from tblpassenger.
3.
Write a SQL statement to select all fields from
tblpassenger where name = Smith
4.
Write a SQL statement to select all fields from
tblpassenger where name is from a textbox input.
Visual Basic
Slide 14 of 75
Updating a Record
Database Programming
• Used to update a record or group of records
• Search the specific records using an SQL statement.
• If the record is found then update the record using
Update method.
• Use an EOF Statement to check if a record exist
• If the record is not found display an Error Message.
• Close the recordset once update is complete
Visual Basic
Slide 15 of 75
Updating a Record
Database Programming
Private Sub cmdupdate_Click()
Dim rs as adodb.recordset
‘declare recordset
Set rs = new adodb.recordset
‘set the recordset
Dim strsql As String
strsql = “select * from login where ID = “ & txtid.text & “”
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
If rs.eof then ‘Record Does Not Exist
Msgbox “Record Not Found”
Else
rs.Fields("age") = Val(txtage.text)
rs.Update
End if
rs.close
End Sub
Visual Basic
Slide 16 of 75
Quick Review Question
Database Programming
• Write a procedure to search for a Student ID and
update the student’s address and phone
number.
• If the studentID is not found display an error
message using a Msgbox.
• Use tblstudent as the table name.
Visual Basic
Slide 17 of 75
Deleting a Record
Database Programming
•
•
•
•
Visual Basic
To delete a record or group of records.
Search for a record to be deleted.
Use .EOF to check if record is found.
Use .delete command to delete the record.
Slide 18 of 75
Deleting a Record
Database Programming
Private Sub Command2_Click()
Dim strsql As String
Dim rs as adodb.recordset
Set rs = new adodb.recordset
strsql = “select * from login where firstname = ‘ ” & txtfirstname.text & “ ’ ”
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
If rs.EOF Then
MsgBox "Record Not Found"
Else
rs.Delete
End If
rs.Close
End Sub
Visual Basic
Slide 19 of 75
Quick Review Question
Database Programming
• Write a program to request for a Student-ID and
delete it from the student-table.
• If the Student-ID is not found display an error
message using a MsgBox.
Visual Basic
Slide 20 of 75
Question and Answer Session
Database Programming
Q&A
Visual Basic
Slide 21 of 75
Next Session
Database Programming
Displaying and Searching Records
Visual Basic
Slide 22 of 75
Database Programming
Displaying and Searching
Records
Visual Basic
Slide 23 of 75
Learning Outcomes
Database Programming
At the end of this lecture you should be able to:
1. Display records to a list box
2. Select records from a lit box
3. Search records using the like operator
4. Display records to a combo box
Visual Basic
Slide 24 of 75
Key Words
Database Programming
If you have mastered this topic, you should be able to use the following
terms correctly in your assignments and exams:
•
•
•
Visual Basic
List Box
Do Until
Like
Slide 25 of 75
References
Database Programming
• Database Access with Visual Basic 6
Jeffrey P Mc Manus
SAMS Publication
• Visual Basic 6 How to Program
Deitel and Deitel
Chapter 18
Visual Basic
Slide 26 of 75
Display Records to a List Box
Database Programming
Private Sub Command4_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
sql = "Select * from login"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
List1.Clear
Visual Basic
Slide 27 of 75
Display Data to a List Box
Database Programming
Do Until rs.EOF
List1.AddItem rs.Fields("firstname")
List1.ItemData(List1.NewIndex) = rs.Fields("ID")
rs.MoveNext
Loop
End Sub
Visual Basic
Slide 28 of 75
Group Exercise
Database Programming
• Write a procedure to display all students
name from tblstudent to a listbox.
Visual Basic
Slide 29 of 75
Search Using List Box Click Event
Database Programming
Private Sub list1_click()
Dim rs as adodb.recordset
Dim strsql As String
Set rs = new adodb.recordset
sql = "select * from login where ID = " & _
List1.ItemData(List1.ListIndex)
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
Text3.text = rs.Fields("firstname")
Text4.text = rs.Fields("lastname")
rs.Close
End Sub
Visual Basic
Slide 30 of 75
Search Using Like Operator
Database Programming
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql as string
Set rs = New ADODB.Recordset
Sql = "Select * from login where firstname Like “ & _
“ ’ ” & Trim(Text1.text) & “%’ ”
rs.Open sql, cn , adOpenStatic, adLockOptimistic, adCmdText
List1.Clear
Do Until rs.EOF
List1.AddItem rs.Fields("firstname") & " " & rs.Fields("lastname")
rs.MoveNext
Loop
rs.Close
End Sub
Visual Basic
Slide 31 of 75
Group Exercise
Database Programming
• Write a procedure to search for student’s
name beginning with the letter “A”.
• Accept the student’s name using a text
box
• Display the above records to a list box
Visual Basic
Slide 32 of 75
Display records to a Combo Box
Database Programming
Private sub form_load()
sql = "select * from login"
rs.Open sql, cn, adOpenKeyset,adLockOptimistic
Do Until rs.EOF
Combo1.AddItem rs.Fields("firstname")
rs.MoveNext
Loop
rs.Close
End sub
Visual Basic
Slide 33 of 75
Combo Click
Database Programming
Private Sub Combo1_click()
Dim sql As String
sql = "Select * from login where firstname = '" &
Combo1.text & "'"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
Text8.text = rs.Fields("ID")
Text1.text = rs.Fields("firstname")
Text2.text = rs.Fields("lastname")
Text5.text = rs.Fields("age")
rs.Close
Visual Basic
Slide 34 of 75
Form Unload
Database Programming
Private sub Form_Unload(Cancel as
Integer)
cn.close
set cn = nothing
End Sub
Visual Basic
Slide 35 of 75
Database Programming
Data Grids and Multiple Table
Handling
Visual Basic
Slide 36 of 75
Learning Outcomes
Database Programming
At the end of this lecture you should be able to :
1. Display records to a Microsoft Hierarchical Flexi
Grid (MSHFGrid)
2. Display selected records to a Microsoft Hierarchical
Flexi Grid
3. Selecting a Record on a MSHFGrid
4. Multiple Table Handling
Visual Basic
Slide 37 of 75
Key Words
Database Programming
1. MSHFGrid
2. Text Matrix
3. EOF
Visual Basic
Slide 38 of 75
References
Database Programming
• Database Access with Visual Basic 6
Jeffrey P Mc Manus
SAMS Publication
• Visual Basic 6 Programming
T Say
Prentice Hall
• Visual Basic 6 How to Program
Deitel and Deitel
Chapter 18
Visual Basic
Slide 39 of 75
MS HF Grid
Database Programming
• HF Grid looks like a spreadsheet with rows
and columns
• MSHFGrid is used to display records in a
presentable format
• MSHF Grid can be used to select an item
Visual Basic
Slide 40 of 75
Procedure
Database Programming
1. MSHF Grid must be loaded from project /
components
2. Select Project /Components and Click
Microsoft Hierarchical Flexi Grid Control 6.0
3. Click Grid on Tool Bar and draw the Grid on
the Form
4. Write Code to display records on the grid
Visual Basic
Slide 41 of 75
Displaying Records on HF Grid
Database Programming
grid1.Visible = False
- Hide the grid
Dim str As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
str = “select * from login where firstname like ‘ “ &
Trim(Text1.text) & “%’ ”
rs.Open str, cn, adOpenKeySet, adLockOptimistic
If rs.BOF Then
MsgBox “Record Not Found”
Exit Sub
End If
Visual Basic
Slide 42 of 75
Displaying Records on HF Grid
Database Programming
grid1.Refresh
Set grid1.DataSource = rs
With grid1
.TextMatrix(0, 1) = "ID"
.TextMatrix(0, 2) = "Firstname"
.TextMatrix(0, 3) = "Lastname"
.TextMatrix(0, 4) = "DOB"
.TextMatrix(0, 5) = "AGE"
End With
grid1.Visible = True - Show the Grid
rs.close
Visual Basic
Slide 43 of 75
Group Exercise
Database Programming
• Write a program to search for a student-ID
from tblstudent and display the students
particulars to a Data Grid.
• If the student ID is not found display an
error message.
Visual Basic
Slide 44 of 75
Display Selected Fields on HF Grid
Database Programming
Private Sub Command2_Click()
Dim rstitles as adodb.recordset
Set rstitles = new adodb.recordset
Dim sql ,text As String
Dim row As Integer
sql = “Select * from login where firstname like ‘” &
Text1.text & “%’ ”
rstitles.Open sql, cn, adOpenKeyset, adLockOptimistic
grdtitles.Rows = 1
If rstitles.EOF Then
rstitles.Close
Exit Sub
End If
Visual Basic
Slide 45 of 75
Display Selected Fields on HF Grid
Database Programming
Do Until rstitles.EOF
row = row + 1
text = row & vbTab &
rstitles.Fields("firstname") & vbTab &
rstitles.Fields("lastname")
grdtitles.AddItem text
rstitles.MoveNext
Loop
rstitles.Close
End Sub
Visual Basic
Slide 46 of 75
Group Exercise
Database Programming
• Write a procedure to total up all the marks
for each student in the student-table and
display the average on a text box.
• Use student as the table name
• Use rs.recordcount to get the number of
records in a table.
Visual Basic
Slide 47 of 75
Selecting a Record on a Grid
Database Programming
Private Sub grid1_Click()
a = grid1.TextMatrix(grid1.row, grid1.Col)
Msgbox a
End Sub
Visual Basic
Slide 48 of 75
Multiple Table Handling
Database Programming
• Extract data from two or more tables
• Used to display data to a report for
printing
SID
S-name Age
Phone
Related
Book ID
Visual Basic
SID
Book Title
Slide 49 of 75
Multiple Table Handling
Database Programming
Format :
Select table1.fieldname,table2.fieldname
from table1,table2
Example :
Select student.name, student.id,
library.title,library.bookid from student,
library where student.sid = library.sid
Visual Basic
Slide 50 of 75
Multiple Table Handling
Database Programming
sql = “select login.firstname, login.lastname ,profile.title,
profile.author, profile.isbn from login , profile where
profile.loginid = login.id and login.id = “& txtid.text &””
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
grid1.Refresh
Set grid1.DataSource = rs
With grid1
.TextMatrix(0, 1) = "Firstname"
.TextMatrix(0, 2) = "Lastname"
.TextMatrix(0, 3) = "Book Title"
.TextMatrix(0, 4) = "Author"
.TextMatrix(0, 5) = "ISBN"
End With
rs.Close
Visual Basic
Slide 51 of 75
Group Exercise
Database Programming
D-ID
D-Name D-Floor
D-Phone
Department -Table
Emp-ID
D-ID
Emp-Name
Employee -Table
Write an SQL statement to access the following fields :
DID, D-Name, D-Floor, Emp-ID , E-Name where a
particular D-ID exists in the Employee Table
Visual Basic
Slide 52 of 75
Group Exercise
Database Programming
• Write a procedure that will accept a user
name and a password from two text
boxes. If the user name and password is
found in the database display “Valid User”
otherwise display “Invalid User”. Use a
MsgBox to display the appropriate
message.
Visual Basic
Slide 53 of 75
Search Records Between Two Dates
Database Programming
Private Sub Command8_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
sql = "Select * FROM profile WHERE borrowdate between #" &
Format(CDate(mskstart.text), "mm/dd/yyyy") & "#AND#" &
Format(CDate(mskend.text), "mm/dd/yyyy") & "#"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
List1.Clear
If rs.EOF Then
MsgBox "RECORDS IN THAT DATE RANGE ARE NOT FOUND",
vbExclamation
Exit Sub
End If
Visual Basic
Slide 54 of 75
Search Records Between Two Dates
Database Programming
grid1.Visible = True
grid1.Rows = 1
Do Until rs.EOF
row = row + 1
text = row & vbTab & rs.Fields("Title") & vbTab & rs.Fields("author") &
vbTab & rs.Fields("isbn")
grid1.AddItem text
rs.MoveNext
Loop
grid1.FixedRows = 1 ‘To Write data on the First Row
End Sub
Visual Basic
Slide 55 of 75
Move Next – SQL statement
Database Programming
• Place a select statement in the form load to select all
records.
• Select statement should not be placed in the move next
and move previous procedure.
Private sub Form_load()
Dim rs1 as adodb.Recordset
Set rs1 = new adodb.Recordset
sql = “select * from login”
rs1.Open sql, cn, adOpenKeySet, adLockOptimistic
End sub
Visual Basic
Slide 56 of 75
Move Next
Database Programming
Private Sub Command5_Click()
rs1.MoveNext
If rs1.EOF = True Then
rs1.MoveFirst
End If
Text8.text = rs1.Fields("ID")
Text1.text = rs1.Fields("firstname")
Text2.text = rs1.Fields("lastname")
Text5.text = rs1.Fields("age")
End Sub
Visual Basic
Slide 57 of 75
Group Exercise
Database Programming
• Write a procedure to move to a previous
record
• You need to use the following key words
BOF
IF….ELSE…THEN
MovePrevious
Visual Basic
Slide 58 of 75
Move Previous
Database Programming
To move to a previous record
rs1.MovePrevious
If rs1.BOF = True Then
rs1.MoveLast
End If
Text8.text = rs1.Fields("ID")
Text1.text = rs1.Fields("firstname")
Text2.text = rs1.Fields("lastname")
Text5.text = rs1.Fields("age")
Visual Basic
Slide 59 of 75
Group Exercise
Database Programming
Write a program to accept a students ID ,name,
age, intake and dob.
1)
2)
3)
4)
Visual Basic
Save the record to a tblstudent
Write a procedure to delete a studentID
Write a procedure to move to a next record
Write a procedure to move to a previous
record
Slide 60 of 75
Database Programming
VISUAL BASIC
DATA REPORTS
Bound and Unbound
Visual Basic
Slide 61 of 75
Learning Outcomes
Database Programming
At the end of this lecture you should be able to :
1. Write a Bound Data Report to display all
records for hard copy printing
2. Write an Unbound Data Report to display
selected records for hard copy printing
Visual Basic
Slide 62 of 75
Key Words
Database Programming
1.
2.
3.
4.
Visual Basic
Data Report
Bound
Unbound
Data Environment
Slide 63 of 75
Bound Data Report
Database Programming
•
•
•
Visual Basic
Is used to display records on screen for
hardcopy printing
Resembles like an MS-Word Document
Primarily used to display all records for
printing
Slide 64 of 75
Data Report
Database Programming
Records
Displayed Here
Visual Basic
Slide 65 of 75
Bound Data Report - Step 1
Database Programming
Select Project Click Components
Visual Basic
Slide 66 of 75
Bound Data Report – Step 2
Database Programming
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Visual Basic
Click Project and Click Data Environment
Right Click Connection1 - Select Properties
Select Microsoft Jet OLE DB Provider
Click Next
Select Database Path and Database Name
Click Test Connection
Right Click Connection1 – Add Command1
Right Click Command1 – Select Properties
Select Table from Database Object
Select Table Name
Slide 67 of 75
Bound Data Report – Step 3
Database Programming
1.
2.
3.
4.
5.
6.
7.
8.
9.
Visual Basic
Click on Command1 Tree View
You will be able to see the field name
Select Project – Add Data Report
Drag field to the Detail Section of the Data Report
Select Data Report Properties
Place Command1 in Data Member
Place Data Environment1 in Data Source
Run Data Report
In Form 1 - Private Sub Command1_Click ()
DataReport1.Show
End Sub
Slide 68 of 75
Bound Data Report - Rules
Database Programming
1. If database name is not selected and test
connection is clicked an error message is
displayed.
2. If the previous steps are not followed the
program will not run.
Visual Basic
Slide 69 of 75
Database Programming
Unbound Data Report
Visual Basic
Slide 70 of 75
Unbound Data Report
Database Programming
• Unbound data report is used to display
selected fields to a data report
• Query statements can be written to select
certain records from a table
• Does not depend on the Data Environment
• Users must write program code to display
records on a data report
Visual Basic
Slide 71 of 75
Unbound Data Report - Step 1
Database Programming
Visual Basic
Slide 72 of 75
Unbound Data Report - Step 2
Database Programming
1. Insert Text Box to Detail Section1 as a
field
2. Insert Labels to Page Header - Section2
3. Write Code to display records to the data
report
Visual Basic
Slide 73 of 75
Group Exercise
Database Programming
Write a program to accept a students ID ,name,
age,
intake and dob.
1)
2)
3)
4)
Visual Basic
Save the record to a tblstudent
Write a procedure to delete a studentID
Write a procedure to move to a next record
Write a procedure to move to a previous
record
Slide 74 of 75
Homework
Database Programming
• Write a program that will search a
particular students name from <studenttable> and display the Name, ID , Age on
an un bound data report.
• The table has the following fields :
Name
ID
Age
Visual Basic
Slide 75 of 75
Homework
Database Programming
• Write a program to display all records from
a table to a data report
• Write a program to search for a users IC
Number and display his/her name,
address, postcode, age , gender, tel-no to
a bound data report
Visual Basic
Slide 76 of 75