Transcript Chapter11

Structured Query Language

Also known as SQL


Industry standard for accessing relational databases
General format of SQL Select
Select [Distinct] fieldlist From tablenames
[Where search conditions]
[Group By fieldlist]
[Having group criterion]
[Order By fieldlist [ASC|DESC]]

Examples
Select * From Contacts
Select * From Contacts, ContactTypes Where
Contacts.ContactTypeID = ContactTypes.ContactTypeID
Order By LastName
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Creating a new Dynaset

Steps to define new dynaset via data control
1) Set data control’s RecordSource property to SQL query
2) Re-open recordset using Refresh method

Example
stSQL = “Select * From Contacts, ContactTypes Where “ _
& “Contacts.ContactTypeID = ContactTypes.ContactTypeID ”
datBooks.RecordSource = stSQL
datBooks.Refresh
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Database Objects

Data Access Objects (DAO)


Remote Data Objects (RDO)



Uses Jet DB engine to access databases
Used when developing client/server application
that access remote ODBC database system
Requires Enterprise edition of VB & 32-bit OS
ActiveX Data Objects (ADO)




New model with VB 6.0
Combines best features of DAO and ADO
Not limited to relational DBs
Requires OLE database driver to use with Jet DB
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
DAO Model
DBEngine
Errors
Workspaces
Groups
Users
Databases
Recordsets
QueryDefs
TableDefs
Relations
Containers
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Data Control vs. DAO Notation
Purpose of notation
Notation with data control
Move to first record Call
datEmployee.Recordset.MoveFirst
Notation with DAO
Call
fEmpRS.MoveFirst
Move to last record
Call
datEmployee.Recordset.MoveLast
Call
fEmpRS.MoveLast
Move to next
record
Call
datEmployee.Recordset.MoveNext
Call
fEmpRS.MoveNext
Move to previous
record
Call
datEmployee.Recordset.MovePrevious
Call
fEmpRS.MovePrevious
Condition to check datEmployee.Recordset.BOF
if before the start of
the Recordset
FEmpRS.BOF
datEmployee.Recordset.EOF
fEmpRS.EOF
Condition to check
if after the end of
the Recordset
Retrieve contents of datName.Recordset!FieldName
or
a specific field
datName.Recordset("FieldName")
or
datName.Recordset![FieldName]
fEmpRS!FieldName
or
fEmpRS("FieldName")
or
fEmpRS![FieldName]
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Options with SQL Select

Like operator


Relational operator used to find similar matches
Expression being compared is surrounded by
single quotes and normally contains * wildcard
 FirstName
Like ‘A*’
– returns all records whose FirstName values start with A

Aggregate Queries



Used to get summary-type information
Define new field to hold the result from an
aggregate function
Inner Joins
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Aggregate Queries
SELECT AggFunction(fieldname) As newdbfield
FROM tablename
[WHERE searchcriteria]
Function
AVG
COUNT
SUM
MAX
MIN
Purpose
Returns the average of the values in the selected field
Returns the number of records in the selected fields.
Returns the sum of all the values in the selected field.
Returns the highest value in the selected field.
Returns the smallest value in the selected field.
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Inner Joins for Multiple Tables
SELECT [DISTINCT] fieldlist
FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2
[WHERE searchcriteria]
[ORDER BY fieldname]

Example
Select * From Contacts INNER JOIN ContactTypes On
Contacts.ContactTypeID = ContactTypes.ContactTypeID Order
By LastName
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Modifying DB Records

Define action query




INSERT INTO
UPDATE
DELETE
Use Execute method

Call DBObject.Execute(stSQL)
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Add a new record
Private Sub WriteToDB()
Dim SQL As String
SQL = "INSERT INTO Employee "
SQL = SQL & "(EmpName, BirthDate, PayRate, Manager)"
SQL = SQL & " VALUES ("
SQL = SQL & DBTextFmt(txtEmpName.Text) & ", "
SQL = SQL & CStr(DBDateFmt(txtBirthdate)) & ", "
SQL = SQL & txtPayRate.Text & ", "
SQL = SQL & txtMgr.Text & ")"
Call fEmpDB.Execute(SQL)
' Enable navigation buttons
cmdFirst.Enabled = True
cmdLast.Enabled = True
cmdNext.Enabled = True
cmdPrevious.Enabled = True
End Sub
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Delete a record
Private Sub cmdDelete_Click()
Dim SQL As String
SQL = "DELETE FROM Employee Where EmpID = " & _
fEmpRS("EmpID")
Call fEmpDB.Execute(SQL)
Call fEmpRS.Close
Call fEmpDB.Close
Call ReOpenDB
End Sub
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Create New Database
Option Explicit
Private fUserWS As Workspace
Private fNewDB As Database
Private Sub CreateDB()
Dim FileName As String
Set fUserWS = DBEngine.Workspace(0)
FileName = AppendFileToPath(App.Path, “NewTestDB.mdb”)
If FileExists(FileName) Then
If MsgBox(“The database exists. Do you want to replace it?”, _
vbYesNo + vbQuestion) = vbYes Then
Kill FileName
Else
Exit Sub
' Do not create the DB-already exists
End If
End If
Set fNewDB = fUserWS.CreateDatabase(FileName, dbLangGeneral)
End Sub
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Create Database Table
‘
‘
Create table
SQL = "CREATE TABLE Employee (" & _
"EmpID
COUNTER, " & _
"EmpName
TEXT(30), " & _
"BirthDate DATETIME, " & _
"PayRate
CURRENCY, " & _
"Manager
BIT)"
Call fNewDB.Execute(SQL)
Create index to improve performance
SQL = "CREATE UNIQUE INDEX indEmpPrimary"
SQL = SQL & " ON Employee (EmpID)"
SQL = SQL & " WITH PRIMARY"
Call fDBPaychecks.Execute(SQL)
© 1999, by Que Education and Training, Chapter 11, pages 593-625 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach