Cambridge Technical Introductory Diploma in IT

(Unit 9 - LO3) Making a log in page in Mediator

Be able to implement and test products (P4, P5, M3)

Making a log in page in Mediator

There are many parts to this process, the pages in mediator, the database and the connection script that links the two together. Take care with the process as you work your way through it.

The database

It is best to begin with the database as this is the easist part to make. Open Access and make a new database.

This will be the result, an empty table.

Switch to "Design View" by clicking on "View" and "Design View".

You will be asked to give the table a name: give it the name "users". (Rollover)

This is the result. The table is called "users" and it has a single field called "ID".

You are going to change this field to be "username" by typing "username" where it says "ID". (Rollover)

Do you notice that the data type for this field is "Autonumber"? Use the drop down to change this to "Text". (Rollover)

Next, add another field underneath the username. This is to be "password" and the data type is also text. It will pick text by itself most of the time.

One last check, make sure that there is a little key next to username...

... if there isn't then click on the big key and it will appear. This makes username a primary key which means it must be different for every user.

Switch back to datasheet view by clicking on "View" and then "Datasheet View". Save the table when asked.

Now you are in position to add a few users to the database. Give each a sensible username and password.

Here is my table of users ...

username password
JimJones Icon56
TimSmith Happy35
JennyWhite Pincush10n
TinaFey Cr34mTea

... and here is the list of users in the database.

4 is sufficient as it allows you to pick the first last and one in the middle that is not just thoe other one when you are testing later.

Finally save the database with a sensible name. I am going to call mine "pswd" as this is the name given to password fines in some remote systems. Important: make sure that you save the database into the same folder as the rest of your project.

Equally important is the fact that you cannot save it as an Access 2010 database as this has the file extension accdb that Mediator does not understand. It must be saved as an MDB file. First click on "File" and then "Save & Publish"

... then change the database type to be "Access 2002-2003 Database" ...

... and then press "Save as" once you have given the database a sensible name.

Making the connection string.

Here is the first complicated bit. You need to make a connection string that connects the database to the Mediator product that you are making. This is a text file thet tells Mediator lots of useful information.

Here is a text file that you can copy and paste into Notepad.

DRIVER=Microsoft Access Driver (*.mdb)
FIL=MS Access

You will have to make the name of the database in the last line match the name that you have given to your database. Everyting else is fixed. it does not change.

This is the connection string for my project. I have highlighted the name of the database that I have chosen. The file extension for Access is ".mdb". This will find the old version that you have made.

Save the file careully. Click "File" then "Save as" and then make sure that you change the file type. Notepad will always use the "txt" extension as it normally only deals with text files. This wold not work for your purpose. So use the drop down at the right hand end of "Save as type" ...

... and slect "All files". You will now see all the files in the folder. It is important that you use the filename "connection.dsn" for the name of the file.

You can see here in the view of the folder that the connection file has the DSN file extension. This is very important as the connection will not work otherwise.

This connection will be picked up by Mediator later in this page.


The next step is to set up the variables that Mediator is going to use to store the information that it extracts and delivers to the database. Choose the page tab and then click on "Variables" ...

... so that you can add the variables that are required.

Variable name Variable scope Variable type Initial value Reason
B_Login Global (this means it exists on every page of the product.) Boolean (True or False) False Stores the user's success in logging in. This is used for later to stop users from getting round the login system.
Password Global String (A collection of letters or numbers)   The password that the user types in to the login page.
UserID Global String   The userID that the user types in to the login page.
V_Password Global String   The password that the product gets from the database.
V_User_ID Global String   The userID that the product gets from the database.

Some of the variables have similar names as they have similar uses but it is the V_ at the front that tells you that this is to be used as part of the database connection. The B_Login has the initial value "False" so that the user cannot access any restricted page until the user has successfully logged in.

This is the list of variables filled in.

Save the product at this point.

Resources - Connection String

The next stage is to make the connection between the database and the product.

This image shows the resources screen for this product. You are going to add a new database connection, so select "Database" annd drag it onto the work area ...

You will now see the "Database Resource" dialogue box. Click on "File". (Rollover)

Next, click on the drop down to the right of the space where the filename will go. You will then see a list of connections that you can use. I have 2 but you should only have 1). Double click on the connection.

The database resource will now populate itself (we hope!) with all of the details required to connect the database to the product.

As there is only one table (users) the resource will select that as the tabe to open when the database is connected. If you have more than one table then you can change the default table if required.

You will also see the structure of the table users with the two fields, username and password. These are both VARCHAR fields a type of string data type. There should be green dots next to each field; weirdly, this means that they are not connected to a variable in the product.

Click once on the field that you are going to link, in this case "username" and then "Link to" ...

... and then fom the drop down choose the field that you want the product to bind to. (Rollover) In the case of the "username" it is " V_User_ID". I have used the V for all of the variables that come from the database so that I will know which is which when writing scripts.

You should now see the red dot next to username and it will show that it is connected to V_User_ID

Repeat this process to connect "password" to "V_Password"

The next to last thing to do is to rename the database resource as something sensible, I have chosen "Connection_String". You may wish to do the same.

Finally copy the connection string to every page. USe CTRL + C to copy and CTRL + V to paste. If you make a new page you will have to add the string to that as well.

Basic page structure

The product that you are going to make will have many more pages than this, I am just going to show you how to make the pages that you need for the log in process.

Firstly you will need a page that asks the user to log in, then you will need two other pages, one for a successful log in and one for a failed attempt. You will also need a page for a user to register if that is to be part of your system. So 4 pages altogether. The image below shows the 4 pages (and the bottom master page which stores some important database information as well as any layout or graphic style). Any other content would follow these pages.

The important thing to note is the names of the objects on the page. From the bottom:

Title The title of the page in large lettering (in my case) This could be a headline or text object.
Text_Rubric This is the information ot the user (rubric) that is displayed on the page.
Text_Username This is the word "Username" that is placed next to the box where the user will type their username.
Text_Password This is the word "Password" that is placed next to the box where the user will type their password.
I_UserID This is the "Input box" that will take the user's username.
I_Password This is the "Input box" that will take the user's password.
Submit_Button This is the submit button that initiates the login process
Register_Button This is a button that takes the user to the registration page.

I have included the object type in the name of the object so that I can tell the difference between the username from the database V_User_ID and the username input box I_UserID and so forth.

This is the layout of the content on the first page, the log in page.

Clearly there is the heading at the top of the page, this is a "headline" but could be text. There are three sections of text, two buttons and two input boxes. The buttons and the input boxes will have code attached to them as will the page itself. You will obviously style the page the way you want to to match the rest of your product.

Adding code

First, adding the code to the page. Right click on the pale blue space that is not your page and then select "Events" or press "F9"

Drag the "On Page Ready" icon onto the work space ...

... then press the "Database" tab ... (Rollover)

Drag "DB Pointer" next to "On Page Ready" ...

... and you will see this dialogue box ...

Mediator should pick up the connection string that you have added to the page. (This will be the name of the resource that you added to the page not the name of the DSN file.) Press OK. This has now made the connection to the database and pointed the product to the first record in the database.

Next adding code to the username input box. Right click on the I_UserID input box to see the events dialogue.

Scroll down the list of events and drag "On Change" on to the work area.

Now change the tab to be "Programming" from what ever it was ... (Rollover)

... then drag the "Assign" action to the work area, select the variable "UserID" from the drop down list and add the text


to the assignment expression. I_UserID is the name of the input box and text is the property of the input box that you want to show.

This will store the text value of the input box (I_UserID) in the variable UserID. This is the finished event. You can see that the event hints at the code that you have written.

How can you test that this is working as it should?

This is not part of the finished product and will need to be removed or hidden when you have shown that the code works. The idea is to add a way of showing the value of the variable UserID as it is being typed into the input box.

Add another input box and call it Test_Username.

Now add another piece of code to the I_UserID input box by right clicking on the I_UserID input box and choosing "Events". From the "Multimedia" tab drag "Set Property" to the work area. (Rollover).

You will see this. Select the Test_Username object from the drop down list (Rollover)

Next, from the drop down list of properties, select "Text".

Change the property from "Constant value" to "Value as expression". (Rollover)

Click on the "fx" and select "Project variables"

... select "UserID" and press "OK" ...

You should have something like this. Press "OK".

Press "F6" to test the page and as you type in the username the same text should appear in the test_username box.

So it is possible to enter a username and check that it is working. You can click on the test input box and press "F3" to make it vanish if you'd like, but I will keep mine visible until it all works properly.

Repeat this whole process for the Password input box (I_Password). This is the code that you would need to write for the I_Password input box ...

... and this shows the password working.

Logging in

Finally we come to the tricky bit, the code to test that the user can log in. Right click on the submit button and select "Events" and drag "Mouse Click" into the work area.

Click on the tab "Database" and drag "DB SQL" to the work area. (Rollover).

You will be presented with the Database SQL dialogue box into which you will need to type the SQL code to extract the data from the database and store it into the variables in the product.

This is the code that you must type. You can copy and paste it from here. All of the quotes are really very necessary.

"SELECT * FROM Users WHERE Username LIKE '%"+ I_UserID.Text+"%' "

  • Users is the name of the table in the Accecss database.
  • Username is the key field of the Access database.

The last stage for this button code is to check that the username and password that the user entered actually matches the credentials stored in the database. This is done with an IF statement. Change to the "Programming" tab and drag "IF" to the work area ... (Rollover).

The IF statement always starts of by including the condition "True". This needs to be replace with (Rollover)

UserID=V_User_ID and Password=V_Password

This is checking that the username entered by the user (UserID) matches the username stored in the database (V_User_ID) and that the password entered by the user (Password) matches the password associated with the username stored in the database (V_Password).

IF this condition is true then we can send the user to the login success page and set the variable B_Login to be True. IF the condition is not met we ask the user to try again.

From the "Programming" tab drag "Assign" into the "Then" section.

From the drop down select the B_Login variable ...

... and type in the value "True".

Add a "Go to page" event for each eventuality (Rollover) and then test the whole thing.

I am relieved to be able to say that it worked first time. Woo Hoo, still got it!.

Using other data from the database

Now that you have values in your database that can be read by the product, you can add extra fields to the data table and then usse this data in your product.

I have added a field for "Firstname" to my database (in the same way that I added password)

I can now edit the connection to the database to include this field and connect it to a new variable in the product.

The new variable will be V_Firstname to follow the style that I have used so far.

Open the connection string again and add the link to the new variable. (Rollover.)

You will have to copy this new connection string to every page that you have made so far. This is why if you intend to make any further use of the database you need to plan the changes to make life easier for yourself.

The next thing to do is to introduce Firstname as a variable so that it can be used by the rest of the pages in the product. Add another assign instruction from the programming tab. It must be placed before the page change instruction otherwise the pages changes and the code is not executed.

Assign the variable "Firstname" to have the value of V_Firstname (the connected varaible from the database).

You can now use the variable Firstname in your code.