WORKING WITH FILES
15.1 Introduction
Up until lesson 13 we are only creating programs that could accept data at runtime, when a program is terminated, the data also disappear. Is it possible to save data accepted by a VB program into a storage device, such as a hard disk or diskette, or even CDRW? The answer is possible. Is this chapter, we will learn how to create files by writing them into a storage device and then retrieve the data by reading the contents of the files using customized VB programs.
15.2 Creating files
To create a file, use the following command
Open “filename” for Output As #fileNumber
Each file created must have a file name and a file number for identification. As for file name, you must also specify the path where the file will reside.
For Example
Open “C:\My Documents\sample.txt” For Output As #1
will create a text file by the name of sample.txt in the My Document folder. The accompany file number is 1. If you wish to create and save the file in D drive, simply change the path, as follows:
Open “D\sample.txt” For Output As #1
If you wish to create a HTML file, simple change the extension to .html
Open “C:\My Documents\sample.html” For Output As #2
15.2.1 Sample Program – Create a text file
Figure 15.1
Private Sub Create_Click()
Dim intMsg As String
Dim studentName As String
Open “D:\sample.txt” For Output As #1
intMsg = MsgBox(“File Sample.txt Opened”)
studentName = InputBox(“Enter the Student Name”)
Print #1, studentName
intMsg = MsgBox(“Writing a “& studentName &” to sample.txt”)
Close #1
intMsg = MsgBox(“File Sample.txt Closed”)
End Sub
Private Sub Exit_Click()
End
End Sub
Figure 15.2 Output of Create a text file
Figure 15.3
Figure 15.4
Figure 15.5
* The above program will create a file samole.txt in the D drive and ready to receive input from users. Any data input by users will be saved in this text file
15.3.1 Sample Program – Reading File
Figure 15.6
Private Sub Reading_Click()
Dim variable1 As String
Open “D:\sample.txt” For Input As #1
Input #1, Variable1
Text1.Text = variable1
Close #1
End Sub
Figure 15.7 – Output
* This program will open the sample.txt file and display its contents in the Text1 textbox.
CREATING MULTIMEDIA APPLICATIONS – PART I
You can create various multimedia applications in VB that could play audio CD, audio files, VCD, video files and etc.
To be able to play multimedia files or multimedia devices, you have to insert Microsoft Multimedia Control into your VB applications that you are going to create. However, Microsoft Multimedia Control is not normally included in the startup toolbox, therefore you need to add the Microsoft Multimedia Control by pressing Ctrl+T and select it from the component dialog box that is displayed.
16.1 Creating a CD player
(a) The Interface
Figure 16.1
Private Sub Form_Load()
‘To position the page at the center
Left = (Screen.Width – Width)\2
Top = (Screen.Height – Height)\2
‘Open the CD
myCD.Command = “Open”
End Sub
Private Sub myCD_StatusUpdate()
‘Update the track number
trackNum.Caption = myCD.Track
End Sub
Private Sub Next_Click()
myCD.Command = “Next”
End Sub
Private Sub Next_Click()
myCD.Command = “Play”
End Sub
Private Sub Next_Click()
myCD.Command = “Prev”
End Sub
Private Sub Next_Click()
myCD.Command = “Stop”
End Sub
Private Sub Next_Click()
End
End Sub
CREATING MULTIMEDIA APPLICATIONS – PART II
In previous lesson, we have programmed a CD player. Now, with some modifications, we will transform the CD player into an audio file player. This player will be created in such a way that it could search for wave and midi files in your drives and play them.
In this project, you need to insert a ComboBox, a DriveListBox, a TextBox and a FileListBox in to your Form. I shall briefly discuss the function of each of the above controls. Besides, you must also insert Microsoft Multimedia Control (MMControl) in your form, you may make it visible or invisible. In my program, I choose to make it invisible so that I could use the command buttons created to control the player.
¨ ComboBox – to display and enable selection of different type of files.
¨ DriveListBox – to allow selection of different drives available on your PC
¨ DirListBox – to display Directories
¨ TextBox – to display selected files
¨ FileListBox – to display files that are available
Relevant codes must be written to coordinate all the above controls so that the application can work properly. The program should flow in the following logical way:
Step: 1
User choose the type of files he wants to play
Step: 2
User selects the drive that might contains the relevant audio files
Step: 3
User looks into directories and subdirectories for the files specified in Step1. The files should be displayed in the FilListBox.
Step: 4
User selects the files from the FileListBox and clicks the Play button.
Step: 5
User click on the Stop to stop playing and Exit button to end the application.
(i) The Interface
Figure 17.1
(ii) The Codes
Private Sub Combo1_Change()
‘To determine file type
If ListIndex = 0 Then
File1.Pattern = (“*wav”)
ElseIf ListIndex = 1 Then
File1.Pattern = (“*mid”)
Else
File1.Pattern = (“*.*”)
End If
End Sub
Private Sub Dir1_Change()
‘To change directories and subdirectories (or folder and sub folders)
File1.Path = Dir1.Path
If Combo1.ListIndex = 0 Then
File1.Pattern = (“*.wav”)
ElseIf Combo1.ListIndex = 1 Then
File1.Pattern = (“*.mid”)
Else
File1.Pattern = (“*.*”)
End If
End Sub
Private Sub Drive1_Change()
‘To change drives
Dir1.Path = Drive1.Drive
End Sub
Private Sub File1_Click()
If Combo1.ListIndex = 0 Then
File1.Pattern = (“*.wav”)
ElseIf Combo1.ListIndex = 1 Then
File1.Pattern = (“*.mid”)
Else
File1.Pattern = (“*.*”)
End If
If Right(File1.Path, 1) <> “\” Then
filenam = File1.Path + “\” + File1.FileName
Else
filenam = File1.Path + File1.FileName
End If
Text1.Text = filenam
End Sub
Private Sub Form_Load()
‘To center the Audioplayer startup page
Left = (Screen.Width – Width) \ 2
Top = (Screen.Height – Height) \ 2
Combo1.Text = “*.wav”
Combe1.AddItem “*.wav”
Combe1.AddItem “*.mid”
Combe1.AddItem “All files”
End Sub
Private Sub AudioPlayer_Click()
End Sub
Private Sub Play_Click()
‘To play WaveAudio file or Midi File
If Combo1.ListIndex = 0 Then
AudioPlayer.DeviceType = “WaveAudio”
ElseIf Combo1.ListIndex = 1 Then
AudioPlayer.DeviceType = “Sequencer”
End If
AudioPlayer.FileName = Text1.Text
AudioPlayer.Command = “Open”
AudioPlayer.Command = “Play”
End Sub
Private Sub Stop_Click()
If AudioPlayer.Mode = 524 Then Exit Sub
If AudioPlayer.Mode <> 525 Then
AudioPlayer.Wait = True
AudioPlayer.Command = “Stop”
End If
AudioPlayer.Command = “True”
AudioPlayer.Command = “Close”
End Sub
Private Sub Exit_Click()
End
End Sub
CREATING MULTIMEDIA APPLICATIONS – PART III
In lesson 17, we have created an audio player. Now, with modifications, we will transform the audio player into a picture viewer. This Player will be created in such a way that it could search for all types of graphics your drives and displays them.
In this project, you need to insert a DirveListBox, a DirListBox, a FileListBox and an Image Box into your form.
Example 18.1
(i) The Interface
Figure 18.1
(ii) The Code
Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub
Private Sub File1_Click()
Image1.Picture = LoadPicture(File1.Path & "\" & File1.FileName)
End Sub
Private Sub Form_Load()
Image1.Stretch = True
End Sub
(iii) Output
Figure 18.2
CREATING MULTIMEDIA APPLICATIONS – PART IV
IV
A MULTIMEDIA PLAYER
In lesson 18, we have created an Image viewer. Now, with some modifications, we will transform the Image viewer into a multimedia player that could play all kinds of movie files besides audio files. This player will be created in such a way that it could search for all types of graphics your drives and play them.
In this project, you need to insert a DirveListBox, a DirListBox, a FileListBox and Windows Media Player into your form.
Select the Windows Media Player Tool:
Project à Components (Ctrl+T) à Windows Media Player
Example
(i) The Interface
Figure 19.1
(ii) The Codes
Private Sub Play_Click()
MediaPlayer1.Play
End Sub
Private Sub Pause_Click()
MediaPlayer1.Pause
End Sub
Private Sub Stop_Click()
MediaPlayer1.Stop
End Sub
Private Sub Exit_Click()
End
End Sub
Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub
Private Sub File1_Click()
MediaPlayer1.FileName = File1.Path & "\" & File1.FileName
End Sub
(iii) Output
Figure 19.2
GRAPHICS TECHNIQUES WITH VISUAL BASIC
20.1 Graphics Methods
- Graphics methods apply to forms and picture boxes (remember a picture box is like a form within a form). With these methods, we can draw lines, boxes, and circles. Before discussing the commands that actually perform the graphics drawing, though, we need to look at two other topics: screen management and screen coordinates.
- In single program environments (DOS, for example), when something is drawn on the screen, it stays there. Windows is a multi- tasking environment. If you switch from a Visual Basic application to some other application, your Visual Basic form may become partially obscured. When you return to your Visual Basic application, you would like the form to appear like it did before being covered. All controls are automatically restored to the screen. Graphics methods drawings may or may not be restored - we need them to be, though. To accomplish this, we must use proper screen management.
- The simplest way to maintain graphics is to set the form or picture box's AutoRedraw property to True. In this case, Visual Basic always maintains a copy of graphics output in memory (creates persistent graphics ). Another way to maintain drawn graphics is (with AutoRedraw set to False) to put all graphics commands in the form or picture box's Paint event. This event is called whenever an obscured object becomes unobscured. There are advanta ges and disadvantages to both approaches (beyond the scope of discussion here). For now, we will assume our forms won't get obscured and, hence, beg off the question of persistent graphics and using the AutoRedraw property and/or Paint event.
- All graphic s methods described here will use the default coordinate system:
Note the x (horizontal) coordinate runs from left to right, starting at 0 and extending to ScaleWidth - 1. The y (vertical) coordinate goes from top to bottom, starting at 0 and ending at ScaleHeight - 1. Points in this coordinate system will always be referred to by a Cartesian pair, (x, y). Later, we will see how we can use any coordinate system we want.
ScaleWidth and ScaleHeight are object properties representing the “graphics” dimensions of an object. Due to border space, they are not the same as the Width and Height properties. For all measurements in twips (default coordinates), ScaleWidth is less than Width and ScaleHeight is less than Height. That is, we can’t draw to all points on the form.
- PSet Method:
To set a single point in a graphic object (form or picture box) to a particular color, use the PSet method. We usually do this to designate a starting point for other graphics methods. The syntax is:
ObjectName.PSet (x, y), Color
where ObjectName is the object name, (x, y) is the selected point, and Color is the point color (discussed in the next section). If the ObjectName is omitted, the current form is assumed to be the object. If Colo r is omitted, the object's ForeColor property establishes the color. PSet is usually used to initialize some further drawing process.
- Pset Method Example:
This form has a ScaleWidth of 3975 (Width 4095) and a ScaleHeight of 2400 Height 2805). The command:
PSet (1000, 500)
will have the result:
The marked point (in color ForeColor, black in this case) is pointed to by the Cartesian coordinate (1000, 500) - this marking, of course, does not appear on the form. If you want to try this example, and the other graphic methods, put the code in the Form_Click event. Run the project and click on the form to see the results (necessary because of the AutoRedraw problem).
- CurrentX and CurrentY:
After each drawing operation, the coordinate of the last point drawn to is maintained in two Visual Basic system variables, CurrentX and CurrentY. This way we always know where the next drawing operation will begin. We can also change the values of these variables to move this last point. For example, the code:
CurrentX = 1000
CurrentY = 500
is equivalent to:
PSet(1000, 500)
- Line Method:
The Line method is very versatile. We can use it to draw line segments, boxes, and filled boxes. To draw a line, the syntax is:
ObjectName.Line (x1, y1) - (x2, y2), Color
where ObjectName is the object name, (x1, y1) the starting coordinate, (x2, y2) the ending coordinate, and Color the line color. Like PSet, if ObjectName is mitted, drawing is done to the current form and, if Color is omitted, the object’s ForeColor property is used.
To draw a line from (CurrentX, CurrentY) to (x2, y2), use:
ObjectName.Line - (x2, y2), Color
There is no need to specify the start point since CurrentX and CurrentY are known.
To draw a box bounded by opposite corners (x1, y1) and (x2, y2), use:
ObjectName.Line (x1, y1) - (x2, y2), Color, B
and to fill that box (using the current FillPattern), use:
ObjectName.Line (x1, y1) - (x2, y2), Color, BF
- Line Method Examples:
Using the previous example form, the commands:
Line (1000, 500) - (3000, 2000)
Line - (3000, 1000)
draws these line segments:
The command:
Line (1000, 500) - (3000, 2000), , B
draws this box (note two commas after the second coordinate - no color is specified):
- Circle Method:
The Circle method can be used to draw circles, ellipses, arcs, and pie slices. We'll only look at drawing circles - look at on- line help for other drawing modes.
The syntax is:
ObjectName.Circle (x, y), r, Color
This command will draw a circle with center (x, y) and radius r, using Color.
- Circle Example:
With the same example form, the command:
Circle (2000, 1000), 800
Produces the result:
- Print Method:
Another method used to 'draw' to a form or picture box is the Print method. Yes, for these objects, printed text is drawn to the form.
The syntax is:
ObjectName.Print [information to print]
Here the printed information can be variables, text, or some combination. If no object name is provided, printing is to the current form.
Information will print beginning at the object's CurrentX and CurrentY value. The color used is specified by the object's ForeColor property and the font is specified by the object's Font characteristics.
- Print Method Example:
The code (can’t be in the Form_Load procedure because of that pesky AutoRedraw property):
CurrentX=200
CurrentY=200
Print "Here is the line of text" will produce this result (I’ve used a large font):
- Cls Method:
To clear the graphics drawn to an object, use the Cls method.
The syntax is:
ObjectName.Cls
If no object name is given, the current form is cleared. Recall Cls only clears the lowest of the three display layers. This is where graphics methods draw.
- For each graphic method, line widths, fill patterns, and other graphics features can be controlled via other object properties. Consult on- line help for further information.
20.2 Using Colors
- Notice that all the graphics methods can use a Color argument. If that argument is omitted, the ForeColor property is used. Color is actually a hexadecimal (long integer) representation of color - look in the Properties Window at some of the values of color for various object properties. So, one way to get color values is to cut and paste values from the Properties Window. There are other ways, though.
- Symbolic Constants:
Visual Basic offers eight symbolic constants (see Appendix I) to represent some basic colors. Any of these constants can be used as a Color argument.
Constant Value Color
vbBlack 0x0 Black
vbRed 0xFF Red
vbGreen 0xFF00 Green
vbYellow 0xFFFF Yellow
vbBlue 0xFF0000 Blue
vbMagenta 0xFF00FF Magenta
vbCyan 0xFFFF00 Cyan
vbWhite 0xFFFFFF White
- QBColor Function:
For Microsoft QBasic, GW -Basic and QuickBasic programmers, Visual Basic replicates the sixteen most used colors with the QBColor function. The color is specified by QBColor(Index), where the colors corresponding to the Index are:
Index Color Index Color
0 Black 8 Gray
1 Blue 9 Light blue
2 Green 10 Light green
3 Cyan 11 Light cyan
4 Red 12 Light red
5 Magenta 13 Light magenta
6 Brown 14 Yellow
7 White 15 Light (bright) white
- RGB Function:
The RGB function can be used to produce one of 224 (over 16 million) colors! The
syntax for using RGB to specify the color property is:
RGB(Red, Green, Blue)
where Red, Green, and Blue are integer measures of intensity of the corresponding primary colors. These measures can range from 0 (least intensity) to 255 (greatest intensity). For example, RGB(255, 255, 0) will produce yellow.
- Any of these four representations of color can be used anytime your Visual Basic code requires a color value.
- Color Examples:
frmExample.BackColor = vbGreen
picExample.FillColor = QBColor(3)
lblExample.ForeColor = RGB(100, 100, 100)
20.3 Mouse Events
- Related to graphics methods are mouse events. The mouse is a primary interface to performing graphics in Visual Basic. We've already used the mouse to Click and DblClick on objects. Here, we see how to recognize other mouse events to allow drawing in forms and picture boxes.
MouseDown Event:
The MouseDown event procedure is triggered whenever a mouse button is pressed while the mouse cursor is over an object. The form of this procedure is:
Sub ObjectName_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
.
.
End Sub
The arguments are:
Button Specifies which mouse button was pressed.
Shift Specifies state of Shift, Ctrl, and Alt keys.
X, Y Coordinate of mouse cursor when button was pressed.
Values for the Button argument are:
Symbolic Constant Value Description
vbLeftButton 1 Left button is pressed.
vbRightButton 2 Right button is pressed.
vbMiddleButton 4 Middle button is pressed.
Only one button press can be detected by the MouseDown event. Values for the Shift argument are:
Symbolic Constant Value Description
vbShiftMask 1 Shift key is pressed.
vbCtrlMask 2 Ctrl key is pressed.
vbAltMask 4 Alt key is pressed.
The Shift argument can represent multiple key presses. For example, if Shift = 5 (vbShiftMask + vbAltMask), both the Shift and Alt keys are being pressed when the MouseDown event occurs.
- MouseUp Event:
The MouseUp event is the opposite of the MouseDown event. It is triggered whenever a previously pressed mouse button is released. The procedure outline is:
Sub ObjectName_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
.
.
End Sub
The arguments are:
Button Specifies which mouse button was released.
Shift Specifies state of Shift, Ctrl, and Alt keys.
X, Y Coordinate of mouse cursor when button was released.
The Button and Shift constants are the same as those for the MouseDown event.
- MouseMove Event:
The MouseMove event is continuously triggered whenever the mouse is being
moved. The procedure outline is:
Sub ObjectName_MouseMove(Button As Integer, Shift As Integer, X As Single,Y As Single)
.
.
End Sub
The arguments are:
Button Specifies which mouse button(s), if any, are pressed.
Shift Specifies state of Shift, Ctrl, and Alt keys
X, Y Current coordinate of mouse cursor
The Button and Shift constants are the same as those for the MouseDown event. A difference here is that the Button argument can also represent multiple button presses or no press at all. For example, if Button = 0, no button is pressed as the mouse is moved. If Button = 3 (vbLeftButton + vbRightButton), both the left and right buttons are pressed while the mouse is being moved.
Example
Blackboard
- Start a new application. Here, we will build a blackboard we can scribble on with the mouse (using colored ‘chalk’).
2. Set up a simple menu structure for your application using the Menu Editor. The menu should be:
File
New
Exit
Properties for these menu items should be:
Caption Name
&File mnuFile
&New mnuFileNew
- mnuFileSep
E&xit mnuFileExit
3. Put a picture box and a single label box (will be used to set color) on the form. Set the following properties:
Form1 :
BorderStyle 1-Fixed Single
Caption Blackboard
Name frmDraw
Picture1:
Name picDraw
Label1 :
BorderStyle 1-Fixed Single
Caption [Blank]
Name lblColor
The form should look something like this:
4. Now, copy and paste the label box (create a control array named lblColor) until there are eight boxes on the form, lined up vertically under the original box. When done, the form will look just as above, except there will be eight label boxes.
5. Type these lines in the general declarations area. DrawOn will be used to indicate whether you are drawing or not.
Option Explicit
Dim DrawOn As Boolean
6. Attach code to each procedure.
The Form_Load procedure loads colors into each of the la bel boxes to allow choice of drawing color. It also sets the BackColor to black and the ForeColor to Bright White.
Private Sub Form_Load()
'Load drawing colors into control array
Dim I As Integer
For I = 0 To 7
lblColor(I).BackColor = QBColor(I + 8)
Next I
picDraw.ForeColor = QBColor(15) ‘ Bright White
picDraw.BackColor = QBColor(0) ‘ Black
End Sub
In the mnuFileNew_Click procedure, we check to see if the user really wants to start over. If so, the picture box is cleared with the Cls method.
Private Sub mnuFileNew_Click()
'Make sure user wants to start over
Dim Response As Integer
Response = MsgBox("Are you sure you want to start a new
drawing?", vbYesNo + vbQuestion, "New Drawing")
If Response = vbYes Then picDraw.Cls
End Sub
In the mnuFileExit_Click procedure, make sure the user really wants to stop the application.
Private Sub mnuFileExit_Click()
'Make sure user wants to quit
Dim Response As Integer
Response = MsgBox("Are you sure you want to exit the
Blackboard?", vbYesNo + vbCritical + vbDefaultButton2,
"Exit Blackboard")
If Response = vbYes Then End
End Sub
When the left mouse button is clicked, drawing is initialized at the mouse cursor location in the picDraw_MouseDown procedure.
Private Sub picDraw_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
'Drawing begins
If Button = vbLeftButton Then
DrawOn = True
picDraw.CurrentX = X
picDraw.CurrentY = Y
End If
End Sub
When drawing ends, the DrawOn switch is toggled in picDraw_MouseUp.
Private Sub picDraw_MouseUp(Button As Integer, Shift As
Integer, X As Single, Y As Single)
'Drawing ends
If Button = vbLeftButton Then DrawOn = False
End Sub
While mouse is being moved and DrawOn is True, draw lines in current color in the
picDraw_MouseMove procedure.
Private Sub picDraw_MouseMove(Button As Integer, Shift As
Integer, X As Single, Y As Single)
'Drawing continues
If DrawOn Then picDraw.Line -(X, Y), picDraw.ForeColor
End Sub
Finally, when a label box is clicked, the drawing color is changed in the lblColor_Click procedure.
Private Sub lblColor_Click(Index As Integer)
'Make audible tone and reset drawing color
Beep
picDraw.ForeColor = lblColor(Index).BackColor
End Sub
7. Run the application. Click on the label boxes to change the color you draw with. Fun, huh? Save the application.
8. A challenge for those who like challenges. Add Open and Save options that allow you to load and save pictures you draw. Suggested steps (may take a while – I suggest trying it outside of class):
A. Change the picture box property AutoRedraw to True. This is necessary to save pictures. You will notice the drawing process slows down to accommodate persistent graphics.
B. Add the Open option. Write code that brings up a common dialog box to get a filename to open (will be a .bmp file) and put that picture in the picDraw.Picture property using the LoadPicture function.
C. Add the Save option. Again, add code to use a common dialog box to get a proper filename. Use the SavePicture method to save the Image property of the picDraw object. We save the Image property, not the Picture property, since this is where Visual Basic maintains the persistent graphics.
D. One last change. The Cls method in the mnuFileNew_Click code will not clear a picture loaded in via the Open code (has to do with using AutoRedraw). So, replace the Cls statement with code that manually erases the picture box. I'd suggest using the BF option of the Line method to simply fill the space with a box set equal to the BackColor (white). I didn't say this would be easy.
20.4 Drag and Drop Events
- Related to mouse events are drag and drop events. This is the process of using the mouse to pick up some object on a form and move it to another location. We use drag and drop all the time in Visual Basic design mode to locate objects on our application form.
- Drag and drop allows you to design a simple user interface where tasks can be performed without commands, menus, or buttons. Drag and drop is very intuitive and, at times, faster than other methods. Examples include dragging a file to another folder or dragging a document to a printer queue.
- Any Visual Basic object can be dragged and dropped, but we usually use picture and image boxes. The item being dragged is called the source object. The item being dropped on (if there is any) is called the target.
- Object Drag Properties:
If an object is to be dragged, two properties must be set:
DragMode Enables dragging of an object (turns off ability to
receive Click or MouseDown events). Usually use 1-
Automatic (vbAutomatic).
DragIcon Specifies icon to display as object is being dragged.
As an object is being dragged, the object itself does not move, only the DragIcon. To move the object, some additional code using the Move method (discussed in a bit) must be used.
- DragDrop Event:
The DragDrop event is triggered whenever the source object is dropped on the target object. The procedure form is:
Sub ObjectName_DragDrop(Source As Control, X As Single, Y As Single)
.
.
End Sub
The arguments are:
Source Object being dragged.
X, Y Current mouse cursor coordinates.
- DragOver Event:
The DragOver event is triggered when the source object is dragged over another object. Its procedure form is:
Private Sub ObjectName_DragOver(Source As Control, X As Single, Y
As Single, State As Integer)
.
.
End Sub
The first three arguments are the same as those for the DragDrop event. The State argument tells the object where the source is. Its values are 0-Entering (vbEnter), 1- Leaving (vbLeave), 2-Over (vbOver).
- Drag and Drop Methods:
Drag Starts or stops manual dragging (won't be addressed here - we use Automatic dragging)
Move Used to move the source object, if desired.
Example
To move the source object to the location specified by coordinates X and Y, use:
Source.Move X, Y
The best way to illustrate the use of drag and drop is by example.
Example
Letter Disposal
- We'll build a simple application of drag and drop where unneeded correspondence is dragged and dropped into a trash can. Start a new application. Place four image boxes and a single command button on the form. Set these properties:
Form1 :
BackColor White
BorderStyle 1-Fixed Single
Caption Letter Disposal
Name frmDispose
Command1:
Caption &Reset
Name cmdReset
Image1:
Name imgCan
Picture trash01.ico
Stretch True
Image2:
Name imgTrash
Picture trash01.ico
Visible False
Image3:
Name imgBurn
Picture trash02b.ico
Visible False
Image4:
DragIcon drag1pg.ico
DragMode 1-Automatic
Name imgLetter
Picture mail06.ico
Stretch True
The form will look like this:
Some explanation about the images on this form is needed. The letter image is the control to be dragged and the trash can (at Image1 location) is where it will be dragged to. The additional images (the other trash can and burning can) are not visible at run-time and are used to change the state of the trash can, when needed. We could load these images from disk files at run-time, but it is much quicker to place them on the form and hide them, then use them when required.
- The code here is minimal. The Form_DragDrop event simply moves the letter image if it is dropped on the form.
Private Sub Form_DragDrop(Source As Control, X As Single, Y
As Single)
Source.Move X, Y
End Sub
- The imgCan_DragDrop event changes the trash can to a burning pyre if the letter is dropped on it.
Private Sub imgCan_DragDrop(Index As Integer, Source As
Control, X As Single, Y As Single)
'Burn mail and make it disappear
imgCan.Picture = imgBurn.Picture
Source.Visible = False
End Sub
- The cmdReset_Click event retur ns things to their original state.
Private Sub cmdReset_Click()
'Reset to trash can picture
imgCan.Picture = imgTrash.Picture
imgLetter.Visible = True
End Sub
- Save and run the application. Notice how only the drag icon moves. Notice the letter moves once it is dropped. Note, too, that the letter can be dropped anywhere. The fire appears only when it is dropped in the trash.
20.5 Timer Tool and Delays
Many times, especially in using graphics, we want to repeat certain operations at regular intervals. The timer tool allows such repetition. The timer tool does not appear on the form while the application is running.
Timer tools work in the background, only being invoked at time intervals you specify. This is multi- tasking - more than one thing is happening at a time.
- Timer Properties:
Enabled Used to turn the timer on and off. When on, it
continues to operate until the Enabled property is set to False.
Interval Number of milliseconds between each invocation of the Timer Event.
- Timer Events:
The timer tool only has one event, Timer. It has the form:
Sub TimerName_Timer()
.
.
End Sub
This is where you put code you want repeated every Interval seconds.
- Timer Example:
To make the computer beep every second, no matter wha t else is going on, you add a timer tool (named timExample) to the form and set the Interval property to 1000. That timer tool's event procedure is then:
Sub timExample_Timer()
Beep
End Sub
In complicated applications, many timer tools are often used to control numerous simultaneous operations. With experience, you will learn the benefits and advantages of using timer tools.
- Simple Delays:
If you just want to use a simple delay in your Visual Basic application, you might want to consider the Timer function. This is not related to the Timer tool. TheTimer function simply returns the number of seconds elapsed since midnight.
To use the Timer function for a delay of Delay seconds (the Timer function seems to be accurate to about 0.1 seconds, at best), use this code segment:
Dim TimeNow As Single
.
.
TimeNow = Timer
Do While Timer - TimeNow < Delay
Loop
One drawback to this kind of coding is that the application cannot be interrupted
while in the Do loop. So, keep delays to small values.
20.6 Animation Techniques
- One of the more fun things to do with Visual Basic programs is to create animated graphics. We'll look at a few simple animation techniques here. I'm sure you'll come up with other ideas for animating your application.
- One of the simplest animation effects is achieved by toggling between two images. For example, you may have a picture of a stoplight with a red light. By quickly changing this picture to one with a green light, we achieve a dynamic effect - animation. Picture boxes and image boxes are used to achieve this effect.
- Another approach to animation is to rotate through several pictures - each a slight change in the previous picture - to obtain a longer animation. This is the principle motion pictures are based on - pictures are flashed by us at 24 frames per second and our eyes are tricked into believing things are smoothly moving. Control arrays are usually used to achieve this type of animation.
4. More elaborate effects can be achieved by moving an image while, at the same, time changing the displayed picture. Effects such as a little guy walking across the screen are easily achieved. An object is moved using the Move method. You can do both absolute and relative motion (using an object's Left and Top properties).
For examp le, to move a picture box named picExample to the coordinate (100, 100), use:
picExample.Move 100, 100
To move it 20 twips to the right and 50 twips down, use:
picExample.Move picExample.Left + 20, picExample.Top + 50
Quick Example: Simple Animation
1. Start a new application. Place three image boxes on the form. Set the following properties:
Image1:
Picture mail02a.ico
Visible False
Image2:
Picture mail02b.ico
Visible False
Image3:
Picture mail02a.ico
Stretch True
Make Image3 larger than default size, using the ‘handles.’
A few words about what we're going to do. Image1 holds a closed envelope, while Image2 holds an opened one. These images are not visible - they will be selected for display in Image3 (which is visible) as Image3 is clicked. (This is similar to hiding things in the drag and drop example.) It will seem the envelope is being torn opened, then repaired.
2. Attach the following code to the Image3_Click procedure.
Private Sub Image3_Click()
Static PicNum As Integer
If PicNum = 0 Then
Image3.Picture = Image2.Picture : PicNum = 1
Else
Image3.Picture = Image1.Picture : PicNum = 0
End If
End Sub
When the envelope is clicked, the image displayed in Image3 is toggled (based on the value of the static variable PicNum).
3. Run and save the application.
Quick Example: Animation with the Timer Tool
1. In this example, we cycle through four different images using timer controlled animation. Start a new application. Put two image boxes, a timer tool, and a command button on the form. Set these properties:
Image1:
Picture trffc01.ico
Visible False
Now copy and paste this image box three times, so there are four elements in the Image1 control array. Set the Picture properties of the other three elements to:
Image1(1) :
Picture trffc02.ico
Image1(2) :
Picture trffc03.ico
Image1(3) :
Picture trffc04.ico
Image2:
Picture trffc01.ico
Stretch True
Command1:
Caption Start/Stop
Timer1:
Enabled False
Interval 200
The form should resemble this:
- Attach this code to the Command1_Click procedure.
Private Sub Command1_Click()
Timer1.Enabled = Not (Timer1.Enabled)
End Sub
The timer is turned on or off each time this code is invoked.
- Attach this code to the Timer1_Timer procedure.
Private Sub Timer1_Timer()
Static PicNum As Integer
PicNum = PicNum + 1
If PicNum > 3 Then PicNum = 0
Image2.Picture = Image1(PicNum).Picture
End Sub
This code changes the image displayed in the Image2 box, using the static variable PicNum to keep track of what picture is next.
4. Save and run the application. Note how the timer tool and the four small icons do not appear on the form at run-time. The traffic sign appears to be spinning, with the display updated by the timer tool every 0.2 seconds (200 milliseconds).
5. You can make the sign ‘walk off’ one side of the screen by adding this line after setting the Picture property:
Image2.Move Image2.Left + 150
CREATING DATABASE APPLICATIONS IN VISUAL BASIC – PART I
Visual basic allows us to manage database created with different database program such as MS Access, Dbase, Paradox and etc. In this lesson, we are not dealing with how to create database files but we will see how we can access database files in the VB environment.
In the following example, we will create database applications which enable one to browse customer’s names. To create this application, insert the data control into the new form. Place the data control somewhere at the bottom of the form. Name the data control as data_navigator. To be able to use the data control, we need to connect it to any database. We can create a database file using any database application but I suggest we use the database files that come with VB6.0. Let select MS.MDB as our database file. To connect the data control to this database, double – click the DatabaseName property in the properties window and select the above file, i.e. MS.MDB. Next, double – click on the RecordSource property to select the customer table from the database. You can also change the caption of the data control to anything but I use “Click to browse Customers” here.
After that, we will place a label and change its caption to Customer Name. Last but not least, insert another label and name it as cus_name and leave the label empty as customer’s names will appear here when we click the arrows on the data control. We need to bind this label to the data control for the application to work. To do this, open the label’s DataSource and select data_navigator that will appear automatically. One more thing we need to do is to bind the label to the correct filed so that data in this field will appear on this label. To do this, open the DataFiled property and select ContactName. Now, press F5 and run the program. You should be able to browse all the customer’s names by clicking the arrows on the data control.
The design Interface:
Figure 21.1
In this project, you need to insert a Label, Text Box and Data Tool into your form.
Then, go to Add-Ins Menu à Visual Data Manager
o Now open the VisData Window
o Select File menu, choose new and Microsoft Access, version 7.00 MDB
o Save the MDB file with file name (MS.MDB)
o Now open the small window of Database Window and display the properties
o Select the properties and right click and choose New Table
o Enter the Table Name (Example - Name)
o Now, Press the Add Field button
o Enter the Name field and then press OK button
o Name field go to Table structure window box and add the Field List section
o Now, press Build the Table button and Close the Table Structure Window
o Now, you see the Name field in Database Window
o Select the Name field and right click
o Choose the Open option and Open Dynaset:Name Box (Name – Table name)
o Press Add command button Enter the name in the Text Box and press Update
o Add few names in the name field
o Then, Close the Dynaset:Name Box
o Now, Close the VisData Window
o Select the Data1 Tool on the VB Form and go to its properties
o Databasename – select MS.MDB path
o RecordSource - select Name (Table Name)
o Then, select Text Box and go to its properties
o DataSource – Select Data1 (Tool)
o DataField – Name (Field Name)
o Now, you run the project
o You click the Data tool arrow button and you see the names on the text box.
Output
Figure –21.2
You can also add other fields using exactly the same method. For example, you can add Address, City and Telephone number to the database browser.
Figure – 21.3
DATABASE ACCESS AND MANAGEMENT
DATABASE ACCESS AND MANAGEMENT
- In simplest terms, a database is a collection of information. This collection is stored in well-defined tables, or matrices.
- The rows in a database table are used to describe similar items. The rows are referred to as database records . In general, no two rows in a database table will be alike.
- The columns in a database table provide characteristics of the records. These characteristics are called database fields . Each field contains one specific piece of information. In defining a database field, you specify the data type, assign a length, and describe other attributes.
- Here is a simple database example:
- In this database table, each record represents a single individual. The fields (descriptors of the individuals) include an identification number (ID No), Name, Date of Birth, Height, and Weight.
- Most databases use indexes to allow faster access to the information in the database. Indexes are sorted lists that point to a particular row in a table. In the example just seen, the ID No field could be used as an index.
- A database using a single table is ca lled a flat database. Most databases are made up of many tables. When using multiple tables within a database, these tables must have some common fields to allow cross-referencing of the tables. The referral of one table to another via a common field is called a relation. Such groupings of tables are called relational databases.
- In our first example, we will use a sample database that comes with Visual Basic. This database (BIBLIO.MDB) is found in the main Visual Basic directory (try c:\Program Files\Microsoft Visual Studio \VB98). It is a database of books about computers. Let’s look at its relational structure. The BIBLIO.MDB database is made up of four tables:
Authors Table (6246 Records, 3 Fields)
Publishers Table (727 Records, 10 Fields)
Title Author Table (16056 Records, 2 Fields)
Titles Table (8569 Records, 8 Fields)
The Authors table consists of author identification numbers, the author’s name, and the year born. The Publishers table has information regarding book publishers. Some of the fields include an identification number, the publisher name, and pertinent phone numbers. The Title Author table correlates a book’s ISBN (a universal number assigned to books) with an author’s identification number. And, the Titles table has several fields describing each individual book, including title, ISBN, and publisher identification.
Note each table has two types of information: source data and relational data.Source data is actual information, such as titles and author names. Relational data are references to data in other tables, such as Au_ID and PubID. In the Authors, Publishers and Title Author tables, the first column is used as the table index. In the Titles table, the ISBN value is the index.
Using the relational data in the four tables, we should be able to obtain a complete description of any book title in the database. Let’s look at one example:
Here, the book in the Titles table, entitled “Step-by-step dBase IV,” has an ISBN of 0-0280095-2-5 and a PubID of 52. Taking the PubID into the Publishers table, determines the book is published by McGraw-Hill and also allows us to access all other information concerning the publisher. Using the ISBN in the Title Author table provides us with the author identification (Au_ID) of 171, which, when used in the Authors table, tells us the book’s author is Toby Wraye.
We can form alternate tables from a database’s inherent tables. Such virtual tables, or logical views , are made using queries of the database. A query is simply a request for information from the database tables. As an example with the BIBLIO.MDB database, using pre-defined query languages, we could ‘ask’ the database to form a table of all authors and books published after 1992, or provide all author names starting with B. We’ll look briefly at queries.
Keeping track of all the information in a database is handled by a database management system (DBMS). They are used to create and maintain databases. Examples of commercial DBMS programs are Microsoft Access, Microsoft FoxPro, Borland Paradox, Borland dBase, and Claris FileMaker. We can also use Visual Basic to develop a DBMS. Visual Basic shares the same ‘engine’ used by Microsoft Access, known as the Jet engine. In this class, we will see how to use Visual Basic to access data, display data, and perform some elementary management operations.
22.1 ADO
The ADO
As mentioned in Review and Preview, previous versions of Visual Basic used another data control. That control is still included with Visual Basic 6.0 (for backward compatibility) and has as its icon:
Make sure you are not using this data control for the work in this class. This control is suitable for small databases. You might like to study it on your own.
The data control (or tool) can access databases created by several other programs besides Visual Basic (or Microsoft Access). Some other formats supported include Btrieve, dBase, FoxPro, and Paradox databases.
The data control can be used to perform the following tasks:
1. Connect to a database.
2. Open a specified database table.
3. Create a virtual table based on a database query.
4. Pass database fields to other Visual Basic tools, for display or editing.
Such tools are bound tools (controls), or data aware.
5. Add new records or update a database.
6. Trap any errors that may occur while accessing data.
7. Close the database.
Data Control Properties:
Align Determines where data control is displayed.
Caption Phrase displayed on the data control.
ConnectionString Contains the information used to establish a
connection to a database.
LockType Indicates the type of locks placed on records during
editing (default setting makes databases read-only).
Recordset A set of records defined by a data control’s
ConnectionString and RecordSource properties.
Run-time only.
RecordSource Determines the table (or virtual table) the data
control is attached to.
As a rule, you need one data control for every database table, or virtual table, you need access to. One row of a table is accessible to each data control at any one time. This is referred to as the current record.
When a data control is placed on a form, it appears with the assigned caption and four arrow buttons:
The arrows are used to navigate through the table rows (records). As indicated, the buttons can be used to move to the beginning of the table, the end of the table, or from record to record.
22.2 Data Links
After placing a data control on a form, you set the ConnectionString property. The ADO
If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files,
select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an
Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is
now created and can be assigned to ConnectionString, using the steps below.
If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
Assigning Tables
Once the ADO
Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English- like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in database. Click OK.
Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
22.3 Bound Data Tools
- Most of the Visual Basic tools we’ve studied can be used as bound, or data-aware, tools (or controls). That means, certain tool properties can be tied to a particular database field. To use a bound control, one or more data controls must be on the form.
- Some bound data tools are:
Label Can be used to provide display-only access to a
specified text data field.
Text Box Can be used to provide read/write access to a
specified text data field. Probably, the most widely
used data bound tool.
Check Box Used to provide read/wr ite access to a Boolean
field.
Combo Box Can be used to provide read/write access to a text
data field.
List Box Can be used to provide read/write access to a text
data field.
Picture Box Used to display a graphical image from a bitmap,
icon, or metafile on your form. Provides read/write
access to a image/binary data field.
Image Box Used to display a graphical image from a bitmap,
icon, or metafile on your form (uses fewer resources
than a picture box). Provides read/write access to a
image/binary data field.
- There are also three ‘custom’ data aware tools, the DataCombo (better than using the bound combo box), DataList (better than the bound list box), and DataGrid tools, we will look at later.
- Bound Tool Properties:
DataChanged Indicates whether a value displayed in a bound
control has changed.
DataField Specifies the name of a field in the table pointed to
by the respective data control.
DataSource Specifies which data control the control is bound to.
If the data in a data-aware control is changed and then the user changes focus to another control or tool, the database will automatically be updated with the new data (assuming LockType is set to allow an update).
To make using bound controls easy, follow these steps (in order listed) in placing the controls on a form:
1. Draw the bound control on the same form as the data control to which it will be bound.
2. Set the DataSource property. Click on the drop-down arrow to list the data controls on your form. Choose one.
3. Set the DataField property. Click on the drop-down arrow to list the fields associated with the selected data control records. Make your choice. 4. Set all other properties, as required.
By following these steps in order, we avoid potential data access errors.
- The relationships between the bound data control and the data control are:
Example
Accessing the Books Database
1. Start a new application. We’ll develop a form where we can skim through the books database, examining titles and ISBN values. Place an ADO
2. If you haven’t done so, create a data link for the BIBLIO.MDB database following the steps given under Data Links in these notes.
3. Set the following properties for each control. For the data control and the two text boxes, make sure you set the properties in the order given.
Form1:
BorderStyle 1-Fixed Single
Caption Books Database
Name frmBooks
Adodc1:
Caption Book Titles
ConnectionString BIBLIO.UDL (in whatever folder you saved it in -
select, don’t type)
RecordSource SELECT * FROM Titles
Name dtaTitles
Label1:
Caption Title
Label2:
Caption ISBN
Text1:
DataSource dtaTitles (select, don’t type)
DataField Title (select, don’t type)
Locked True
MultiLine True
Name txtTitle
Text [Blank]
Text2:
DataSource dtaTitles (select, don’t type)
DataField ISBN (select, don’t type)
Locked True
Name txtISBN
Text [Blank]
When done, the form will look something like this (try to space your controls as shown; we’ll use all the blank space as we continue with this example):
4. Save the application. Run the application. Cycle through the various book title susing the data control. Did you notice something? You didn’t have to write one line of Visual Basic code! This indicates the power behind the data tool and bound tools.
22.4 Creating a Virtual Table
Many times, a database table has more information than we want to display. Or, perhaps a table does not have all the information we want to display. For instance, in Example 1, seeing the Title and ISBN of a book is not real informative - we would also like to see the Author, but that information is not provided by the Titles table. In these cases, we can build our own virtual table, displaying only the information we want the user to see.
We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.
Quick Example: Forming a Virtual Table
- We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:
SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles
WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title
Author].ISBN ORDER BY Author
This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.
Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.
- Add a label box and text box to the form, for displaying the author name. Set the control properties.
Label3:
Caption Author
Text1:
DataSource dtaTitles (select, don’t type)
DataField Author (select, don’t type)
Locked True
Name txtAuthor
Text [Blank]
When done, the form should resemble this:
- Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code? I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names.
Finding Specific Records
In addition to using the data control to move through database records, we can write Visual Basic code to accomplish the same, and other, tasks. This is referred to as programmatic control. In fact, many times the data control Visible property is set to False and all data manipulations are performed in code. We can also use programmatic control to find certain records.
There are four methods used for moving in a database. These methods replicate the capabilities of the four arrow buttons on the data control:
MoveFirst Move to the first record in the table.
MoveLast Move to the last record in the table.
MoveNext Move to the next record (with respect to the current
record) in the table.
MovePrevious Move to the previous record (with respect to the
current record) in the table.
When moving about the database programmatically, we need to test the BOF (beginning of file) and EOF (end of file) properties. The BOF property is True when the current record is positioned before any data. The EOF property is True when the current record has been positioned past the end of the data. If either property is True, the current record is invalid. If both properties are True, then there is no data in the database table at all.
These properties, and the programmatic control methods, operate on the Recordset property of the data control. Hence, to move to the first record in a table attached to a data control named dtaExample, the syntax is:
dtaExample.Recordset.MoveFirst
There is a method used for searching a database:
Find Find a record that meets the specified search criteria.
This method also operates on the Recordset property and has three arguments we will be concerned with. To use Find with a data control named dtaExample :
dtaExample.Recordset.Find Criteria,NumberSkipped,SearchDirection
The search Criteria is a string expression like a WHERE clause in SQL. We won’t go into much detail on such criteria here. Simply put, the criteria describes what particular records it wants to look at. For example, using our book database, if we want to look at books with titles (the Title field) beginning with S, we would use:
Criteria = “Title >= ‘S’”
Note the use of single quotes around the search letter. Single quotes are used to enclose strings in Criteria statements. Three logical operators can be used: equals (=), greater than (>), and less than (<). · The NumberSkipped argument tells how many records to skip before beginning the Find. This can be used to exclude the current record by setting NumberSkipped to 1.
The SearchDirection argument has two possible values: adSearchForward or adSearchBackward. Note, in conjunction with the four Move methods, the SearchDirection argument can be used to provide a variety of search types (search from the top, search from the bottom, etc.)
If a search fails to find a record that matches the criteria, the Recordset’s EOF or BOF property is set to True (depending on search direction). Another property used in searches is the Bookma rk property. This allows you to save the current record pointer in case you want to return to that position later. The example illustrates its use.
Example
‘Rolodex’ Searching of the Books Database
1. We expand the book database application to allow searching for certain author names. We’ll use a ‘rolodex’ approach where, by pressing a particular letter button, books with author last names corresponding to that button appear on the form.
2. We want a row of buttons starting at ‘A’ and ending at ‘Z’ to appear on the lower part of our form. Drawing each one individually would be a big pain, so we’ll let Visual Basic do all the work in the Form_Load procedure. What we’ll do is create one command button (the ‘A’), make it a control array, and then dynamically create 25 new control array elements at run-time, filling each with a different letter. We’ll evenlet the code decide on proper spacing.
So, add one command button to the previous form. Name it cmdLetter and give it a Caption of A. Set its Index property to 0 to make it a control array element. On my form, things at this point look like this:
3. Attach this code to the Form_Load procedure. This code sets up the rolodex control array and draws the additional 25 letter buttons on the form. (Sorry, you have to type some code now!)
Private Sub Form_Load()
Dim I As Integer
‘Size buttons
cmdLetter(0).Width = (frmBooks.ScaleWidth - 2*
cmdLetter(0).Left) / 26
For I = 1 To 25
Load cmdLetter(I) ' Create new control array element
'Position new letter next to previous one
cmdLetter(I).Left = cmdLetter(I - 1).Left +
cmdLetter(0).Width
'Set caption and make visible
cmdLetter(I).Caption = Chr(vbKeyA + I)
cmdLetter(I).Visible = True
Next I
End Sub
At this point, even though all the code is not in place, you could run your application to check how the letter buttons look. My finished form (at run-time) looks like this:
Notice how Visual Basic adjusted the button widths to fit nicely on the form.
- Attach this code to the cmdLetter_Click procedure. In this procedure, we use a search criteria that finds the first occurrence of an author name that begins with the selected letter command button. If the search fails, the record displayed prior to the search is retained (using the Bookmark property).
Private Sub cmdLetter_Click(Index As Integer)
Dim BookMark1 As Variant
'Mark your place in case no match is found
BookMark1 = dtaTitles.Recordset.Bookmark
'Move to top of table to start search
dtaTitles.Recordset.MoveFirst
dtaTitles.Recordset.Find "Author >= '" +
cmdLetter(Index).Caption + "'", 0, adSearchForward
If dtaTitles.Recordset.EOF = True Then
dtaTitles.Recordset.Bookmark = BookMark1
End If
txtAuthor.SetFocus
End Sub
Let’s look at the search a little closer. We move to the top of the database using Move First. Then, the Find is executed (notice the selected letter is surrounded by single quotes). If EOF is True after the Find, it means we didn’t find a match to the Criteria and Bookmark is returned to its saved value.
- Save your application. Test its operation. Note once the program finds the first occurrence of an author name beginning with the selected letter (or next highest letter if there is no author with the pressed letter), you can use the data control navigation buttons (namely the right arrow button) to find other author names beginning with that letter.
Data Manager
At this point, we know how to use the data control and associated data bound tools to access a database. The power of Visual Basic lies in its ability to manipulate records in code. Such tasks as determining the values of particular fields, adding records, deleting records, and moving from record to record are easily done. This allows us to build a complete database management system (DBMS).
We don’t want to change the example database, BIBLIO.MDB. Let’s create our own database to change. Fortunately, Visual Basic helps us out here. The Visual Data Manager is a Visual Basic Add-In that allows the creation and management of databases. It is simple to use and can create a database compatible with the Microsoft Jet (or Access) database engine.
To examine an existing database using the Data Manager, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may
be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select Open Database from the Data Manager File menu.
3. Select the database type and name you want to examine.
Once the database is opened, you can do many things. You can simply look through the various tables. You can search for particular records. You can apply SQL queries. You can add/delete records. The Data Manager is a DBMS in itself. You might try using the Data Manager to look through the BIBLIO.MDB example database.
To create a new database, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select New from the Data Manager File menu. Choose database type (Microsoft Access, Version 7.0), then select a directory and enter a name for your database file. Click OK.
3. The Database window will open. Right click the window and select New
Table. In the Name box, enter the name of your table. Then define the table’s fields, one at a time, by clicking Add Field, then entering a field name, selecting a data type, and specifying the size of the field, if required. Once the field is defined, click the OK button to add it to the field box. Once all fields are defined, click the Build the Table button to save your table.
Example
Phone Directory - Creating the Database
1. With this example, we begin the development of a simple phone directory. In the directory, we will keep track of names and phone numbers. We’ll be able to edit, add and delete names and numbers from the directory. And, we’ll be able to search the directory for certain names. In this first step, we’ll establish the structure for the database we’ll use. The directory will use a single table, with three fields: Name , Description, and Phone. Name will contain the name of the person or company, Description will contain a descriptive phrase (if desired) of who the person or company is, and Phone will hold the phone number.
2. Start the Data Manager. Use the previously defined steps to establish a new database (this is a Microsoft Access, Version 7.0 database). Use PhoneList as a Name for your database table. Define the three fields. Each should be a Text data type. Assign a size of 40 to the Name and Description fields, a size of 15 to the Phone field. When all fields have been defined, the screen should look like this:
When done with the field definitions, click Build the Table to save your new table. You will be returned to the Database Tables window.
3. We’re now ready to enter some data in our database. From the Database Tables window, right click the PhoneList table and select Open. The following window will appear:
At this point, add several (at least five - make them up or whatever) records to your database. The steps for each record are: (1) click Add to add a record, (2) fill in the three fields (or, at least the Name and Phone fields), and (3) click Update to save the contents.
You can also Delete records and Find records, if desired. You can move through the records using the scroll bar at the bottom of the screen. When done entering records, click Close to save your work. Select Exit from the Data Manager File menu. Your database has been created.
Database Management
The Data Manager is a versatile utility for creating and viewing databases. However, its interface is not that pretty and its use is somewhat cumbersome. We would not want to use it as a database management system (DBMS). Nor, would we expect users of our programs to have the Data Manager available for their use. The next step in our development of our database skills is to use Visual Basic to manage our databases, that is develop a DBMS.
We will develop a simple DBMS. It will allow us to view records in an existing database. We will be able to edit records, add records, and delete records. Such advanced tasks as adding tables and fields to a database and creating a new database can be done with Visual Basic, but are far beyond the scope of the discussion here.
To create our DBMS, we need to define a few more programmatic control methods associated with the data control Recordset property. These methods are:
AddNew A new record is added to the table. All fields are
set to Null and this record is made the current
record.
Delete The current record is deleted from the table. This
method must be immediately followed by one of the
Move methods because the current record is invalid
after a Delete.
Update Saves the current contents of all bound tools.
To edit an existing record, you simply display the record and make any required changes. The LockType property should be set to adLockPessimistic (locks each record as it is edited). Then, when you move off of that record, either with a navigation button or through some other action, Visual Basic will automatically update the record. If desired, or needed, you may invoke the Update method to force an update (use LockType = asLockOptimistic). For a data control named dtaExample, the syntax for this statement is:
dtaExample.Recordset.Update
To add a record to the database, we invoke the AddNew method. The syntax for our example data control is:
dtaExample.Recordset.AddNew
This statement will blank out any bound data tools and move the current record to the end of the database. At this point, you enter the new values. When you move off of this record, the changes are automatically made to the database. Another way to update the database with the changes is via the Update method.
After adding a record to a database, you should invoke the Refresh property of the data control to insure proper sorting (established by RecordSource SQL statement) of the new entry. The format is:
dtaExample.Refresh
To delete a record from the database, make sure the record to delete is the current record. Then, we use the Delete method. The syntax for the example data control is:
dtaExample.Recordset.Delete
Once we execute a Delete, we must move (using one of the ‘Move’ methods) off of the current record because it no longer exists and an error will occur if we don’t move. This gets particularly tricky if deleting the last record (check the EOF property). If EOF is true, you must move to the top of the database (MoveFirst). You then must make sure there is a valid record there (check the BOF property). The example code demonstrates proper movement.
Example
Phone Directory - Managing the Database
1. Before starting, make a copy of your phone database file using the Windows Explorer. That way, in case we mess up, you still have a good copy. And, create a data link to the database. Here, we develop a simple DBMS for our phone number database. We will be able to display individual records and edit them. And, we will be able to add or delete records. Note this is a simple system and many of the fancy ‘bells and whistles’ (for example, asking if you really want to delete a record) that should really be here are not. Adding such amenities is left as an exercise to the student.
2. Load your last Books Database application (Example - the one with the ‘Rolodex’ search). We will modify this application to fit the phone number DBMS. Resave your form and project with different names. Add three command buttons to the upper right corner of the form. Modify/set the following properties for each tool. For the data control and text boxes, make sure you follow the order shown.
frmBooks (this is the old name):
Caption Phone List
Name frmPhone
dtaTitles (this is the old name):
Caption Phone Numbers
ConnectionString [your phone database data link] (select, don’t type)
RecordSource SELECT * FROM PhoneList ORDER BY Name (the
ORDER keyword sorts the database by the given
field)
Name dtaPhone
LockType adLockOptimistic
Label1:
Caption Description
Label2:
Caption Phone
Label3:
Caption Name
txtAuthor (this is the old name):
DataSource dtaPhone (select, don’t type)
DataField Name (select, don’t type)
Locked False
Name txtName
MaxLength 40
TabIndex 1
txtISBN (this is the old name) :
DataSource dtaPhone (select, don’t type)
DataField Phone (select, don’t type)
Locked False
Name txtPhone
MaxLength 15
TabIndex 3
txtTitle (this is the old name):
DataSource dtaPhone (select, don’t type)
DataField Description (select, don’t type)
Locked False
Name txtDesc
MaxLength 40
TabIndex 2
Command1:
Caption &Add
Name cmdAdd
Command2:
Caption &Save
Enabled False
Name cmdSave
Command3:
Caption &Delete
Name cmdDelete
When done, my form looked like this:
At this point, you can run your application and you should be able to navigate through your phone database using the data control. Don’t try any other options, though. We need to do some coding.
3. In Form_Load, replace the word frmBooks with frmPhone . This will allow the letter keys to be displayed properly.
4. In the cmdLetter_Click procedure, replace all occurrences of the word dtaTitles with dtaPhone. Replace all occurrences of Author with Name. The modified code will be:
Private Sub cmdLetter_Click(Index As Integer)
Dim BookMark1 As Variant
'Mark your place in case no match is found
BookMark1 = dtaPhone.Recordset.Bookmark
dtaPhone.Recordset.MoveFirst
dtaPhone.Recordset.Find "Name >= '" +
cmdLetter(Index).Caption + "'"
If dtaPhone.Recordset.EOF = True Then
dtaPhone.Recordset.Bookmark = BookMark1
End If
txtName.SetFocus
End Sub
5. Attach this code to the cmdAdd_Click procedure. This code invokes the code needed to add a record to the database. The Add and Delete buttons are disabled. Click the Save button when done adding a new record.
Private Sub cmdAdd_Click()
cmdAdd.Enabled = False
cmdSave.Enabled = True
cmdDelete.Enabled = False
dtaPhone.Recordset.AddNew
txtName.SetFocus
End Sub
6. Add this code to the cmdSave_Click procedure. When done entering a new record, the command button status’s are toggled, the Recordset updated, and the data control Refresh method invoked to insure proper record sorting.
Private Sub cmdSave_Click()
dtaPhone.Recordset.Update
dtaPhone.Refresh
cmdAdd.Enabled = True
cmdSave.Enabled = False
cmdDelete.Enabled = True
txtName.SetFocus
End Sub
7. Attach this code to the cmdDelete_Click procedure. This deletes the current record and moves to the next record. If we bump into the end of file, we need to check if there are no records remaining. If no records remain in the table, we display a message box. If records remain, we move around to the first record.
Private Sub cmdDelete_Click()
dtaPhone.Recordset.Delete
dtaPhone.Recordset.MoveNext
If dtaPhone.Recordset.EOF = True Then
dtaPhone.Refresh
If dtaPhone.Recordset.BOF = True Then
MsgBox "You must add a record.", vbOKOnly +
vbInformation, "Empty file"
Call cmdAdd_Click
Else
dtaPhone.Recordset.MoveFirst
End If
End If
txtName.SetFocus
End Sub
- Save the application. Try running it. Add records, delete records, edit records. If you’re really adventurous, you could add a button that dials your phone (via modem) for you! Look at the custom communications control.
Custom Data Aware Controls
- As mentioned earlier, there are three custom data aware tools, in addition to the standard Visual Basic tools: the DataList, DataCombo, and DataGrid
- Like the data control, previous versions of Visual Basic used DAO versions of the list, combo, and grid controls, named DBList, DBCombo, and DBGrid. Make sure you are not using these tools.
- DataList Box:
The first bound data custom tool is the DataList Box. The list box is automatically filled with a field from a specified data control. Selections from the list box can then be used to update another field from the same data control or, optionally, used to update a field from another data control.
Some properties of the DataList box are:
DataSource Name of data control that is updated by the
selection.
DataField Name of field updated in Recordset specified by
DataSource.
RowSource Name of data control used as source of items in list
box.
ListField Name of field in Recordset specified by RowSource
used to fill list box.
BoundColumn Name of field in Recordset specified by RowSource
to be passed to DataField, once selection is made.
This is usually the same as ListField.
BoundText Text value of BoundColumn field. This is the value
passed to DataField property.
Text Text value of selected item in list. Usually the same
as BoundText.
The most prevalent use of the DataList box is to fill the list from the database, then allow selections. The selection can be used to fill any tool on a form, whether it is data aware or not.
As a quick example, here is a DataList box filled with the Title (ListField) field from the dtaExample (RowSource) data control. The data control is bound to the Titles table in the BIBLIO.MDB database.
- DataCombo Box:
The DataCombo Box is nearly identical to the DataList box, hence we won’t look at a separate set of properties. The only differences between the two tools is that, with the DataCombo box, the list portion appears as a drop-down box and the user is given the opportunity to change the contents of the returned Text property.
- DataGrid Tool:
The DataGrid tool is, by far, the most useful of the custom data bound tools. It can display an entire database table, referenced by a data control. The table can then be edited as desired.
The DataGrid control is in a class by itself, when considering its capabilities. It is essentially a separate, highly functional program. The only property we’ll be concerned with is the DataSource property, which, as always, identifies the table associated with the respective data control. Refer to the Visual Basic Programmer’s Guide and other references for complete details on using the DataGrid control.
As an example of the power of the DataGrid control, here’s what is obtained by simply setting the DataSource property to the dtaExample data control, which is bound to the Titles table in the BIBLIO.MDB database:
At this point, we can scroll through the table and edit any values we choose. Any changes are automatically reflected in the underlying database. Column widths can be changed at run-time! Multiple row and column selections are possible! Like we said, a very powerful tool.
22.5 Creating a Data Report
- Once you have gone to all the trouble of developing and managing a database, it is nice to have the ability to obtain printed or displayed information from your data. The process of obtaining such information is known as creating a data report.
- There are two steps to creating a data report. First, we need to create a Data Environment. This is designed within Visual Basic and is used to tell the data report what is in the database. Second, we create the Data Report itself. This, too, is done within Visual Basic. The Data Environment and Data Report files then become part of the Visual Basic project developed as a database management system. · The Visual Basic 6.0 data report capabilities are vast and using them is a detailed process. The use of these capabilities is best demonstrated by example. We will look at the rudiments of report creation by building a tabular report for our phone database.
Example
Phone Directory - Building a Data Report
We will build a data report that lists all the names and phone numbers in our phone database. We will do this by first creating a Data Environment, then a Data Report. We will then reopen the phone database management project and add data reporting capabilities.
Creating a Data Environment
1. Start a new Standard EXE project.
2. On the Project menu, click Add Data Environment. If this item is not on the menu, click Components. Click the Designers tab, and choose Data Environment and click OK to add the designer to your menu.
3. We need to point to our database. In the Data Environment window, right-click the Connection1 tab and select Properties. In the Data Link Properties dialog box, choose Microsoft Jet 3.51 OLE DB Provider. Click Next to get to the Connection tab. Click the ellipsis button. Find your phone database (mdb) file. Click OK to close the dialog box.
4. We now tell the Data Environment what is in our database. Right-click the Connection1 tab and click Rename . Change the name of the tab to Phone . Rightclick this newly named tab and click Add Command to create a Command1 tab. Right-click this tab and choose Properties. Assign the following properties:
Command Name PhoneList
Connection Phone
DataBase Object Table
ObjectName PhoneList
5. Click OK. All this was needed just to connect the environment to our database.
6. Display the properties window and give the data environment a name property of denPhone . Click File and Save denPhone As. Save the environment in an appropriate folder. We will eventually add this file to our phone database management system. At this point, my data environment window looks like this (I expanded the PhoneList tab by clicking the + sign):
Creating a Data Report
Once the Data Environment has been created, we can create a Data Report. We will drag things out of the Data Environment onto a form created for the Data Report, so make sure your Data Environment window is still available.
1. On the Project menu, click Add Data Report and one will be added to your project. If this item is not on the menu, click Components. Click the Designers tab, and choose Data Report and click OK to add the designer to your menu.
2. Set the following properties for the report:
Name rptPhone
Caption Phone Directory
DataSource denPhone (your phone data environment - choose, don’t type)
DataMember PhoneList (the table name - choose don’t type)
3. Right-click the Data Report and click Retrieve Structure . This establishes a report format based on the Data Environment.
4. Note there are five sections to the data report: a Report Header, a Page Header, a Detail section, a Page Footer, and a Report Footer. The headers and footers contain information you want printed in the report and on each page. To place information in one of these regions, right-click the selected region, click Add Control, then choose the control you wish to place. These controls are called data report controls and properties are established just like you do for usual controls. Try adding some headers.
5. The Detail section is used to layout the information you want printed for each record in your database. We will place two field listings (Name, Phone) there. Click on the Name tab in the Data Environment window and drag it to the Detail section of the Data Report. Two items should appear: a text box Name and a text box Name (PhoneList). The first text box is heading information. Move this text box into the Page Header section. The second text box is the actual value for Name from the PhoneList table. Line this text box up under the Name header. Now, drag the Phone tab from the Data Environment to the Data Report. Adjust the text boxes in the same manner. Our data report will have page headers Name and Phone. Under these headers, these fields for each record in our database will be displayed. When done, the form should look something like this:
In this form, I’ve resized the labels a bit and added a Report Header. Also, make sure you close up the Detail section to a single line. Any space left in this section will be inserted after each entry.
6. Click File and Save rptPhone As. Save the environment in an appropriate folder. We will now reopen our phone database manager and attach this and the data environment to that project and add capabilities to display the report.
Accessing the Data Report
1. Reopen the phone directory project. Add a command button named cmdReport and give it a Caption of Show Report. (There may be two tabs in your toolbox, one named General and one named DataReport. Make sure you select from the General tools.)
2. We will now add the data environment and data report files to the project. Click the Project menu item, then click Add File. Choose denPhone and click OK. Also add rptPhone. Look at your Project Window. Those files should be listed under Designers .
3. Use this code in cmdReport_Click:
Private Sub cmdReport_Click()
rptPhone.Show
End Sub
4. This uses the Show method to display the data report.
5. Save the application and run it. Click the Show Report button and this should appear:
You now have a printable copy of the phone directory. Just click the Printer icon. Notice the relationship with this displayed report and the sections available in the Data Report designer.
Exercise
Home Inventory Database
Design and develop an application that manages a home inventory database. Add the option of obtaining a printed list of your inventoried property.
Database Design:
The first step is to design a database using Data Manager (or Access). My database is a single table (named MYSTUFF). Its specifications are:
Field Name Field Type Field Length
Item Text 40
Serial Number Text 20
Date Purchased Text 20
New Value Currency <N/A>
Location Text 40
This database is saved as file HomeInv.mdb. Create a data link to your database. The link is saved as HomeInv.udl.
Report Design:
The second step is to use the Data Environment and Data Report designers to setup how you want the printed home inventory to appear. Use your discretion here. My final report design is saved in denHomeInv and rptHomeInv. We will access this report from our Visual Basic application. My Data Report design looks like this:
Project Design:
Form:
Properties:
Form frmHome :
BorderStyle = 1 - Fixed Single
Caption = Home Inventory
CommandButton cmdExit:
Caption = E&xit
ADO Data Control dtaHome:
Caption = Book Titles
ConnectionString = HomeInv.udl (in whatever folder you saved it in -select, don’t type)
RecordSource = SELECT * FROM MyStuff
Visible = False
CommandButton cmdShow:
Caption = Show &Report
CommandButton cmdPrevious :
Caption = &Previous Item
CommandButton cmdNext:
Caption = &Next Item
CommandButton cmdDelete :
Caption = &Delete Item
CommandButton cmdAdd:
Caption = &Add Item
TextBox txtLocation:
DataField = Location
DataSource = dtaHome
FontName = MS Sans Serif
FontSize = 9.75
MaxLength = 40
TextBox txtValue:
DataField = New Value
DataSource = dtaHome
FontName = MS Sans Serif
FontSize = 9.75
TextBox txtDate:
DataField = Date Purchased
DataSource = dtaHome
FontName = MS Sans Serif
FontSize = 9.75
MaxLength = 20
TextBox txtSerial:
DataField = Serial Number
DataSource = dtaHome
FontName = MS Sans Serif
FontSize = 9.75
MaxLength = 20
TextBox txtItem:
DataField = Item
DataSource = dtaHome
FontName = MS Sans Serif
FontSize = 9.75
MaxLength = 40
Label Label5:
Caption = Location
FontName = Times New Roman
FontSize = 12
Label Label4:
Caption = New Value
FontName = Times New Roman
FontSize = 12
Label Label3:
Caption = Purchase Date
FontName = Times New Roman
FontSize = 12
Label Label2:
Caption = Serial Number
FontName = Times New Roman
FontSize = 12
Label Label1:
Caption = Item
FontName = Times New Roman
FontSize = 12
Code:
General Declarations:
Option Explicit
cmdAdd Click Event:
Private Sub cmdAdd_Click()
'Add new item to database
dtaHome.Recordset.AddNew
txtItem.SetFocus
End Sub
cmdDelete Click Event:
Private Sub cmdDelete_Click()
'Delete item from database
Dim Rvalue As Integer
Rvalue = MsgBox("Are you sure you want to delete this
item?", vbQuestion + vbYesNo, "Delete Item")
If Rvalue = vbNo Then Exit Sub
dtaHome.Recordset.Delete
dtaHome.Recordset.MoveNext
If dtaHome.Recordset.EOF Then
If dtaHome.Recordset.BOF Then
MsgBox "You must add an item.", vbOKOnly +
vbInformation, "Empty Database"
Call cmdAdd_Click
Else
dtaHome.Recordset.MoveFirst
End If
End If
txtItem.SetFocus
End Sub
cmdExit Click Event:
Private Sub cmdExit_Click()
End
End Sub
cmdNext Click Event:
Private Sub cmdNext_Click()
'Move to next item - if at end-of-file, backup one item
dtaHome.Recordset.MoveNext
If dtaHome.Recordset.EOF Then
dtaHome.Recordset.MovePrevious
txtItem.SetFocus
End Sub
cmdPrevious Click Event:
Private Sub cmdPrevious_Click()
'Move to previous item - if at beginning-of-file, go down
one item
dtaHome.Recordset.MovePrevious
If dtaHome.Recordset.BOF Then dtaHome.Recordset.MoveNext
txtItem.SetFocus
End Sub
cmdShow Click Event:
Private Sub cmdShow_Click()
rptHomeInv.Show
End Sub
CREATING DATABASE APPLICATIONS IN VISUAL BASIC – PART II
In Lesson 21, you have learned how to create a simple database application using data control. In this lesson, you will work on the same application but use some slightly more advance commands. The data control support some methods that are useful in manipulating the database, for example, to move the pointer to a certain location. The following are some of the commands that you can use to move the pointer around.
data_navigator.RecordSet.MoveFirst ‘Move to the first record
data_navigator.RecordSet.Last ‘Move to the last record
data_navigator.RecordSet.MoveNext ‘Move to the next record
data_navigator.RecordSet.MovePrevious ‘Move to the previous record
*note: data_navigator is the name of data control
In the following example, you shall insert four commands and label them as First Record, Next Record, Last Record and Previous Record. They will be used to navigator around the database without using the data control. You still need to retain the same data control (from example in lesson 19) but set the property Visible to no so that users will not see the data control but use the button to browse through the database instead. Now, double-click on the command button and key in the codes according to the labels.
Private Sub First_Click()
Data1.RecordSet.MoveFirst
End Sub
Private Sub Last_Click()
Data1.RecordSet.MoveLast
End Sub
Private Sub Next_Click()
Data1.RecordSet.MoveNext
End Sub
Private Sub Previous_Click()
Data1.RecordSet.MovePrevious
End Sub
Private Sub Exit_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Exit.ToolTipText = "Exit the Programme"
End Sub
Private Sub Previous_Click()
End
End Sub
Run the application and you shall obtain the interface below and you will be able to browse the database using the four command buttons.
Figure 23.1
Example 23.2
Enter the Employee Name, Number, Join Date & Basic Pay and Edit, and Find the Record
(i) The Interface
Figure – 23.2
(ii) The Codes
Dim en As Integer
Private Sub add_Click()
If add.Caption = “Add” Then
If Data1.Recordset.RecordCount = 0 Then
en = 100
Else
Data1.Recordset.MoveLast
en = Data1.Recordset (“EmpNo”) + 1
End If
Data1.Recordset.AddNew
Text2 = en
Text1.SetFocus
Text3 = Date $
add.Caption = “Update”
Else
Data1.Recordset..Update
add.Caption = “Add”
End If
End Sub
Private Sub edit_Click()
If edit.Caption = “Edit” Then
Data1.Recordset.Edit
Text1.SetFocus
edit.Caption = “Update”
Else
Data1.Recordset.Update
edit.Caption = “Edit”
End If
End Sub
Private Sub find_Click()
en = InputBox (“Enter the Employee No”)
Data1.Recordset.FindFirst (“EmpNo=” &en)
If Data1.Recordset.NoMatch Then
MsgBox (“File Not Found”)
End If
End Sub
Private Sub delete_Click()
Dim d As String
d = MsgBox (“Are you Sure?”, 4+32, “Delete”)
If d = vbYes Then
Data1.Recordset.Delete
Data1.Recordset.MovePrevious
If Data1.Recordset.BOF Then
Data1.Recordset.MoveNext
End If
End If
End Sub
Private Sub exit_Click()
End
End Sub
CREATING VB DATABASE APPLICATIONS USING ADO CONTROL
In Lesson 21 and Lesson 23, we have learned to build VB database applications using data control. However, data control is not a very flexible tool as it could work only with limited kinds of data and must work strictly in the Visual Basic environment. To overcome these limitations, we can use a much more powerful data control in VB known as ADO ADO ADO
To be able to use ADO
The following example will illustrate how to build a relatively powerful database application using ADO ADO
Figure – 24.1
The properties of all the controls are listed as follow:
Form Name
|
formbook Title
|
Form Caption
|
Book Title-ADO Application
|
adoBooks
| |
Label1 Name
|
lb1App
|
Label1 Caption
|
Book Titles
|
Label2 Name
|
lb1Title
|
Label2 Caption
|
Title:
|
Label3 Name
|
lb1Year
|
Label3 Caption
|
Year Published
|
Label4 Name
|
lb1ISBN
|
Label4 Caption
|
ISBN:
|
Label5 Name
|
lb1PubID
|
Label5 Caption
|
Publisher’s ID
|
Label6 Name
|
lb1Subject
|
Label6 Caption
|
Subject:
|
TextBox1 Name
|
txtitle
|
TextBox1 DataField
|
Title
|
TextBox1 DataSource
|
adoBooks
|
TextBox2 Name
|
txtPub
|
TextBox2 DataField
|
Year Punlished
|
TextBox2 DataSource
|
adoBooks
|
TextBox3 Name
|
txtISBN
|
TextBox3 DataField
|
ISBN
|
TextBox3 DataSource
|
adoBooks
|
TextBox4 Name
|
txtPubID
|
TextBox4 DataField
|
PubID
|
TextBox4 DataSource
|
adoBooks
|
TextBox5 Name
|
txtSubject
|
TextBox5 DataField
|
Subject
|
TextBox5 DataSource
|
adoBooks
|
Command1 Name
|
cmdSave
|
Command1 Caption
|
&Save
|
Command2 Name
|
cmdAdd
|
Command2 Caption
|
&Add
|
Command3 Name
|
cmdDelete
|
Command3 Caption
|
&Delete
|
Command4 Name
|
cmdCancel
|
Command4 Caption
|
&Cancel
|
Command5 Name
|
cmdPrev
|
Command5 Caption
|
&<
|
Command6 Name
|
cmdNext
|
Command6 Caption
|
&>
|
Command7 Name
|
cmdExit
|
Command7 Caption
|
E&xit
|
To be able to access and manage a database, you need to connect the ADO
- Click on the
- Click on the ADO ConnectionString property, the following dialog box will appear.
when the dialog box appear, select the Use Connection String’s Option. Next, click build and at the Data Link dialog box, double-Click the option labeled Microsoft Jet 3.51 OLE DB provider.
After that, click the Next button to select the file Biblo.MDB. You can click on Text Connection to ensure proper connection of the database file. Click OK to finish the connection.
Finally, click on the RecordSource property and set the command type to adCmd Table and Table name to Titles. Now you are really to use the database file.
Now, you need to write code for all the command buttons. After which, you can make the ADO control invisible.
For the Save button, the program codes are as follow:
Private Sub cmdSave_Click()
adoBooks.RecordSet.Fields(“Title”)=txtTitle.Text
adoBooks.RecordSet.Fields(“Year Published”)=txtPub.Text
adoBooks.RecordSet.Fields(“ISBN”)=txtISBN.Text
adoBooks.RecordSet.Fields(“PubID”)=txtPubID.Text
adoBooks.RecordSet.Fields(“Subject”)=txtSubject.Text
adoBooks.RecordSet.Update
End Sub
For the Add button, the program codes are as follow:
Private Sub cmdAdd_Click()
adoBooks.RecordSet.AddNew
End Sub
For the Delete button, the program codes are as follow:
Private Sub cmdDelete_Click()
Confirm=MsgBox(“Are you sure want to delete this record?”,
vbYesNo, “Deletion Confirmation”)
If Confirm = vbYes Then
adoBooks.RecordSet.Delete
MsgBox ”Record Deleted!”, , “Message”
Else
MsgBox ”Record Not Deleted!”, , “Message”
End If
End Sub
For the Delete button, the program codes are as follow:
Private Sub cmdCancel_Click()
txtTitle.Text = “ ”
txtPub.Text = “ ”
txtPubID.Text = “ ”
txtISBN.Text = “ ”
txtSubject.Text = “ ”
End Sub
For the Previous (<) button, the program codes are as follow:
Private Sub cmdPrev_Click()
If Not adoBooks.RecordSet.BOF Then
adoBooks.RecordSet.MovePrevious
If adoBooks.RecordSet.BOF Then
adoBooks.RecordSet.MoveNext
End If
End If
End Sub
For the Next (>) button, the program codes are as follow:
Private Sub cmdNext_Click()
If Not adoBooks.RecordSet.EOF Then
adoBooks.RecordSet.MoveNext
If adoBooks.RecordSet.EOF Then
adoBooks.RecordSet.Previous
End If
End If
End Sub
DAO – Data Access Objects
A data access object is a collection of object classes that model the structure of relational database system.
ADO – ActiveX Data Objects
In Visual Basic, three data access interface are available to you. ActiveX Data Objects (ADO), Remote Data Objects (RDO, and Data Access Objects (DAO).
ADO Use:-
ADO is designed as an easy to use application level interface to Microsoft’s newest and most powerful data access object.
ADO Compared with DAO and RDO
i) Data Access Object
ADO was the first Object – Oriented interface that exposed the Microsoft Jet database engine (Used by Microsoft Access) and allowed Visual Basic developers to directly connect to Access tables as well as database – through ODBC, DAO is suited best for either single system applications or far small, local developments.
ii) Remote Data Objects
RDO is an Object – Oriented data access interface to ODBC, combined with easy-to-use style of DAO, providing an interface that exposes virtually all of ODBC’s low level power and flexibility. RDO is limited, though, in that it doesn’t access Jet or ISAM databases very well, and that it can access relational databases only through existing ODBC drivers. However, RDO has proven to be the interface of choice for a large number of SQL Server, Oracle and other large relational database developers.
iii) ActiveX Data Objects
ADO Data Control
(i) To add data control to the toolbox go to components and select Microsoft ADO data control 6.0 (OLE DB)
(ii) Right – Click the ADO data control and choose ADO properties to display the property page of Ado
(iii) In the general tab select the option use Connecting String and choose build, which displays the data link properties box
(iv) In the Data Link properties list select the Microsoft Jet 3.51 OLE DB Provider, choose Next to more to the Connection tab. Select the mdb database
(v) Click the Test Connection button to test the connection. If the connection is successful it displays a success massage. Finally choose OK.
(vi) Click the Recordsource tab of the property page and select command Typeas 2_adcmd Table. Select the Table from the Table or Procedure name list.
Example 24.2 – Using ADO and Data Grid
(i) The Interface
(ii) Connect the mdb file
Create mdb file – E:\ADO.mdb
Table name – ADO
Field List
Name Type Size
name Text 50
no Long 4
sub1 Long 4
sub2 Long 4
total Long 4
average Single 4
Connect Adodc Tool à Properties
Connection String à Use Connection String à Build à
Microsoft Jet 3.51 OLE DB Provider à
Select Database name à OK
Record Source à Command Type (2 – adCmdTable) à
Table or Stored Procedure Name à ADO
Connect Text Boxes à Properties
Data Source - Adodc1
Data Field à name (for Text1)
Data Field à no (for Text2)
…………………..
…………………..
Data Field à class (for Text8)
Connect Data Grid Tool à Data Source – Adodc1
(iii) The Codes
Private Sub Command1_Click()
Dim rn As Integer
If Command1.Caption = "Add" Then
If Adodc1.Recordset.RecordCount = 0 Then
rn = 100
Else
rn = Adodc1.Recordset!no + 1
End If
Adodc1.Recordset.AddNew
Text1.SetFocus
Text2 = rn
Command1.Caption = "Update"
Else
Adodc1.Recordset.Update
Command1.Caption = "Add"
End If
End Sub
Private Sub Command2_Click()
Dim mg As String
mg = MsgBox("Are you sure?", vbYesNo + vbQuestion, "Delete")
If mg = vbYes Then
Adodc1.Recordset.Delete
Adodc1.Recordset.MoveNext
If Adodc1.Recordset.EOF Then
MsgBox "This is the last Record", vbOKOnly, "Microsoft Visual Basic"
Adodc1.Recordset.MovePrevious
End If
End If
End Sub
Private Sub Command3_Click()
Adodc1.Recordset.MoveNext
If Adodc1.Recordset.EOF Then
MsgBox "Last Record"
Adodc1.Recordset.MoveLast
End If
End Sub
Private Sub Command4_Click()
Adodc1.Recordset.MovePrevious
If Adodc1.Recordset.BOF Then
MsgBox "First Record"
Adodc1.Recordset.MoveFirst
End If
End Sub
Private Sub Command5_Click()
Adodc1.Recordset.MoveLast
End Sub
Private Sub Command6_Click()
Adodc1.Recordset.MoveFirst
End Sub
Private Sub Command7_Click()
If Command7.Caption = "Edit" Then
Adodc1.Recordset.Edit
Text1.SetFocus
Command7.Caption = "Update"
Else
Adodc1.Recordset.Update
Command7.Caption = "Edit"
End If
End Sub
Private Sub Command8_Click()
End
End Sub
Private Sub text4_change()
Text5 = Val(Text3) + Val(Text4)
Text6 = Val(Text5) / 2
End Sub
(iv) Output
Example 24.3 – Using DAO Control – Simple Program
mdb file – D:\example.mdb
Table name – example
Field List
Name Type Size
name Text 50
place Text 50
i) The Codes
Dim db As Database
Dim rs As Recordset
Private Sub Command1_Click()
If Command1.Caption = "Add" Then
rs.AddNew
Text1 = ""
Text2 = ""
Text1.SetFocus
Command1.Caption = "Update"
Else
rs("name") = Text1
rs("place") = Text2
rs.Update
Command1.Caption = "Add"
End If
End Sub
Private Sub Command2_Click()
End
End Sub
Private Sub Form_Load()
Set db = OpenDatabase("D:\example.mdb")
Set rs = db.OpenRecordset("example")
End Sub
ii) Output
Example 24.4 – Using DAO Control
1. The Interface
mdb file – D:\DAO.mdb
Table name – Product
Field List
Name Type Size
ProductName Text 50
ProductID Long 4
Price Long 4
Quantity Long 4
Amount Single 4
2. Select DAO Control
à Project Menu àReferences à
Microsoft DAO 3.51 Library
3. The Codes
Dim db As Database
Dim rs As Recordset
Private Sub Command1_Click()
If Command1.Caption = "Add" Then
rs.AddNew
Text1 = ""
Text2 = ""
Text3 = ""
Text4 = ""
Text5 = ""
Text1.SetFocus
Command1.Caption = "Update"
Else
rs("ProductName") = Text1
rs("ProductID") = Text2
rs("Price") = Val(Text3)
rs("Quantity") = Val(Text4)
rs("Amount") = Val(Text5)
rs.Update
Command1.Caption = "Add"
End If
End Sub
Private Sub Command2_Click()
If Command2.Caption = "Edit" Then
rs.Edit
Text1.SetFocus
Command2.Caption = "Update"
Else
rs("ProductName") = Text1
rs("ProductID") = Text2
rs("Price") = Val(Text3)
rs("Quantity") = Val(Text4)
rs("Amount") = Val(Text5)
rs.Update
Command2.Caption = "Edit"
End If
End Sub
Private Sub Command3_Click()
Dim pn As Long
pn = InputBox("Enter the Product ID")
rs.MoveFirst
While Not rs.EOF
If pn = rs.Fields("Product ID") Then
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
Exit Sub
Else
rs.MoveNext
If rs.EOF Then
MsgBox "Product ID not found"
End If
End If
Wend
End Sub
Private Sub Command4_Click()
rs.Delete
MsgBox "This record Deleted"
rs.MoveNext
If rs.EOF Then
rs.MovePrevious
End If
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
End Sub
Private Sub Command5_Click()
rs.MoveFirst
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
End Sub
Private Sub Command5_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Command5.ToolTipText = "Move First"
End Sub
Private Sub Command6_Click()
If rs.RecordCount > 0 Then
rs.MoveNext
If rs.EOF Then
rs.MovePrevious
MsgBox "This is Last Record"
End If
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
End If
End Sub
Private Sub Command6_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Command6.ToolTipText = "Move Next"
End Sub
Private Sub Command7_Click()
If rs.RecordCount > 0 Then
rs.MovePrevious
If rs.BOF Then
rs.MoveNext
MsgBox "This is First Record"
End If
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
End If
End Sub
Private Sub Command7_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Command7.ToolTipText = "Move Previous"
End Sub
Private Sub Command8_Click()
rs.MoveLast
Text1 = rs("ProductName")
Text2 = rs("ProductID")
Text3 = rs("Price")
Text4 = rs("Quantity")
Text5 = rs("Amount")
End Sub
Private Sub Command8_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Command8.ToolTipText = "Move Last"
End Sub
Private Sub Command9_Click()
End
End Sub
Private Sub Form_Load()
Set db = OpenDatabase("D:\DAO.mdb")
Set rs = db.OpenRecordset("Product")
End Sub
Private Sub Text4_LostFocus()
Text5 = Val(Text3) * Val(Text4)
End Sub
