Lab 7.5 – A Simple DataBase Interface
Goals:
w Solidify
understanding of records and random access files
w Create a file to
use as a data base
w Randomly store and
retrieve records from a file
w Display retrieved
information
w Format and send
information to the printer
w Learn basic
operations on strings
__1. Create a data file of
information about presidents of the U.S.
w Use what you have learned from Lab 7.4 to create the data file.
w Open a new VB program, prjMakeFile, placing one command button
on the form.
w Declare the type shown below:
Private
Type PrezInfo
LastName As String * 15
FirstName As String * 15
presnum As Integer
yearofbirth As Integer
yearofdeath As Integer
TermofOffice As String * 25
events As String * 2000
End
Type
Const
numofprezes = 43
w For the command button, write code to:
X Open the file presdata for
random access
X Give the presnum field
the same value as i, as below:
prezDat.presnum = i
X Do not give any other fields any values.
X Write all 43 records into the file presdata.
w Test the operation of this code.
X Add to the code for the command button this for loop:
For i = 1 To 10
Get #1, i, prezDat
Debug.Print prezDat.presnum
Debug.Print prezDat.yearofbirth
Debug.Print "xx" &
prezDat.LastName & "yy"
Next i
X What does this tell you about the contents of the presdata file?
w Exit the VB environment.
__2. Create a rough interface to the presdata file.
w Create a new VB program for this purpose.
X Enter the VB environment and begin a brand new
program.
X Rename the project prjSimpDB and the form frmSimpDB, saving
them in the
same folder (directory) as prjMakeFile.
w Supply data for one of the records in the presdata file.
X Place a command button on the form, giving it the
code below:
Dim prezDat As PrezInfo
Open App.Path & "\presdata" For Random As #1 Len =
Len(prezDat)
prezDat.LastName = "Jefferson"
prezDat.FirstName = "Thomas"
prezDat.yearofbirth = 1743
prezDat.yearofdeath = 1826
prezDat.TermofOffice = "1801-1809"
prezDat.events = "Acquired Louisiana Territory from Napoleon. Sent
a naval squadron to fight the Barbary pirates, who were harassing American
commerce in the Mediterranean."
Put #1, 3, prezDat
Close #1
w Run the program.
X Is there any way to tell whether the information was
written to
the file?
X What is the value of Len(prezDat)?
__3. Add retrieval and
display capabilities to this interface.
w Place a label, lblSho, onto
the form making it at least 5000 x 5000
twips and giving
it a font size of 12.
w Place a second command button on the form, giving it
this code:
Dim
prezDat As PrezInfo
Dim
show1 As String
Dim
show2 As String
Dim
show3 As String
Dim
show4 As String
Dim
show5 As String
Open App.Path & "\presdata" For
Random As #1 Len = Len(prezDat)
Get #1, 3, prezDat
show1 = Trim(prezDat.FirstName) & "
" & Trim(prezDat.LastName) & Chr(10)
show2 = "Born: " &
Str(prezDat.yearofbirth) & Chr(10) & "Died: " &
Str(prezDat.yearofdeath) & Chr(10)
show3 = "Term of Office: " &
Trim(prezDat.TermofOffice) & Chr(10)
show4 = "Significant Events:"
& Chr(10)
show5 = Trim(prezDat.events)
lblSho.Caption = show1 & show2 &
show3 & show4 & show5
Close #1
w Run the program.
What happens?
w What is the purpose of Chr(10)
in the code?
w What is the purpose of the calls to Trim?
w What is the purpose of the calls to Str?
w As it is above, there are 9 lines of code, not
counting the variable
declarations. What do you think
would be the minimum lines of
code that could be written to accomplish
this? Explain.
__4. Add information about two more presidents.
w Add two more command buttons for two presidents of your choice.
X Using the example above, write code to place
information about
these presidents
into the presdata
file.
If you do not have ready
access to
this information, check one of these websites:
http://www.whitehouse.gov/history/presidents/
http://www.pbs.org/wgbh/amex/presidents/frames/record/record.html
X Run the program to place this information into the
file.
__5. Add a simple interface for retrieving any record the user chooses.
w Place a textbox onto the form.
w Adapt the code from step #3 to retrieve and display any record.
X Declare a
variable, recnum.
X Set recnum equal to the
number the user types into the textbox.
X Rewrite the Get statement
to retrieve record number recnum,
instead of
record number 3.
w Now run the program.
X Try to retrieve and display each of the records
presently in the
presidential
data base.
__6. Finally, add capability to print out any given record.
w Define a function, per the code below:
Public
Function FindSpace(ByVal maxlen As String, ByVal mesg As String) As Integer
Dim
foundspot As Integer
foundspot = maxlen
While Mid(mesg, foundspot, 1) <> "
" And foundspot > 1
foundspot = foundspot - 1
Wend
If foundspot = 1 Then FindSpace = maxlen Else
FindSpace = foundspot
End Function
w Place another command button onto the form.
w Give it the code below.
Dim
prezDat As PrezInfo
Dim
recnum As Integer
Dim
eventstring As String
Dim
pstring As String
Dim
spaceloc As Integer
recnum = Val(txtWho.Text)
Open App.Path & "\presdata" For
Random As #1 Len = Len(prezDat)
Get #1, recnum, prezDat
Printer.Print
Printer.Print
Printer.Font = "Times New Roman"
Printer.FontSize = 18
Printer.FontBold = True
Printer.Print Spc(12);
Trim(prezDat.FirstName) & " " & Trim(prezDat.LastName)
Printer.Print
Printer.Font = "Times New Roman"
Printer.FontSize = 16
Printer.FontBold = True
Printer.Print "Born: ";
Printer.FontBold = False
Printer.Print Str(prezDat.yearofbirth)
Printer.FontBold = True
Printer.Print "Died: ";
Printer.FontBold = False
Printer.Print Str(prezDat.yearofdeath)
Printer.FontBold = True
Printer.Print "Term of Office: ";
Printer.FontBold = False
Printer.Print prezDat.TermofOffice
Printer.FontBold = True
Printer.Print "Significant Events:
"
Printer.Font = "Arial"
Printer.FontBold = False
Printer.FontSize = 12
eventstring = Trim(prezDat.events)
While Len(eventstring) > 0
spaceloc = FindSpace(80, eventstring)
pstring = Left(eventstring, spaceloc)
If pstring = eventstring Then
eventstring = ""
Else
eventstring = Right(eventstring,
Len(eventstring) - spaceloc)
End If
Printer.Print pstring
Wend
Printer.EndDoc
Close #1
w Test it out.
X Notice that, just as before, this feature is geared to
the value
entered into
the textbox.
__7. Some final observations.
w What is the purpose of Spc(12)
in the code?
w What is one way to skip lines as you print?
w What is the purpose of the semicolon in Printer.Print "Born:
"; ?
w What does the function FindSpace
do?
w What purpose does it serve in this code?
w What is the purpose of the calls to Left and Right?
w How are the variables eventstring and
pstring used?
w Why do we need the test: If pstring = eventstring?
w What is the purpose of Printer.EndDoc?