Visual Basic Database Programming Topic Structure of the

Visual Basic Database Programming

Topic & Structure of the lesson Database Programming • Introduction • Database Concepts • Active X ADODB • Add Records • Update • Delete Records Visual Basic 2

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 3

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 4

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 5

Active X Data Objects (ADODB) Database Programming • • Visual Basic Newest Database Connectivity method in VB 6. 0. Supports Hierarchical Flexi Grid Control. Easier to program and debug. Allows Connection String method to connect to a database. 6

Steps to Connect To A Database Programming 1. Select Project References 2. Select Microsoft Active. X Data Objects 2. 1 Library 3. Write the connection string to connect to a database Visual Basic 7

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: newadodbdevin. mdb" cn. Open connstring End Sub Visual Basic 8

Group Exercise Database Programming • Write a procedure to connect to a database called library. mdb Visual Basic 9

Adding A New Record Database Programming • • • Visual Basic Execute the recordset’s Add. New 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. 10

Adding A New Record Database Programming Private Sub cmdadd_Click() dim rs as adodb. recordset rs = new adodb. recordset Dim strsql As String strsql = "select * from login where firstname = null" rs. Open strsql, cn, ad. Open. Keyset, ad. Lock. Optimistic rs. Add. New 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 11

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 12

Basic SQL Statements Database Programming SELECT * from Table. Name WHERE criteria (*) indicates all fields from a Table Select * From Student Where name = ‘John’ Select Student. ID, Name from tblstudent Select * from Student where name = ‘”& txtname. text & “’” Select * from tblstudent order by name – To Sort by Name Visual Basic 13

Quick Review Question Database Programming 1. Write a SQL statement to select all fields from tblpassenger. 2. Write a SQL statement to select passenger. ID, 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 14

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 15

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, ad. Open. Keyset, ad. Lock. Optimistic 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 16

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 student. ID is not found display an error message using a Msgbox. • Use tblstudent as the table name. Visual Basic 17

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

Deleting a Record Database Programming Private Sub Command 2_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, ad. Open. Keyset, ad. Lock. Optimistic If rs. EOF Then Msg. Box "Record Not Found" Else rs. Delete End If rs. Close End Sub Visual Basic 19

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 Msg. Box. Visual Basic 20

Question and Answer Session Database Programming Q&A Visual Basic 21

Next Session Database Programming Displaying and Searching Records Visual Basic 22

Database Programming Displaying and Searching Records Visual Basic 23

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 24

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 25

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 26

Display Records to a List Box Database Programming Private Sub Command 4_Click() Dim rs As ADODB. Recordset Set rs = New ADODB. Recordset Dim sql As String sql = "Select * from login" rs. Open sql, cn, ad. Open. Keyset, ad. Lock. Optimistic List 1. Clear Visual Basic 27

Display Data to a List Box Database Programming Do Until rs. EOF List 1. Add. Item rs. Fields("firstname") List 1. Item. Data(List 1. New. Index) = rs. Fields("ID") rs. Move. Next Loop End Sub Visual Basic 28

Group Exercise Database Programming • Write a procedure to display all students name from tblstudent to a listbox. Visual Basic 29

Search Using List Box Click Event Database Programming Private Sub list 1_click() Dim rs as adodb. recordset Dim strsql As String Set rs = new adodb. recordset sql = "select * from login where ID = " & _ List 1. Item. Data(List 1. List. Index) rs. Open sql, cn, ad. Open. Keyset, ad. Lock. Optimistic Text 3. text = rs. Fields("firstname") Text 4. text = rs. Fields("lastname") rs. Close End Sub Visual Basic 30

Search Using Like Operator Database Programming Private Sub Command 1_Click() Dim rs As ADODB. Recordset Dim sql as string Set rs = New ADODB. Recordset Sql = "Select * from login where firstname Like “ & _ “ ’ ” & Trim(Text 1. text) & “%’ ” rs. Open sql, cn , ad. Open. Static, ad. Lock. Optimistic, ad. Cmd. Text List 1. Clear Do Until rs. EOF List 1. Add. Item rs. Fields("firstname") & " " & rs. Fields("lastname") rs. Move. Next Loop rs. Close End Sub Visual Basic 31

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 32

Display records to a Combo Box Database Programming Private sub form_load() sql = "select * from login" rs. Open sql, cn, ad. Open. Keyset, ad. Lock. Optimistic Do Until rs. EOF Combo 1. Add. Item rs. Fields("firstname") rs. Move. Next Loop rs. Close End sub Visual Basic 33

Combo Click Database Programming Private Sub Combo 1_click() Dim sql As String sql = "Select * from login where firstname = '" & Combo 1. text & "'" rs. Open sql, cn, ad. Open. Keyset, ad. Lock. Optimistic Text 8. text = rs. Fields("ID") Text 1. text = rs. Fields("firstname") Text 2. text = rs. Fields("lastname") Text 5. text = rs. Fields("age") rs. Close Visual Basic 34

Form Unload Database Programming Private sub Form_Unload(Cancel as Integer) cn. close set cn = nothing End Sub Visual Basic 35

Database Programming Data Grids and Multiple Table Handling Visual Basic 36

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 37

Key Words Database Programming 1. MSHFGrid 2. Text Matrix 3. EOF Visual Basic 38

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 39

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 40

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 41

Displaying Records on HF Grid Database Programming grid 1. 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(Text 1. text) & “%’ ” rs. Open str, cn, ad. Open. Key. Set, ad. Lock. Optimistic If rs. BOF Then Msg. Box “Record Not Found” Exit Sub End If Visual Basic 42

Displaying Records on HF Grid Database Programming grid 1. Refresh Set grid 1. Data. Source = rs With grid 1. Text. Matrix(0, 1) = "ID". Text. Matrix(0, 2) = "Firstname". Text. Matrix(0, 3) = "Lastname". Text. Matrix(0, 4) = "DOB". Text. Matrix(0, 5) = "AGE" End With grid 1. Visible = True - Show the Grid rs. close Visual Basic 43

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 44

Display Selected Fields on HF Grid Database Programming Private Sub Command 2_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 ‘” & Text 1. text & “%’ ” rstitles. Open sql, cn, ad. Open. Keyset, ad. Lock. Optimistic grdtitles. Rows = 1 If rstitles. EOF Then rstitles. Close Exit Sub End If Visual Basic 45

Display Selected Fields on HF Grid Database Programming Do Until rstitles. EOF row = row + 1 text = row & vb. Tab & rstitles. Fields("firstname") & vb. Tab & rstitles. Fields("lastname") grdtitles. Add. Item text rstitles. Move. Next Loop rstitles. Close End Sub Visual Basic 46

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 47

Selecting a Record on a Grid Database Programming Private Sub grid 1_Click() a = grid 1. Text. Matrix(grid 1. row, grid 1. Col) Msgbox a End Sub Visual Basic 48

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 49

Multiple Table Handling Database Programming Format : Select table 1. fieldname, table 2. fieldname from table 1, table 2 Example : Select student. name, student. id, library. title, library. bookid from student, library where student. sid = library. sid Visual Basic 50

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, ad. Open. Keyset, ad. Lock. Optimistic grid 1. Refresh Set grid 1. Data. Source = rs With grid 1. Text. Matrix(0, 1) = "Firstname". Text. Matrix(0, 2) = "Lastname". Text. Matrix(0, 3) = "Book Title". Text. Matrix(0, 4) = "Author". Text. Matrix(0, 5) = "ISBN" End With rs. Close Visual Basic 51

Group Exercise Database Programming D-ID D-Name D-Floor Department -Table Emp-ID DPhone 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 52

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 Msg. Box to display the appropriate message. Visual Basic 53

Search Records Between Two Dates Database Programming Private Sub Command 8_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, ad. Open. Keyset, ad. Lock. Optimistic List 1. Clear If rs. EOF Then Msg. Box "RECORDS IN THAT DATE RANGE ARE NOT FOUND", vb. Exclamation Exit Sub End If Visual Basic 54

Search Records Between Two Dates Database Programming grid 1. Visible = True grid 1. Rows = 1 Do Until rs. EOF row = row + 1 text = row & vb. Tab & rs. Fields("Title") & vb. Tab & rs. Fields("author") & vb. Tab & rs. Fields("isbn") grid 1. Add. Item text rs. Move. Next Loop grid 1. Fixed. Rows = 1 ‘To Write data on the First Row End Sub Visual Basic 55

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 rs 1 as adodb. Recordset Set rs 1 = new adodb. Recordset sql = “select * from login” rs 1. Open sql, cn, ad. Open. Key. Set, ad. Lock. Optimistic End sub Visual Basic 56

Move Next Database Programming Private Sub Command 5_Click() rs 1. Move. Next If rs 1. EOF = True Then rs 1. Move. First End If Text 8. text = rs 1. Fields("ID") Text 1. text = rs 1. Fields("firstname") Text 2. text = rs 1. Fields("lastname") Text 5. text = rs 1. Fields("age") End Sub Visual Basic 57

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 Move. Previous Visual Basic 58

Move Previous Database Programming To move to a previous record rs 1. Move. Previous If rs 1. BOF = True Then rs 1. Move. Last End If Text 8. text = rs 1. Fields("ID") Text 1. text = rs 1. Fields("firstname") Text 2. text = rs 1. Fields("lastname") Text 5. text = rs 1. Fields("age") Visual Basic 59

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 student. ID Write a procedure to move to a next record Write a procedure to move to a previous record 60

Database Programming VISUAL BASIC DATA REPORTS Bound and Unbound Visual Basic 61

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 62

Key Words Database Programming 1. 2. 3. 4. Visual Basic Data Report Bound Unbound Data Environment 63

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 64

Data Report Database Programming Records Displayed Here Visual Basic 65

Bound Data Report - Step 1 Database Programming Select Project Click Components Visual Basic 66

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 Connection 1 - Select Properties Select Microsoft Jet OLE DB Provider Click Next Select Database Path and Database Name Click Test Connection Right Click Connection 1 – Add Command 1 Right Click Command 1 – Select Properties Select Table from Database Object Select Table Name 67

Bound Data Report – Step 3 Database Programming 1. 2. 3. 4. 5. 6. 7. 8. 9. Visual Basic Click on Command 1 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 Command 1 in Data Member Place Data Environment 1 in Data Source Run Data Report In Form 1 - Private Sub Command 1_Click () Data. Report 1. Show End Sub 68

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 69

Database Programming Unbound Data Report Visual Basic 70

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 71

Unbound Data Report - Step 1 Database Programming Visual Basic 72

Unbound Data Report - Step 2 Database Programming 1. Insert Text Box to Detail Section 1 as a field 2. Insert Labels to Page Header - Section 2 3. Write Code to display records to the data report Visual Basic 73

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 student. ID Write a procedure to move to a next record Write a procedure to move to a previous record 74

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 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 76