Advanced Visual Basic Databases: Database Management Techniques

1. DataBases CIS 241 - Advanced Visual Basic

2. DataBases • Collection of information • Terminology • File (.mdb) • Table (Record) • Field • key

3. Students’ Table

4. Access Table Design

5. Messages to ADO Objects ADO using OLE DB VB Application Database (.mdb file) Message to the Access DB Visual Basic and DB

6. ActiveX Data Objects (ADO) • Object Model • Project/References • Microsoft ActiveX Data Object 2.0 Library • ADODB Library • Objects • Connection object • Recordset object • Err object • Events • Methods

7. Using a Database in a Project : Connection Object • Connection object will be public to the project • Project/Add Module • Module • Declare connection as public syntax Public connectionID as ADODB.Connection Example: Public myCon as ADODB.Connection

8. Setting up the connection • Create a new connection • Set the connection string • Open the connection Public Sub OpenConnection() Set myCon = new ADODB.Connection myCon.ConnectionString =“Provider=Microsoft.Jet.OLEDB.4.0;” & _ “Data Source=“ & App.Path & “\filename.mdb” myCon.Open End Sub

9. Information for Recordsets • Source • Active Connection • Cursor Location • Cursor Type • Lock Type • Options

10. Using the Recordsets • Generic open routine in code module • Pass table name • Pass recordset Public sub openRecordset(tableName as String, recordsetName as ADODB.Recordset) ‘create the recordset object set recordsetName = new ADODB.RecordSet ‘set cursor location recordsetName.CursorLocation = adUseClient ‘open the recordset recordsetName.Open tableName, connectionID, adOpenDynamic, adLockOptimistic, adCmdTable End Sub

11. Using the Recordset and Open in a Form • Declare the recordset(s) as public to the form Dim rsStudent as ADODB.Recordset Dim rsCourse as ADODB.Recordset • Call the openRecordset subroutine openRecordset “students”, rsStudent openRecordset “courses”, rsCourse

12. Closing • Connection • Close connection • Set connection object to Nothing • Recordset • Close recordset • Set recordset object to Nothing

13. Unbound Controls • Referencing the Recordset • rsIdentifier.Property • rsIdentifier.Method • rsIdentifier![Field Name] • Unbound controls • most common • not automatically connected • assignment to and from recordset to controls in form

14. Data Controls

15. Methods for Form • copyToForm • copyToRecordSet • clearForm • clearRecordSet

16. Example – Copy to Form With rsStudent txtSocSecNum.Text = !socSecNum txtFirst.Text =!firstName txtLast.Text =!lastName txtCourse.Text =!cisNumber txtSection.Text = !sectionNumber txtGpa.Text = !gpa end with

17. Methods & Properties • Methods for Navigating in the Recordset • MoveNext • MovePrevious • MoveFirst • MoveLast • Properties • EOF • BOF • RecordCount

18. Searching for a Record on a Field Value • Find – Single Criteria (EOF true if not found) Recordset.Find searchString rsStudent.Find “cisNumber = 241” rsStudent.Find “firstName = ‘Smith’ “ rsStudent.Find “gpa < “ & txtNum.Text rmStudent.Find “name = ‘” & txtName.Text & “’”

19. Searching for a Record on a Field Value • Filter – Multiple Criteria RecordSet.Filter = searchString rsStudent.Filter = cisNumber = ‘” & txtCName.Text & _ “ ’ AND sectionNumber = “ & txtSection.Text

20. Adding/Deleting/Changing Records • To Add • Clears from • Add new record in recordset • Copy to recordset from form • Save • To Delete • Bring up record • Delete • To Change • Bring up record • Update

21. Protecting the Add Operation • Add • Empty • Allow user to enter values • Cancel • Empty out form • Reset focus to first input • Save • Do any error checking • Add new • Copy to record set • Save

22. Controls for Input • Text box • Locking • MaxLength • Mask Control • Microsoft Masked Edit Control(6.0) • Masked Property (reset must clear and reset mask) • Mask • Text • Values • Literals • AlphaNumeric • Numeric • Alpha

23. Trapping Database Errors • Always Place Error handling in procedures that access the database Public sub cmdNext_Click() on error goto errorHandler rs.MoveNext if rs.EOF then msgbox “No next item”, “Database Error” else copyToForm end if exit sub errorHandler: msgbox “Database operation failed”, “Database Error” on error goto 0 ‘turns off error for now End sub

24. Recordset with SQL • Previously a Whole Table • Part of a Table • Selected fields • Selected source • Selection condition • Recall Open parameters • Source, activeconnection, cursortype, locktype, options • Now • Must set cursorLocation to adUseClient • Options to adCmdText • Source to an sql string

25. SQL String • SELECT field1, field2, …. • FROM table1, table2, … • WHERE condition1, condition2… “SELECT firstName, lastName FROM students “ & _ “WHERE students.cisNumber = 112” “SELECT firstName, lastName FROM students“ & _ “WHERE students.cisNumber = “ & txtN.Text “SELECT firstName, lastName FROM students“ & _ “WHERE students.cisNumber = ” & txtN.Text & _ “ and students.sectionNum = “ & txtS.Text

26. Create and Open • Create as before Dim newrs as ADODB.RecordSet Set newrs = new ADODB.RecordSet • Open newrs.CursorLocation = adUseClient newrs.Open str, classdb, adOpenDynamic, _ adLockOptimistic, adCmdText

27. Data Grid • Add DataGrid Control • Project/components • Microsoft DataGrid Control 6.0 • Open recordset • Use Recordset to set DataSource Set dataGrid.DataSource = newrs

28. Joins • Inner • Left • Right “SELECT lastName, firstName, “ & _ “courses.courseName FROM students “ & _ “INNER JOIN courses ON students.cisNumber = “ & _ “ courses.courseNumber”

29. Connecting to the ListView ‘Recall Old Code Dim item as listItem Set item = lvwS.ListItems.Add(, , “123-45-6789”, “eye”, “eye”) With item.ListSubItems .Add 1, “last” , “Smith” .Add 2, “first” , “John” .Add 3, “gpa” , “3.17” End with

30. Using Selected Item • Dim item as listItem • Set item = lvwS.selectedItem • Enter a new gpa • Change the gpa in the listview item.listsubItems.item(“gpa”).text • Update the recordset item • Move first and find the corresponding record • Assign the new gpa to the gpa field • Update