Accessing Access

We have gone over how to load data in from ASP pages as well as how to send data to them with results being returned. This section is going to build on what we have covered so far and add database integration into the mix by means of an Access database.

Integrating Flash projects with databases is a great way to separate content from design. Because the content is stored in a very raw form of pure data, it can be placed into more than one setting. This is how many sites offer different versions of their site, because all they have to do is build the different front-ends and then connect to the data source through ASP or another middleware language.

But before we start integrating with a database, we need to have one. We will use Microsoft's Access database format (.mdb). If you have Microsoft Office, you should have a copy of Access, but if you don't, it's not a big deal; you can grab the database from the book's website and upload it to your web server. The database itself holds close to 20 DVDs in a table called "dvds," as you can see in Figure 21.5. If you want to set up the database yourself, the table has the following fields:

• id This field is an AutoNumber type field, which means that as you add data to the database, this field will automatically be filled for you with an incrementing number. This field is also the unique key for our database, so the values will never duplicate.

• title This is the title of the movie.

• director This is the director of the movie.

• rating This is the "5-star" rating it received from Amazon.com at the time it was added to the database. It will be a number from 0 to 5.

• rated This is the MPAA rating the movie was given.

• type This is the type of a movie it is (Drama, Comedy, Action).

Figure 21.5. The data that is held in the dvds table in Access.

Figure 21.6. The Design view of the dvds table in Access.

TIP

It is not always necessary to have an id field in your tables with AutoNumber turned on, but it is good practice because you will not repeat a value in this field. However, you could possibly have an identical value in the other fields. For instance, if you had both the original version and the new version of the Manchurian Candidate, the easiest way to tell the difference between the two is the id field.

Now that the table is up on the server, we can go over how to connect to it in ASP.

To connect to a database with VBScript, you have to use the ADODB object to make the connection. This object enables you to connect, query, and return information from databases. With four lines of code like the following, you will create an instance of the ADODB.Connection object, create the ConnectionString, and finally open the connection:

[View full width]
 Dim myConnection   Set myConnection=Server.CreateObject("ADODB.Connection")   myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" &  Server.MapPath("movies.mdb")   myConnection.Open 

Notice that when we create the ConnectionString, we use a function called Server.MapPath. This is because when you pass the path of the database in the ConnectionString, you have to have the full path, but this function will create the full path for you.

The next step is to create the SQL string we are going to use to "talk" directly to the database. SQL syntax is basically a list of commands set up a certain way so that the database will understand the request. With SQL strings, you can get, add, edit, and delete the data in the database. Here are a couple examples of basic SQL strings with the keywords in bold:

 SELECT fieldName1, fieldName2 FROM tableName WHERE fieldName1 = value INSERT INTO tableName (fieldName1, fieldName1,) VALUES ('value1', 'value2') DELETE FROM tableName WHERE fieldName = value 

In the first one, the SQL string will get all the data from fieldName1 and fieldName2 where the value in fieldName1 matches value. The second one will create a new row in tableName and put value1 in fieldName1 and value2 in fieldName2. And the final one will delete all rows from tableName where the value in field fieldName is equal to value. SQL strings are difficult at first because they are unlike any code we have covered in this book, but the more you use them, the more familiar they will become.

TIP

In the first SQL string, we select two different fields to get data from. If you want all the fields, you can type them in one by one, or use the asterisk (*) to select all fields, like this:

 SELECT * FROM tableName WHERE fieldName1 = value 

Now every field will be returned from rows matching the condition.

After you have your SQL string, you would instantiate an ADODB.Recordset object to capture the data and then open it like this:

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

After that is run, you would have some code to handle all the information being returned in the recordset. After that, the recordset and connection would have to be closed, or errors could occur if the database is getting a lot of requests. The code for that would look something like this:

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

Notice that not only did we close both the recordset and the connection, but the references were set to Nothing as an extra precaution.

Those were just some of the basic pieces for interacting with a database, so now let's build a page to actually interact with the movies.mdb database.

The project we are building in the example is an application that can view all the DVDs in the database. Then the user can select a specific one to see all of that DVDs data as well as be able to search the titles for keywords.

The first ASP page to build is the one that will return the title and id of every DVD in the table. You will see why we need the id later. The page will look like this:

[View full width]
 <%@Language=VBScript%> <%Option Explicit%> <%     '''''''Make the connection     Dim myConnection         Set myConnection=Server.CreateObject("ADODB.Connection")         myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ="  & Server.MapPath("movies.mdb")         myConnection.Open     '''''''Create the SQL statement we need and set it to a variable     Dim getAllDVDS         getAllDVDS = "SELECT title, id FROM dvds"     '''''''Make the record set object     Dim myRS         Set myRS=Server.CreateObject("ADODB.Recordset")         myRS.Open getAllDVDS, myConnection     Dim allDVDs, ids, mainMessage         Do While Not (myRS.EOF)             allDVDs = allDVDs& myRS("title") & "~"             ids = ids & myRS("id") & "~"             myRS.MoveNext         Loop     '''''''Create the message we are sending to Flash         mainMessage = "dvds=" & allDVDs & "&ids=" & ids     '''''''Clean up...MUST HAVE!!!         myRS.Close         Set myRS=Nothing         myConnection.Close         Set myConnection=Nothing     '''''''Send that data to Flash         Response.Write(mainMessage) %> 

The top section does what it has done in the entire chapterit sets the language and the Option Explicit. After that, the connection to the database is opened. Then we create the recordset and open it with our SQL string. Next, a couple of variables we need are created, and then the recordset being returned is parsed with the data being thrown into the variables we just created, separated by "~". Then we set the mainMessage variable to the data we are sending back to Flash. Finally, the connection and recordset are closed, and the data is sent out. Save this page as getAllDVDs.asp, upload it to your server, and test it to see the information being returned. It should look like Figure 21.7.

Figure 21.7. This is the data being returned in its raw format, before Flash parses it.

Next up is the Flash part. This Flash file will be the one we use throughout the whole example; we will add new pieces to it as we go forward. Follow these steps to get started:

1.	Create a new Flash document and save it as dvdList.fla.
2.	Create a second layer, and call the bottom layer content and the top layer actions.
3.	In the Content layer, drag an instance of the List component onto the stage at coordinate x20, y50, and size it to 175x200. Also, give it an instance name of dvds_list.
4.	Now for some ActionScript. Select the first frame in the Actions layer, and place this code: function getAllDVDs():Void{ var getAll_lv:LoadVars = new LoadVars(); getAll_lv.onLoad = function (success){ if(success){ //get the data being returned var titles_array = this.dvds.split("~"); var ids_array = this.ids.split("~"); //temporary array to hold the data before it goes to the list var temp_array = new Array(); //because of the extra "~", there is an extra element //so we only need every element, but the last one var tempLength = titles_array.length - 1; var i = 0; while(i < tempLength){ temp_array.push({label: titles_array[i], data: ids_array[i]}); i++; } //set the list component dvds_list.dataProvider = temp_array; }else{ trace("An error occurred with the connection"); } } //get the data getAll_lv.load("http://MYSERVER/getAllDVDs.asp"); } //call the function getAllDVDs();

The preceding code does a lot. First, we create a function to get all the DVDs (this could have been done without it being in a function, but later you will see why a function was used). In the function, a new LoadVars object is created, and the onLoad event is set. When data is received back, it will go through it element by element and store the info in another array that eventually gets set to the dataProvider of the List component. At the end of the function, we load the ASP page with all the data. Then, after the function is complete, we call it to get all the DVDs. Test it out, and you should see something like Figure 21.8.

Figure 21.8. All the DVDs in the database are now in the List.

Next up is the ASP page that will receive some data from Flash and return all the information on a given DVD. The page will look like this:

[View full width]
 <%@Language=VBScript%> <%Option Explicit%> <%   ''the data coming from Flash   Dim dvdID   dvdID = Trim(Request("sentID"))   '''''''Make the connection   Dim myConnection    Set myConnection=Server.CreateObject("ADODB.Connection")    myConnection.ConnectionString="DRIVER= {Microsoft Access Driver (*.mdb)};" & "DBQ=" &  Server.MapPath("movies.mdb")    myConnection.Open   '''''''Create the SQL statement we need and set it to a variable   Dim getDVDInfo    getDVDInfo = "SELECT * FROM dvds WHERE ADODB.Recordset")    myRS.Open getDVDInfo, myConnection   Dim mainMessage    IF NOT(myRS.EOF)THEN      MainMessage = "title=" & myRS("title")      mainMessage = mainMessage & "&director=" & myRS("director")      mainMessage = mainMessage & "&rating=" & myRS("rating")      mainMessage = mainMessage & "&rated=" & myRS("rated")      mainMessage = mainMessage & "&type=" & myRS("type")    ELSE      mainMessage="error=none returned"    END IF '''''''Clean up...MUST HAVE!!!   myRS.Close   Set myRS=Nothing   myConnection.Close   Set myConnection=Nothing '''''''Send that data to Flash   Response.Write(mainMessage) %> 

There are a few differences between this ASP page and the last one. First, this page is accepting some data being sent to it from Flash (the id of the DVD, so now you see why we grabbed the ids in the first ASP page). Next, the SQL string is a little different; all the fields in the row matching the id are returned. We then check to make sure some data was returned and put it in mainMessage to be sent back to Flash. If no rows are returned, an error is sent back to Flash saying none was returned. And just like before, at the bottom we close the connection and recordset and send the data to Flash.

Save this file as getdVDInfo.asp and put it up on your server. If you want to see the results being returned, try this URL: http://MYSERVER/getDVDInfo.asp?sentID=1.

Now we'll go back to Flash to add a few fields and some more ActionScript.

1. Before we get to the ActionScript, we need to create a few text fields on the stage. They will all be dynamic text with the border turned on and on the Content layer. The instance names will be the following: title_txt, director_txt, rating_txt, rated_txt, type_txt. You can see their layout and how they are labeled in Figure 21.9 (which also shows results being returned).

   Figure 21.9. Using another ASP page, you can return specific data about a selected DVD and fill the fields in Flash.

   

   
   

2. After that, it's back to ActionScript. Add these lines of code to the first frame of the Actions layer:

    //The listener object for when a user selects a DVD var dvd_obj:Object = new Object(); dvd_obj.change = function(){   var dvdID = dvds_list.value;   var dvdInfo_lv:LoadVars = new LoadVars();   dvdInfo_lv.onLoad = function(success){    if(success){      if(!this.error){       title_txt.text = this.title;       director_txt.text = this.director;       rating_txt.text = this.rating;       rated_txt.text = this.rated;       type_txt.text = this.type;      }else{       trace(this.error);      }    }else{      trace("An error occurred with the connection");    }   }   dvdInfo_lv.sentId = dvdID;   //send and receive the data   dvdInfo_lv.sendAndLoad("http://MYSERVER/getDVDInfo.asp",dvdInfo_lv, "POST"); } //add the event listener to the List component dvds_list.addEventListener("change", dvd_obj); 

   
   

Part of this code was covered back in Chapter 16, "Components 2.0." A listener object is built, and then the event is set to it for when a user selects a DVD from the list. When this event is triggered, another LoadVars object is created that handles sending the correct id to the ASP page and handles the data being returned. If, for some reason, no DVD information is returned, an error is sent to the Output panel. But if data is returned, it is sent to the text fields we just made. After the event, we add the event listener to the List component holding the DVDs. Test it out and you will see, as in Figure 21.9, when you click on a DVD, the information is returned to Flash and the fields are filled.

The final piece of this example is the capability to search through the titles and return results based on the keyword. Again, we will start with the ASP page.

[View full width]
 <%@Language=VBScript%> <%Option Explicit%> <%   ''the data coming from Flash   Dim keyWord    keyWord = Trim(Request("keyWord"))   '''''''Make the connection   Dim myConnection    Set myConnection=Server.CreateObject("ADODB.Connection")    myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" &  Server.MapPath("movies.mdb")    myConnection.Open   '''''''Create the SQL statement we need and set it to a variable   Dim searchDVDs    searchDVDs = "SELECT title, id FROM dvds WHERE title LIKE '%" & keyWord & "%'"      '''''''Make the record set object   Dim myRS    Set myRS=Server.CreateObject("ADODB.Recordset")    myRS.Open searchDVDs, myConnection    Dim allDVDs, ids, mainMessage      Do While Not (myRS.EOF)       allDVDs = allDVDs& myRS("title") & "~"       ids = ids & myRS("id") & "~"       myRS.MoveNext      Loop   '''''''Create the message we are sending to Flash   mainMessage = "dvds=" & allDVDs & "&ids=" & ids   '''''''Clean up...MUST HAVE!!!   myRS.Close   Set myRS=Nothing   myConnection.Close   Set myConnection=Nothing   '''''''Send that data to Flash   Response.Write(mainMessage) %> 

This file builds on the original getAllDVDs.asp page we started with, but the difference is we collect a keyword being sent from Flash, and search the database using a special SQL string that has the keyword LIKE in it. This keyword does comparisons combined with the wildcard % on either side of the string we are looking for. This means it will look both at the beginning and the end of the value of title in each row. That means it will return any title that has the keyword anywhere in it. After that, the rest is just like getAllDVDs.asp.

Save this file as searchDVDs.asp. If you want to see it work, try this URL: http://MYSERVER/searchDVDs.asp?keyWord=can

Now, we'll go back to Flash for the final few interface pieces and a little more ActionScript:

1.	The first thing is to create an input text field above the List component in the content layer for the search keyword. It has an instance name of search_txt and the border is enabled.
2.	After that, we will need two buttons, both on the Content layer. One should have an instance name of search_btn for searching, and the other with an instance name of showAll_btn for returning all the DVDs again. You can see the layout of the field and the two buttons in Figure 21.10. Figure 21.10. The final layout of the application in the Flash authoring environment.
3.	Now for the ActionScript: back in frame 1 of the actions layer, we will add a few more things starting with a function to clear all the fields and the List component. This is because when we search for results, we want to clear everything out. The code looks like this: //clear the fields and the List function clearForm():Void{ dvds_list.removeAll(); title_txt.text = ""; director_txt.text = ""; rating_txt.text = ""; rated_txt.text = ""; type_txt.text = ""; }
4.	The next piece of code is the event when a user clicks on the Search button: search_btn.onRelease = function(){ //make sure there is a term to search for if(search_txt.text.length>0){ //clear the fields clearForm(); var search_lv:LoadVars = new LoadVars(); search_lv.onLoad = function(success){ if(success){ //get the data being returned var titles_array = this.dvds.split("~"); var ids_array = this.ids.split("~"); //temporary array var temp_array = new Array(); //because of the extra "~", there is a blank element var tempLength = titles_array.length - 1; if(tempLength>0){ var i = 0; while(i < tempLength){ temp_array.push({label: titles_array[i], data: ids_array[i]}); i++; } //set the list component dvds_list.dataProvider = temp_array; }else{ trace("None Returned"); } }else{ trace("An error occurred with the connection"); } } //send the keyword search_lv.keyWord = search_txt.text; //go get the results search_lv.sendAndLoad ("http://www.playgroupfinder.com/searchDVDs.asp", search_lv, "POST"); } } This code does a lot. First it checks whether there is a term to search for. If not, it just ignores the request. If there is a keyword in the field, the form is cleared with the function we just created, and a new LoadVars object is created. This LoadVars is very similar to the first one we made in this example, except that when results are returned, it makes sure at least one DVD is returned, or it sends a message to the Output panel saying no results are returned. If there are results, they are parsed and placed in the List component just like when we get all the DVDs. And when you click on the DVDs in the List component, the info for that DVD is returned.
5.	There is just one more thing to add to the ActionScriptthe code for the showAll_btn: //Go get all DVDs showAll_btn.onRelease = function(){ clearForm(); search_txt.text = ""; getAllDVDs(); }

This final piece of code clears the form (as well as the search text field) and calls the first function we made in this example to go get all the DVDs in the database. Now test the movie out, and it will be able to do everything it has done so far, as well as search through the movie titles for keywords and return their results.

Figure 21.11. Flash and ASP combined with Access make a great team for storing, organizing, and displaying data.