Login | Register

Building a simple login page with Flash MX, ASP and Microsoft Access

How do you connect Flash to a database? This tutorial by David Vogeleer will teach you the basics by creating a simple login system using Flash, ASP and Microsofts access database.

Written by David Vogeleer First, I want thank all the people that emailed me with ideas for tutorials, and I have quite a few in line for you, but one of the most asked for tutorials was tying Flash to a database.

The files needed for this tutorial can be downloaded here

Anyone that has ever built in a little dynamic system to their Flash pages using outside text files or XML files, knows how cool it is to be able to update a simple text file compared to opening the entire Flash file, dig down to find what you need to update, update it, and having to re-upload that, especially if your clients don't know a lot about Flash. Tying into a database is the next logical step.

But, before you start creating scripts to interact with a database, you need to understand the Client/Server model. I'm sure you have heard that buzz phrase whenever you talk about the Internet, that's because the Internet runs on it, even if you don't understand it. What happens is that the client (anyone attempting to go to a basic html page through a browser) sends a request to the server for that html page. In return, the server will send the html page back to the client (person with the browser) and the browser renders the html then displays the web page. It sounds fairly simple, and it really is, this is how the Internet runs.

image

Tying into a database is slightly more complicated. This is because you cannot simply request information from a database through a server, you have to use middleware. Think of middleware as a translator between the client and the database, because that is exactly what it is. What will happen is you make a request of information, you send the request to the middleware which translates the question into a question the database would understand and then goes and gets the answer, then sends it back to you. Something like the following image:

image

Don't get me wrong, the above image is a very simple way of showing the relationship between the client, middleware, and the database for this tutorial, but entire books have been written on this relationship and how the whole process works, I just wanted to show the basic idea before we jump into coding.

Now our database of choice for this tutorial will be a Microsoft Access 2000 .mdb database file. Now, there are plenty of other databases out there, but Access is pretty popular, and easy to use. Most people have Office 2000, and Access is a part of that. But if you don't have it, its not a huge deal because all the files for this tutorial can be downloaded, so you could simply throw the database file on a web server, and most Windows based web servers have access, so it will run fine when we test it. Now it must be a Windows based server, so make sure that's what you are using.

The access file will be called login.mdb and it will contain one table called loginTable with 2 columns called username & password, they both will be text, and there will be no "Primary Key" for this example. (Primary key signifies a column that will always have unique information in each row) And in the database we will have 3 users: admin, user, client with these passwords: orange, apple, grape. When its done, it will look like the following image.

imageNow we have the database, we can close out of access, we won't need it anymore for this tutorial.

Now we write the middleware. For this tutorial, the language for the middleware will be ASP (Active Server Pages). There are other middlewares, such as PHP, ColdFusion, ASP.NET and an assortment of others, but ASP is a good start, and is supported on windows servers. Now with that said, ASP isn't really a language as much as it is a trigger for a language. What I mean by that is when a server gets a request for a .asp page, it knows that before it sends anything back to the client, it must run something within that page, so ASP is not only a trigger, but also a holder for server-side scripting languages. Now, we will be using Vbscript (similar to Visual Basic) as our language of choice in our ASP pages, but ASP also supports Jscript (like JavaScript), Perlscript (similar to Perl) and Python. I chose Vbscript because there is a wealth of information on the web about ASP pages, and the majority of them are written in Vbscript, so it will make it easier for you to find more information pertaining to it.

Before we jump into the code that does stuff, there are a couple of rules to ASP pages. First, all server-side scripting (the code that the server runs before sending information back to the client) must fall between these tags: <% and %>. Second, you must declare the server-side scripting language at the beginning so the server knows how to read it, and for us, that line looks like this:

<%@Language=Vbscript%>



Notice, the script did fall between the designated server-scripting tags, and when we declared the language, we used a "@" symbol and the word "Language", this is how to declare it.

Usually, the second line of code is this:

<%Option Explicit%>



And what that does is make sure that all of our variables we use are declared before we use them. Now, it is not a rule, but it is strongly recommended and is very useful when debugging.

Ok, now all that is out of the way, we can start doing things within our ASP page. So open a text editor (notepad is fine) and save the file as login.asp before you even start writing the code. Once that's done, place these two lines at the top:

<%@Language=Vbscript%>
<%Option Explicit%>



These are the lines we have already discussed on their usage.

Then use an opening server-script tag:

<%



This is where the guts begin. We first want to grab the information that we will be sending from Flash to use with our database, to do so, we must first declare the variables we will store them in. We do this with the Dim keyword like this:

Dim inUserName, inPassword



We can declare multiple variables at the same time simply by separating them with commas. Now they are created, lets store the information coming in using the Request object like this:

inUserName = Trim(Request("username"))
inPassword = Trim( Request("password"))



What is happening in the above code is that we are setting our two variables to two variables that are being sent in from Flash through the Request object. The two variables that have been sent are "username" and "password". (Don't worry if you don't understand how these variables are getting here, we will go over it when we get to the Flash part) And we use the Trim method to get rid of any spaces that might be at the end.

Now we have the information we need, lets compare it to the database, but before we do the comparing, we have to open a connection to the database. So lets look at the code, and then go over it.

Dim myConnection
Set myConnection=Server.CreateObject("ADODB.Connection")
myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("login.mdb")
myConnection.Open



The first thing we did was to create the variable to hold our Server Connection object using the Dim keyword like before.

The next line, we set our variable to the new Server Connection object by creating one using the CreateObject method. Notice, we used the keyword Set in the beginning of the line, this is necessary.

The next line, we set the ConnectionString to our database. (What that really does is set the driver for the type of database we are using, "*.mdb", and then sets the path for the server using the Server.MapPath() method. We use that method because in the ConnectionString, the server requires a complete path, not just a sub-directory path, and using Server.MapPath(), it will create the complete path for us.

And the last line of that section, we open the connection using the Open method.

Ok, we now have a connection to the database, but we're not done yet, because now we need to pull only certain information out of the database, because databases may have several different tables, we need to narrow it down a bit with the Recordset object. This object is used to basically hold data from the database in our connection, so lets look at the next section of code. And we will narrow it down even further by using SQL. SQL is the language databases understand, I'm not going to go into great detail about it here, but there is a list of resources for more information about it, and ASP at the end of the article.

So lets build our SQL statement that we will use to open the Recordset object.

Dim loginSQL
LoginSQL = "SELECT * FROM loginTable WHERE userName = '" & inUserName & "'"



As before, the first thing I did was to create the variable to hold our SQL statement. Then I set the variable, loginSQL, to the SQL statement. About the SQL, it is in quotes as a string, and each parameter is symbolized by being all in CAPS (this isn't necessary, but its good practice for readability). The first part, SELECT, tells the server which columns in the table to grab, and they are separated by commas if there are more than one, but this time we use the "*" to state that we want all columns in the table to be given to the Recordset object. The next parameter is FROM, this holds the name of the table to pull the data from. The third parameter is the conditional part of the statement, the WHERE parameter. In this instance, we are making a condition of the row that contains the username field that is equal to the inUserName variable which is the user name sent by Flash. Because inUserName is a variable, we must place it between single quote marks, and when combining strings, ASP uses the "&". And notice, that the single quote marks that encapsulate the variable are held in between double quotes.

Now we have the SQL statement we want to open the Recordset object with, so lets go ahead and open it. Just like before, you need to declare the variable to hold the new object, and then set it using the Set keyword. Lets take a look:

Dim myRS
Set myRS=Server.CreateObject("ADODB.Recordset") myRS.Open loginSQL, myConnection



After we declare the variable and set it, we use the Open method with 2 parameters, the first is the SQL statement we want use, and the second is the Connection object we have already created and opened.

Ok, once all of that happens we now have a Recordset object hopefully with data, but just incase, lets check.

We will use a standard if conditional statement to first test to see if any names matched the inUserName variable sent by Flash. Then we will use another if statement to see if the password in that row matches the inPassword variable sent by Flash. When we are done, we will send the data back to Flash.

With conditional statements in ASP (with VBScript) the condition must be followed by the keyword Then to state that the following code is to happen if the conditional evaluates to true. Lets take a look:

Dim mainMessage
If myRS.EOF Then
      mainMessage="userInfo=false"



See how we use the keyword Then like I had stated. But the condition itself is probably one your not familiar with. The Recordset object has many properties once it is opened, and one of them is the EOF property that means it is at the end of the Recordset. Therefore, if it is true, there is nothing in the Recordset, and we set a variable we create to a string which will be sent to flash.

If there is something in the Recordset, we want to check if the password matches the one that Flash sent using another conditional. But to get to the next conditional, we use the keyword Else to signify that the conditional is not over yet, but the keyword Then is not necessary with this statement.

Else
    If(StrComp(myRS("password"), inPassword, vbTextCompare) =0) Then 
     mainMessage="userInfo=true"



So we go to the next step of our initial conditional statement by using Else, and then we ask another conditional, and this one is more complex than the first. We use the StrComp method to compare two strings to one another, and if the comparison is equal to 0, then they are equal to one another. Using the vbTextCompare parameter means that it is not case sensitive. So now we are making sure that not only is the username we sent in the database, but now we are also making sure that the passwords match up as well. And if they are both accurate, we set the variable we create earlier, mainMessage, to a string which we will use at the end of the Asp page.

But what if the use is found, but the password is not the same? We need another Else condition after the password comparison, and then we need to end the conditionals.

     Else
       mainMessage = "userInfo=false"
     End If
   End If



So if the user is found, but the password is not correct, it sets the variable to a string that has the userInfo set to false. Then we end the conditionals by writing End If, so the server knows that that particular conditional is over.

Now comes clean up. Because we opened not only the Connection object, but also the Recordset object, they both need to be closed, and then set to the keyword Nothing. Even though in this particular ASP page, it is not that big of a deal if they are left open, but when you get into more complex database integration like modifying, adding or deleting data, it is important to close the connection as soon as you are done with it, incase it is locked so no-one else can use it while the script is running.

myRS.Close
Set myRS=Nothing
myConnection.Close
Set myConnection=Nothing



And notice we closed them in the reverse order we opened them because the Connection object contains the Recordset object.

And finally we send the data to flash using the Response object with the Write method. And then we close the ASP page with the closing server-scripting tag.

Response.Write(mainMessage)



We send the variable to Flash as a string containing a data pair. If you haven't used data pairs before, what it means is that there is a variable followed by an equal sign that is then followed by the data in the variable. If multiple data pairs are present, we use an "&" to separate them. (Keep that in mind if you are trying to send a string that does contain "&" in it, it will mess up your data pairs, and you will get weird errors)

Your final code should look like this:

<%@Language=Vbscript%>
 <%Option Explicit%>
 <%
   Dim inUserName, inPassword
   inUserName =Trim( Request("username"))
   inPassword = Trim(Request("password"))
 
   Dim myConnection
   Set myConnection=Server.CreateObject("ADODB.Connection")
     myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("login.mdb")
     myConnection.Open
   Dim loginSQL
     LoginSQL = "SELECT * FROM loginTable WHERE userName = '" & inUserName & "'" 
   Dim myRS
     Set myRS=Server.CreateObject("ADODB.Recordset")
       myRS.Open loginSQL, myConnection
   Dim mainMessage
     If myRS.EOF Then
       mainMessage="userInfo=false"
     Else
       If(StrComp(myRS("password"), inPassword, vbTextCompare) =0) Then 
         mainMessage="userInfo=true"
       Else
         mainMessage = "userInfo=false"
       End If
     End If
   myRS.Close
 Set myRS=Nothing
 myConnection.Close
 Set myConnection=Nothing
   Response.Write(mainMessage)
 %>



That's it! Now all we have left is the actual flash file, so lets go to that.

We will create a new movie (the dimensions really don't matter as long as our stuff fits). Create 3 layers, one (the top one) called "actions", the next one called "text" and the bottom one called "components". We will put the stuff on the stage first, and then do the code last. In the text layer, we need two input fields that both have border/background enabled, make sure they are both set to input. Give the top one an instance name of "login_txt" and make sure its set for single line. Give the bottom one the instance name of "password_txt" and set it to password (this means it will make little star symbols instead of text characters on the screen). And you can make labels for them so the user knows which field is which. Then on the components layer, drag an instance of the push button component onto the stage and place it under the password_txt text field. Set the label for this push button to "Login" and set the Click Handler to "onLogin". When that's all done, it should like the following image.

imageNow we got the stuff on the stage ready, go to the frame in the actions layer, and open up the actions panel.

The first thing we are going to code is the LoadVars object. It's a new object in Flash MX, and just to give a little background on it; Back in Flash 5, when you wanted to load outside data into Flash, you had to load into a movie clip or the _root (which itself is a movie clip). This had its problems, namely there would be a blank movie clip often times just sitting on the timeline somewhere that was strictly for holding data, plus it had to be created manually, but in Flash MX you can now create empty movie clips on the fly from code using the createEmptyMovieClip method. But again, there are still problems associated with doing this, one when you create the movie clip, you also create all the "stuff" that comes with a new movie clip which will take up space, plus you had to access the data through the movie clip instead of being able to hold the information in an object which could then have methods to massage the data from within it. Now steps in the LoadVars object, this object was made with one purpose in mind, to send and load data. And because it is an object, it is easy to create prototype methods to work the data from within the object. So lets go ahead and create one, as well as what should happen when it receives data.

// first we create the object
 login_lv = new LoadVars();
 
 //now we create the function that will happen when this object receives data
 login_lv.onLoad=function(){
 
 //check to see if the users info is correct
   if(this.userInfo == "true"){
     trace("Welcome");
   }else{
 //if the users info was not correct, trace an error, and clean the text fields
     trace("Invalid data");
     login_txt.text="";
     password_txt.text="";
   }
 }



Now we have that set, lets create the function that will happen when the user tries to login, and we will add some client side validation to make sure the user actually did put information in both text fields, and the password is at least a certain length.

//the function for the login button
 onLogin=function(){
 //an error will occur if the user didn't enter a login name
   if(login_txt.text.length<1){
     trace("please provide a login name");
 //an error will occur if the password is not the right length
   }else if(password_txt.text.length<5){
     trace("invalid password");
   //and clean out the password
     password_txt.text="";
   }else{
   //this is if everything is fine to send
 
   //we will store the user information as properties of the LoadVars object
     login_lv.username=login_txt.text;
     login_lv.password=password_txt.text;
   //now we send the data, and wait to recieve something back
     login_lv.sendAndLoad("yourWebPage.com/login.asp", login_lv, "POST");
   }
 }



Now make sure that this file, the database and the ASP page are all sitting in the same directory on your web server, and your ready to go and start testing. But remember, if the .swf file is on the server, you will need to do something else instead of tracing to know it worked. But, if you put the ASP and the database up, you can test the .swf locally and it will work. Don't forget, all these pages are available for download.

Although this was a simple login page, you can see how much thought and planning has to go into it, and this is just the start, for more information on ASP and SQL with ASP, try these links:

www.aspguys.com
www.aspalliance.com

 

 

Next tutorial:
FlashCom micro-tutorial

Previous tutorial:
Introduction to components

Get new stories first

Click to follow us on Twitter!

 

Comments


Posted by oxy on 01/19 at 08:57 PM

Don’t forget, all these pages are available for download???????????????? Where???????????????????????


Posted by Jens C Brynildsen on 01/21 at 01:29 PM

Hi oxy,
The link is at the start of the article:
http://www.flashmagazine.com/articlefiles/login.zip

Note that this is a rather old tutorial, so your mileage may vary ;)

J

Submit a comment

Only registered members can comment. Click here to login or here to register