Cambridge Technical Introductory Diploma in IT

(Unit 6 - LO1)

Understand how applications are designed

 

Part - - -

LO4 Be able to present application solutions to meet client and user requirements - Connecting to a database

 

1. Create the database - EARD and everything ... BAck to the top of the page

Create the Access database in the same folder as your product.

Keep the structure simple, one table with a primary key and any other fields that you think are necessary. You can add additional fields later if you wish. (Last page visited, highest score and so forth...)

I have a simple, two field data table, with fields "UserID" and "Password".

Populate the table with test data - at least 3 distinct records. This becomes important when you test the functionality of the login box.

2. Make the connection string ... BAck to the top of the page

This is the really tricky bit and relies on making an ODBC connection to the database so that Mediator can pass data to and from the database. The ODBC connection string depends on the type of database driver used by the academy.

(If all fails, I will prepare and give to you the connection string for this network which you can then edit to match the circumstances of your product. (Change the database name and the path detail.))

Open the mediator document that requires a database connection.

Navigate to the first page where the database will be used.

Open the resources...

Drag a database object on to the active page.

The database resource dialogue will open by itself.

Click on the drop down selection next to the database name.

Choose "Machine data source".

Choose "New" and accept the warning.

Select "Next".

You will see the list of data sources available to the PC you are working on.

Data sources to ODBC (Open Database Connectivity) is a any database that conforms to a set of rules so that data is stored in a a similar manner. There is Access, Excel (which can be turned into a database - ask the doubles!) dBase FoxPro as well as SQL of all forms.

You need to select the Access 2010 driver, the one that has the file extension (accdb). The previous incarnation of Access had the file extension mdb.

Press "Next".

Press "Finish".

Give the connection string a name.

This is the name that the connection to the database will be known as to Mediator. It does not have to be the name of the database, although this may help.

I usually name the connection as "Connection String" and the name of the database so I know which database is being connected to the Mediator object that I am making.

Choose "Select" to select the database that you have just made.

You may have to browse to the right folder.

Any absolute file references can be edited later.

Make sure that you change the directory to be the location of the database.

You will see the database in the "Database name" window.

Press "OK" when the database has been located.

You have now completed the connection string for your database.

Press "OK" to finish.

Your newly created connection string is now part of the machine data sources known to ODBC. This may be specific you your exact PC in school, so if you change PC be prepared to re-create the connection string.

Select the connection string that you have made and press "OK".

All being well you will now see the fields of your database in the Mediator dialogue box.

At the moment there are green dots to the left of each field. I will explain more about this a little later.

Press "OK" and your connection string is complete.

At the moment it is called "Resource01" which is hardly descriptive, so again edit the name of the connection string resource to be more meaningful.

3. Deploy the connection string ...BAck to the top of the page

This database connection string needs to be added as a resource to every page that needs to access the database.

Simply copy and paste it into the resource section of each page where the data may be used. When in doubt make it every page.

As you can see I have made three pages:

  • Login - that asks the user for the login credentials and compares them to the values stored on the database.
  • Login_failed - which shows the message that the login process detected that the credentials were not correct.
  • Login_Success - which congratulates the user on logging on to the system

There is a reason for including the three options as separate pages.

The user needs to know that the credentials that they have provided are correct. If they are they progress to the secure content of the object, if not they need to know so that the can check that they are using the correct credentials when given the opportunity to log in again.

If you are going to make use of the database to store progress or scores or access arrangements or other settings such as colours (hint) then make sure that the connection string is deployed to each page that accesses the database either to write to or read from the data.

4. Create variables in Mediator ...BAck to the top of the page

This may sound weird but you will need two sets of variables in mediator, one to store the value in the database and one to store that valuate that you will compare with the value in the database.

I insist that you draw a table such as this to keep track of the various variables that you will meet in the coursse of constructing the system.

Variable

Mediator page, input object name

Mediator database

Database

User_ID

I_UserID

V_User_ID

UserID

Password

I_Password

V_Password

Password

The variable occurs in any one of four guises.

  • The variable created by data entry to the input object.
  • The input object name is the input object that the user will see on the screen. This can be filled by the database or by the user. For a login system it will be filled by the user.
  • The Mediator database variable is the variable extracted from the database (using the connection string that you have just made) and held in memory.
  • The database variable is the one actually in the Access database.

It is always tricky to keep track of which variable is which so the table really helps.

The variables "V_User_ID" and "V_Password" are both global, ie they exist on all pages.

In addition I will have a global Boolean variable called "B_Login" that notes whether the user has logged on. This will initially have the value "False", the user has not logged in.

The next step is to connect the connection string to the variables that we have made.

Go to the first page that makes use of the database. In my case it is the Login Page.

Open the resources and open the Connection string again.

You will note that there are green dots next to each of the fields. These are unconnected variables.

Click on one of the variables from the database.

Select "Link To" and from the drop down select the variable in the Mediator object that the database variable connects to. (If you have more than a few variables then this is why the table is needed.)

When each of the variables is successfully linked the dots turn red.

You can now see that the connection shows how each database variable is connected to the matching Mediator variable.

Make sure that this variable connection is made for every page requiring the connection string.

5. Create Input objects ...BAck to the top of the page

Create a pair of login boxes, a username box and a password box. Each is an input object.

One of the important things to note is that all of the objects on the page have sensible names so you know what input object links to what variable.

Again the table becomes essential.

The two boxes ready for the username and password are both input objects, called I_UserID and I_Password.

6. Use code to connect input objects to the database ...BAck to the top of the page

This is the really, really tricky bit and relies on accurate SQL code and my memory.

The first stage is to take the values typed in by the user and put them into variables and then compare the username/password combination from the page to all of the username/password cobinations in the database. If there is a match then the user is allowed to access the rest of the product, if not the user is sent to the Login_Failed page.

Add an event to I_UserID.

The trigger to the event is "On Change" that you will find at the bottom of the list.

Assign the variable UserId to have the text value of the input object I_UserID.

  1. Select the assingment event.
  2. Select the variable "UserID"
  3. The assignment expression is I_UserID.text

This means that every time the user types a key, the text value of the input object is stored in the variable UserID.

Repeat this for Password.

The assign event for Password is I_Password.text

The next stage is to test that the data is being captured correctly by the input boxes.

Duplicate the entire content of the login page on the login_success page.

Remove the events from the two input boxes (on the login success page).

Add an "On page ready" event to the login success page.

This time use the "Set property" event to set the text value of each input object to be the value of the variables as shown in the image.

Add a button to the login page to send the user to the login_success page and test to see that the input values in the login page "persist" (i.e. are still present) in the login_success page.

The login success page has the added comment (test) just to show that the test was successful. You will have to take my word for it but it was.

Now comes the really tricky bit.

The login page needs to search the database to find the username UserID (the variable created from the login box) and compare the matching password in the database with the password input by the user.

This event is added to the button. If the reuslt is successful the variable B_Login is set to True and the user is sent to the login_successful page, if not then the value of B_Login remains False and the user is sent to the Login_Failed page.

This will be built up a piece a ta time.