Database Programming

1
Database Programming
Visual Basic
2
Topic Structure of the lesson

• Introduction
• Database Concepts
• Active X ADODB
• Add Records
• Update
• Delete Records

3
Learning Outcomes

• At the end of this lecture, you should be able
  to
• Create a database table
• Use Active X Data Objects (ADODB)
• Write Structured Query Language (SQL)
• Add Records
• Update Records

4
Key Words used in this lecture

• If you have mastered this topic, you should be
  able to use
• the following terms correctly in your assignments
  and
• exams
• ADODB
• Add New
• Update
• Open
• Close
• EOF

5
Creating a Database Table

1. Start Microsoft Access
2. Create a table
3. Enter the field names
4. Ensure that you have a primary key field
5. Save the table as tblstudent

6
Active X Data Objects (ADODB)

• 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.

7
Steps to Connect To A Database

• Select Project References
• Select Microsoft ActiveX Data Objects 2.1 Library
• Write the connection string to connect to a
  database

8
Database Connection

• Dim cn as adodb.connection
• Private Sub Form_Load()
• Dim connstring As String
• Set cn New ADODB.Connection
• connstring ProviderMicrosoft.Jet.OLEDB.4.0
  
• Data Sourcec\newadodb\devin.mdb"
• cn.Open connstring
• End Sub

9
Group Exercise

• Write a procedure to connect to a database called
  library.mdb

10
Adding A New Record

• Execute the recordsets 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
  recordsets Update method.

11
Adding A New Record
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
12
Quick Review Question

• Write a procedure to accept Student-ID, student
  name, address, phone-number from text boxes.
• Write the record to a student-table.

13
Basic SQL Statements

• 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

14
Quick Review Question

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.

15
Updating a Record

• 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

16
Updating a Record

• 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

17
Quick Review Question

• Write a procedure to search for a Student ID and
  update the students address and phone number.
• If the studentID is not found display an error
  message using a Msgbox.
• Use tblstudent as the table name.

18
Deleting a Record

• 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.

19
Deleting a Record

• 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

20
Quick Review Question

• 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.

21
Question and Answer Session
Q A
22
Next Session
Displaying and Searching Records
23
Displaying and Searching Records
24
Learning Outcomes

• At the end of this lecture you should be able to
• Display records to a list box
• Select records from a lit box
• Search records using the like operator
• Display records to a combo box

25
Key Words

• If you have mastered this topic, you should be
  able to use the following
• terms correctly in your assignments and exams
• List Box
• Do Until
• Like

26
References

• Database Access with Visual Basic 6
• Jeffrey P Mc Manus
• SAMS Publication
• Visual Basic 6 How to Program
• Deitel and Deitel
• Chapter 18

27
Display Records to a List Box
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.Clea
r

28
Display Data to a List Box

• Do Until rs.EOF
• List1.AddItem rs.Fields("firstname")
• List1.ItemData(List1.NewIndex)
  rs.Fields("ID")
• rs.MoveNext
• Loop
• End Sub

29
Group Exercise

• Write a procedure to display all students name
  from tblstudent to a listbox.

30
Search Using List Box Click Event

• 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

31
Search Using Like Operator

• 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

32
Group Exercise

• Write a procedure to search for students name
  beginning with the letter A.
• Accept the students name using a text box
• Display the above records to a list box

33
Display records to a Combo Box

• 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

34
Combo Click

• 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

35
Form Unload

• Private sub Form_Unload(Cancel as Integer)
• cn.close
• set cn nothing
• End Sub

36

• Data Grids and Multiple Table Handling

37
Learning Outcomes

• At the end of this lecture you should be able to
  
• Display records to a Microsoft Hierarchical Flexi
  Grid (MSHFGrid)
• Display selected records to a Microsoft
  Hierarchical Flexi Grid
• Selecting a Record on a MSHFGrid
• Multiple Table Handling

38
Key Words

• MSHFGrid
• Text Matrix
• EOF

39
References

• 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

40
MS HF Grid

• 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

41
Procedure

• 1. MSHF Grid must be loaded from project /
  components
• 2. Select Project /Components and Click Microsoft
  Hierarchical Flexi Grid Control 6.0
• Click Grid on Tool Bar and draw the Grid on the
  Form
• Write Code to display records on the grid

42
Displaying Records on HF Grid

• 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

43
Displaying Records on HF Grid

• 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

44
Group Exercise

• 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.

45
Display Selected Fields on HF Grid

• 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

46
Display Selected Fields on HF Grid

• Do Until rstitles.EOF
• row row 1
• text row vbTab rstitles.Fields("firstna
  me") vbTab rstitles.Fields("lastname")
• grdtitles.AddItem text
• rstitles.MoveNext
• Loop
• rstitles.Close
• End Sub

47
Group Exercise

• 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.

48
Selecting a Record on a Grid

• Private Sub grid1_Click()
• a grid1.TextMatrix(grid1.row, grid1.Col)
• Msgbox a
• End Sub

49
Multiple Table Handling

• Extract data from two or more tables
• Used to display data to a report for printing

SID S-name Age Phone
Related
Book ID SID Book Title
50
Multiple Table Handling
Format Select table1.fieldname,table2.fieldname
from table1,table2 Example Select
student.name, student.id, library.title,library.bo
okid from student, library where student.sid
library.sid
51
Multiple Table Handling
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 S
et 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
52
Group Exercise
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
53
Group Exercise

• 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.

54
Search Records Between Two Dates
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(mskstar
t.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
55
Search Records Between Two Dates

• 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

56
Move Next SQL statement

• 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

57
Move Next

• 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

58
Group Exercise

• Write a procedure to move to a previous record
• You need to use the following key words
• BOF
• IF.ELSETHEN
• MovePrevious

59
Move Previous

• 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")

60
Group Exercise

• Write a program to accept a students ID ,name,
• age, intake and dob.
• 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

61

• VISUAL BASIC
• DATA REPORTS
• Bound and Unbound

62
Learning Outcomes

• At the end of this lecture you should be able to
  
• Write a Bound Data Report to display all records
  for hard copy printing
• Write an Unbound Data Report to display selected
  records for hard copy printing

63
Key Words

• Data Report
• Bound
• Unbound
• Data Environment

64
Bound Data Report

• Is used to display records on screen for hardcopy
  printing
• Resembles like an MS-Word Document
• Primarily used to display all records for printing

65
Data Report
Records Displayed Here
66
Bound Data Report - Step 1
Select Project Click Components
67
Bound Data Report Step 2

• 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

68
Bound Data Report Step 3

• 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

69
Bound Data Report - Rules

• If database name is not selected and test
  connection is clicked an error message is
  displayed.
• If the previous steps are not followed the
  program will not run.

70

• Unbound Data Report

71
Unbound Data Report

• 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

72
Unbound Data Report - Step 1
73
Unbound Data Report - Step 2

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

74
Group Exercise

• Write a program to accept a students ID ,name,
  age,
• intake and dob.
• 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

75
Homework

• Write a program that will search a particular
  students name from ltstudent-tablegt and display
  the Name, ID , Age on an un bound data report.
• The table has the following fields
• Name
• ID
• Age

76
Homework

• 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