Macromedia Flash Professional 8 Unleashed

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).

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]

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]

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.

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:

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.

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]

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]

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:

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.