Databases
Download
1 / 30

DataBases - PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on

DataBases. CIS 241 - Advanced Visual Basic. DataBases. Collection of information Terminology File (.mdb) Table (Record) Field key. Students’ Table. Access Table Design. Messages to ADO Objects. ADO using OLE DB. VB Application. Database (.mdb file). Message to the Access DB.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' DataBases' - moesha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Databases

DataBases

CIS 241 - Advanced Visual Basic


Databases1
DataBases

  • Collection of information

  • Terminology

    • File (.mdb)

    • Table (Record)

    • Field

      • key




Visual basic and db

Messages to ADO Objects

ADO

using

OLE DB

VB Application

Database

(.mdb file)

Message to the Access DB

Visual Basic and DB


Activex data objects ado
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


Using a database in a project connection object
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


Setting up the connection
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


Information for recordsets
Information for Recordsets

  • Source

  • Active Connection

  • Cursor Location

  • Cursor Type

  • Lock Type

  • Options


Using the recordsets
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


Using the recordset and open in a form
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


Closing
Closing

  • Connection

    • Close connection

    • Set connection object to Nothing

  • Recordset

    • Close recordset

    • Set recordset object to Nothing


Unbound controls
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



Methods for form
Methods for Form

  • copyToForm

  • copyToRecordSet

  • clearForm

  • clearRecordSet


Example copy to form
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


Methods properties
Methods & Properties

  • Methods for Navigating in the Recordset

    • MoveNext

    • MovePrevious

    • MoveFirst

    • MoveLast

  • Properties

    • EOF

    • BOF

    • RecordCount


Searching for a record on a field value
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 & “’”


Searching for a record on a field value1
Searching for a Record on a Field Value

  • Filter – Multiple Criteria

    RecordSet.Filter = searchString

    rsStudent.Filter = cisNumber = ‘” & txtCName.Text & _

    “ ’ AND sectionNumber = “ & txtSection.Text


Adding deleting changing records
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


Protecting the add operation
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


Controls for input
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


Trapping database errors
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


Recordset with sql
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


Sql string
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


Create and open
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


Data grid
Data Grid

  • Add DataGrid Control

    • Project/components

    • Microsoft DataGrid Control 6.0

  • Open recordset

  • Use Recordset to set DataSource

    Set dataGrid.DataSource = newrs


Joins
Joins

  • Inner

  • Left

  • Right

    “SELECT lastName, firstName, “ & _

    “courses.courseName FROM students “ & _

    “INNER JOIN courses ON students.cisNumber = “ & _

    “ courses.courseNumber”


Connecting to the listview
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


Using selected item
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


ad