Simple Database

Name: Simple Database
Author: Paul Kinlan
Date: 7/28/98

Description: This database tutorial was created by Paul Kinlan. You can also download the sample project. Paul's contact information is at the end of the tutorial. Thanks Paul!
Controls needed: Listed Below
Level: All

A Simple Database.

This Tutorial concerns the simple features of Databases, During this tutorial I will look at some of the things that can be achieved with Databases and practical uses of Databases.

Here is a list of subjects that will be covered on the programming of Databases.

• Storing data as a Database,
• Retrieving data from a Database,
• Searching data contained in a Database;

What is a "DATABASE"?

A Database is a collection of records, that can be sorted, removed, searched etc.

A Database allows you to store multiple pieces of information in one file, instead of using several files for each piece of data. Normally a Database contains many fields of data. You can think of a field as a place in which you can hold information, it’s a lot like a variable but you load information from a file and only that variable can get that piece of information.

For example you are keeping information about your record collection, so you have a field called "ARTIST", a field called "RECORD" and a field called "TYPE", now when you put the information into the three fields they are stored in that order in the file. The information is also retrieved in that order when you wish to obtain the data. Storing the data in fields means that you can easily search data if you now what field to search. For example if you want just search by "ARTIST". Then instead of searching the whole file for a string that matches, you can search each "ARTIST" field and miss the other two fields by skipping there position in the file and going to straight to the next "ARTIST" field.

The advantages of Databases are that they are easy to search for specific items, you can add data really easily.

How A Database Works.

The way in which we will get our Database to work is very simple, first we will create template of the fields we are going to be using (ARTIST, RECORD, STYLE). This is done in Visual Basic by placing variables in a TYPE declaration.

This is done so that we can easily access all the variables for are Database fields and so that when we write to the file instead of doing:

ARTIST = trim (txtARTIST.text)

RECORD = trim (txtRECORD.text)

STYLE = trim (txtSTYLE.text)

Put #1, x, ARTIST

Put #1, x, RECORD

Put #1, x, STYLE

We can do:

Typename.ARTIST = trim (txtARTIST.text)

Typename.RECORD = trim (txtRECORD.text)

Typename.STYLE = trim (txtSTYLE.text)

Put #1, x, Typename

This way may seem longer but it’s faster because the is a lot less file access because we are only writing to the drive once when enter new data in stead of several times. As you can see if you have a lot more fields the second method becomes even better, also it is a lot easier to read and harder to get messed up with your variables because you explicitly know that any Typename variables are for read from textboxes and searching etc.

Adding Data

When adding data to a Database we call the data "Records" (please don’t confuse these with the text box txtRECORD or the variable called RECORD, as these are only specific to this project). When you add a Record you increase the record count by one and place the data at the end of the file.

x = Filelen (filetostoredata) / Len (Typename)

…

…

…

x = x + 1

Put #1, x, Typename

When you add a record the picture above would become one record larger, with three fields in that new record.

Searching for fields

When you want to search for an item in a database, you don’t want to search through every byte in the file, so to cut the amount of searching needed, you only look at the selected field in the current record. So for a database with only three fields and you only want to look at one of those fields, you would do a search like this:

Lastrecord = Filelen (filetostoredata) / Len (Typename)

x = 1

Do

Get #1, x, Typename

If trim (ucase (Typename.ARTIST))=trim (ucase (txtARTIST.text)) then

End if

x = x + 1

Loop while x <= Lastrecord

What the code above does is:

1. First you get the how many records there are in the Database file and initialise x as one, x is then the record number you currently want. (There is no record zero so that’s why it’s set to one).
2. Next set up a loop, to loop the number through each of the records.
3. When in the loop you retrieve the record numbered x.
4. Next because we set up a type we can get the information easily from the correct field in the record and then compare it to your search string, if a match is found you can do what ever you want with the result.
5. Lastly increment the current record number "x"

There are other ways in which to do this but this way you make sure that the whole file is searched and not one that exits when just one match is found, remember there may be more than one match found.

Example Database Project

The next section is a simple Database project for you to follow along with. It is based on what I said earlier about keeping your record collection ordered.

Planning

Here we will plan on what we want our Database to be able to do.

1.  
2. Store information on :

•  
• Artist (i.e. Beck ),
• Record Name (i.e. One Foot In The Grave ),
• Style (i.e. Indie );

1.  
2. Ability to :

•  
• Add new records,
• Search for Artist, Record, Style,
• Append incorrect data entered,
• Save records to file;

Layout

Here we will discuss the layout of the project.

1.  
2. We want Textboxes so we can enter data into the correct fields, Labels so we can see what fields we are entering data into. Command Buttons so we can select task to do.

Here is an example of what we need to do this project.

As you can see we have:

4 Command Buttons,

3 Text Boxes and

3 Labels

Now we shall rename the controls on the Form and add captions so they are easier to program with and so it has an user-friendlier interface.

The form should look something like this

To make reading and writing to our Database easy we will use a "RANDOM ACCESS" file, because when reading and writing you can just place in the record number and it will go to that position read to do the reading and writing. This is a lot easier than messing with byte position etc.

CODE

Now that we have the basic layout and design we can start to code some of the features in to the program:

First we will add in our type declaration so that we have the template for our Database.

Instructions:

1. 1. Click "Project" from the VB menu,
   2. Click "Add Module",
   3. Now in the new module type.

Now we can add code to our OnLoad procedure on the form, but first we must add some variables under the option explicit declaration so that they are available to all procedures and functions.

Option explicit

Dim Lastrecord As Long

Dim Currentrecord As Long

Dim RecordDB As RECDAT

The fourth line is the most important here as it declares a variable RecordDB as a type of RECDAT this means to access a variable inside the RECDAT type we can just do RecordDB.varaible.

Now we can add code in to the Form_Load procedure:

Private Sub Form_Load ()

End Sub

Now that you have entered the code above, I will tell you what it does. After declaring the variables we are to use in this procedure, you will see that we find the size of the type this is done so that we can find how many records there currently are in the file so that we know what the lastrecord is. Also Visual Basic uses this length when opening a file as RANDOM ACCESS, this is so Visual Basic knows where about to jump to in the file when you ask for a record position.

The next part of the coding which we next need to do is to add functionality to the command buttons.

First we will add code to the txtADD command button.

Private Sub cmADD_Click ()

RecordDB.ARTIST = Trim (txtARTIST.text)

RecordDB.RECORD = Trim (txtRECORD.text)

RecordDB.STYLE = Trim (txtSTYLE.text)

Put #1, Lastrecord, RecordDB

Lastrecord = Lastrecord + 1

End Sub

Ok now that you have seen the code for the ADD button, I will explain what the code actually does:

First we obtain the data from the text boxes and place them in the template "RecordDB". Once this is done we are ready to write to the file, this is done by using the "PUT" statement, what the put statement does is it goes to the position in the file you selected and then writes the contents of the variable to the disk. Finally we increment the lastrecord by one and change the "forms" caption to show what record we are on.

Now that we are able to add new items to the Database, you would at least think we should be at least able to navigate through them, so now I will present you with the code that allows you to move forward and backwards through each record in the file.

Private Sub cmBACK_Click()

Currentrecord = Currentrecord - 1

If Currentrecord = 0 Then

Currentrecord = Lastrecord - 1

End If

Get #1, Currentrecord, RecordDB

txtARTIST.text = RecordDB.ARTIST

txtRECORD.text = RecordDB.RECORD

txtSTYLE.text = RecordDB.STYLE

fmDataBase.Caption = "Record Collection" + Space(1) & Currentrecord & "/" & Lastrecord - 1

End Sub

Private Sub cmFOR_Click()

Currentrecord = Currentrecord + 1

If Currentrecord > filelen (App.Path + "\" + "Records.rec") \ Len(RecordDB) Then

Currentrecord = 1

End If

Get #1, Currentrecord, RecordDB

txtARTIST.text = RecordDB.ARTIST

txtRECORD.text = RecordDB.RECORD

txtSTYLE.text = RecordDB.STYLE

fmDataBase.Caption = "Record Collection" + Space(1) & Currentrecord & "/" & Lastrecord - 1

End Sub

What the above code does is increment/decrement the "Currentrecord" by +/- 1 so that you get the next record in the file. Also before it reads the data it checks to see if the Currentrecord is not zero because if this was the case the a error would be reported because you cant have a zero record. Also if you go past the last record you won’t get an error but you won’t be able to read any data because there will be none there so you loop back to the begging of the file.

Now for the last part of the project, the "Search" method. In this section I will discuss a simple way of searching Databases, there are more complex search methods, but that goes beyond the scope of this tutorial.

What we can do is to scan through each of the records and search the specified field for the string we want. Here is how we can accomplish this objective:

1. 1. Set up a loop to access each record in the file,
   2. Enter the fields to search,
   3. Enter the search string,
   4. Convert current field and search string to uppercase, so that any case differences are ridden of.
   5. Once a matching record is found, display the information.

Ok, now lets add a new form to the project and two command buttons, one list box, three option buttons and three text boxes. The form should now look something like this.

With all the control’s in place we now have to rename them so that they are easier to handle when we do the coding.

Once all the names and captions have been changed we can now add the code to the final part of the Database.

First we should add code to the Search Button on fmDataBase form:

Private Sub cmSearch_Click ()

End Sub

Also we must also set the lastrecord again so we can place this in FORM_ONLOAD() procedure in the fmSEARCH form.

LastRecord = FileLen (App.Path + "\" + "Records.rec") \

Next we will add code to the option explicit section in the General Declarations of the fmSEARCH form.

Option Explicit

Dim LastRecord As Long

Dim RecordDB As RECDAT

Dim Which As Integer

The last variable is so we know which search to do once we have selected a field to search.

The code for the option buttons is as follows:

Private Sub opARTIST_Click()

txtARTIST.Enabled = True

txtRECORD.Enabled = False

txtSTYLE.Enabled = False

Which = 1

End Sub

Private Sub opRECORD_Click()

txtARTIST.Enabled = False

txtRECORD.Enabled = True

txtSTYLE.Enabled = False

Which = 2

End Sub

Private Sub opSTYLE_Click()

txtARTIST.Enabled = False

txtRECORD.Enabled = False

txtSTYLE.Enabled = True

Which = 3

End Sub

All that the above code does is when an option button has been selected, it stops you being able to enter any text in to any other text box. But the which variable we will come to later when I show you the search code, all it does is define which field to search in.

Now here is the code for the search.

Private Sub cmSearch_Click()

Dim i As Integer

If Which < 1 Or Which > 3 Then

Exit Sub

End If

lstMATCHES.Clear

i = 1

Do

Get #1, i, RecordDB

If Which = 1 Then

If Trim(UCase(txtARTIST.text)) =

lstMATCHES.AddItem Trim(RecordDB.ARTIST) _

+ Space(2) + Trim(RecordDB.RECORD) _

+ Space(2) + Trim(RecordDB.STYLE)

End If

ElseIf Which = 2 Then

If Trim(UCase(txtRECORD.text)) =

+ Space(2) + Trim(RecordDB.RECORD) _

+ Space(2) + Trim(RecordDB.STYLE)

ElseIf Which = 3 Then

+ Space(2) + Trim(RecordDB.RECORD) _

+ Space(2) + Trim(RecordDB.STYLE)

End If

End If

DoEvents

i = i + 1

Loop While i <= LastRecord

End Sub

What the above code does is it gets the data from the Database file and stores it in the RecordDB template. It then converts the data to uppercase and takes away any leading or trailing space, then it takes the text from the textbox which is selected and trims it down and converts it uppercase. Then the textbox data is compared to the data in the field from the currently selected record. If a match is found the code posts the results in the list box so you can see that it found what you where searching for.

Some final things to do are unload the search form when you click close.

Private Sub cmCLOSE_Click()

End Sub

Also we have to close the file, so no errors occur when we reopen the file. So in the terminate procedure of the fmDataBase form enter the code.

Private Sub Form_Unload(Cancel As Integer)

Close #1

End Sub

Another thing that needs to be done is to stop you over entering text into the text boxes, as this will cause an error when you come to write or search the Database. So on the FORM_LOAD procedure of each form we need to add a maxlen to each of the textboxes.

txtARTIST.Maxlen = Len (RecordDB.ARTIST)

txtRECORD.Maxlen = Len (RecordDB.RECORD)

txtSTYLE.Maxlen = Len (RecordDB.STYLE)

This code above stops you from over entering text in to the text boxes.

The Database we just made is not very useful but it gives you a idea of what can be achieved with Database. As a exercise why don’t, you try and make a booking keeping database, where you can keep records of people who have taken out book’s etc, or expand this example further by adding extra features like a CD collection or Games collection, and prices, dates you paid and bought them.

This is the end of this tutorial on Simple Database’s, I hope you enjoyed it and found it useful, and the topics covered in this tutorial are just a small amount of what Databases are all about. If you have any problems or questions just email: paul@pcbware.co.uk or visit www.pcbware.co.uk and I will be glad to help you.

Paul Kinlan