[[[[ connect a Flash movie to an Access database, and use an ASP page]]]]

<table border="1" width="100%" bgcolor="#666699" bordercolor="#FFFFFF">
 <tr>
 <td width="100%"><span class="clsBlurb"><font color="#FFFFFF" face="Verdana"><b>Macromedia's
  Flash is often only used to create those annoying Web site intros that
  people skip right past. Well, since the latest versions of Flash including
  the ability to interface with ASP and other server-generated Web pages you
  can now do much more.</b></font>
  <p><font color="#FFFFFF" face="Verdana">This article explains how to
  connect a Flash movie to an Access database, and use an ASP page to query
  the database and hand information over to the Flash movie. We are going to
  build a very simple Flash address book to demonstrate the technique.</font>
  <p><font color="#FFFFFF" face="Verdana">You are going to need a few tools
  to build the address book: Macromedia Flash 5, Internet Information
  Services 4.0 (or IIS 5.0), and a copy of Microsoft Access.</font>
  <p> 
  <p></span><span class="clsTitle"><font color="#FFFFFF" face="Verdana">The
  Basics</font></span>
  <p><span class="clsBlurb">
  <p><font color="#FFFFFF" face="Verdana">A Flash movie cannot query a
  database directly. It can, however, fetch an ASP page that, in turn, can
  query a database. This functionality revolves around using Flash's
  ActionScript function loadVariables, as follows:</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
loadVariables(URL, location);
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">The loadVariables function retrieves
  the contents of the URL specified and used this to set variables within
  the Flash movie. The content must be in Multipurpose Internet Mail
  Extension (MIME) format or (to get technical)
  application/x-www-urlformencoded. For instance, if the URL specified
  contains a page with the following content:</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
Var1=Test&Var2=Demo
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">The variable Var1 within the Flash
  movie would be set to "Test" and the variable Var2 would be set
  to "Demo." The variables can then be accessed through Flash
  ActionScript to modify the behavior of the movie. In our demonstration we
  are going to use this behavior to pass data to a Flash movie from an
  Access database that will be queried by an ASP page.</font>
  <p></span>
  <p><span class="clsTitle"><font color="#FFFFFF" face="Verdana">Database
  Design</font></span>
  <p><span class="clsBlurb">
  <p><font color="#FFFFFF" face="Verdana">First, let's build the database.
  Our address book is going to be pretty simple so the database only has a
  single table called "Contacts" with five fields: ContactID,
  Name, Telephone, City, and Notes.</font>
  <p> 
  <table border="1" cellSpacing="2">
  <tbody>
   <tr>
   <td><b><font color="#FFFFFF" face="Verdana">Field name</font></b></td>
   <td><b><font color="#FFFFFF" face="Verdana">Type</font></b></td>
   <td><b><font color="#FFFFFF" face="Verdana">Size</font></b></td>
   </tr>
   <tr>
   <td><font color="#FFFFFF" face="Verdana">ContactID</font></td>
   <td><font color="#FFFFFF" face="Verdana">AutoNumber</font></td>
   <td><font color="#FFFFFF" face="Verdana">-</font></td>
   </tr>
   <tr>
   <td><font color="#FFFFFF" face="Verdana">Name</font></td>
   <td><font color="#FFFFFF" face="Verdana">Text</font></td>
   <td><font color="#FFFFFF" face="Verdana">50</font></td>
   </tr>
   <tr>
   <td><font color="#FFFFFF" face="Verdana">Telephone</font></td>
   <td><font color="#FFFFFF" face="Verdana">Text</font></td>
   <td><font color="#FFFFFF" face="Verdana">50</font></td>
   </tr>
   <tr>
   <td><font color="#FFFFFF" face="Verdana">City</font></td>
   <td><font color="#FFFFFF" face="Verdana">Text</font></td>
   <td><font color="#FFFFFF" face="Verdana">50</font></td>
   </tr>
   <tr>
   <td><font color="#FFFFFF" face="Verdana">Notes</font></td>
   <td><font color="#FFFFFF" face="Verdana">Memo</font></td>
   <td><font color="#FFFFFF" face="Verdana">-</font></td>
   </tr>
  </tbody>
  </table>
  <p><font color="#FFFFFF" face="Verdana">The database is called
  "AddressBook.mdb" and is held in the same directory as the ASP
  and Macromedia Flash File Format (SWF) files we are about to build. (SWF
  is the file format used by Macromedia Flash to deliver graphics,
  animation, and sound over the Internet. About 90 percent of Web users can
  view SWF content without having to install a plug-in.)</font>
  <p></span>
  <p><span class="clsTitle"><font color="#FFFFFF" face="Verdana">ASP Design</font></span>
  <p><span class="clsBlurb">
  <p><font color="#FFFFFF" face="Verdana">Let's take a look at the ASP page
  we will use to fetch</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
<%
 Set DataConn = Server.CreateObject("ADODB.Connection")
 DataConn.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & 
Server.MapPath("AddressBook.mdb")
 Set cmdTemp = Server.CreateObject("ADODB.Command")
 Set rstContacts = Server.CreateObject("ADODB.Recordset")
 cmdTemp.CommandText = "Select * From Contacts"
 cmdTemp.CommandType = 1
 Set cmdTemp.ActiveConnection = DataConn
 rstContacts.Open cmdTemp, , 1, 3
 rstContacts.Move CLng(Request("Record"))
 Response.write "Name=" & Server.URLEncode(rstContacts("Name")) & "&"
 Response.write "Telephone=" & Server.URLEncode(rstContacts("Telephone")) & "&"
 Response.write "City=" & Server.URLEncode(rstContacts("City")) & "&"
 Response.write "Notes=" & Server.URLEncode(rstContacts("Notes")) & "&"
 Response.write "TotalRecords=" & rstContacts.RecordCount
 rstContacts.Close
 DataConn.Close
%>
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">The page assumes that we pass in the
  record that we want back from the database and then it returns the
  information in MIME format using the Server.URLEncode command.</font>
  <p> 
  <p> 
  <p><font color="#FFFFFF" face="Verdana">Note: We output one extra piece of
  information (in addition to our information fields) from our ASP page -
  that is "TotalRecords." TotalRecords is a numerical variable
  holding the number of records in the address book. This will help our
  Flash movie know when it has reached the end of our address book.</font>
  <p><font color="#FFFFFF" face="Verdana">Our ASP page is called
  "GetDetail.asp" and will be saved in the same directory as our
  database and Flash files.</font>
  <p></span>
  <p><span class="clsTitle"><font color="#FFFFFF" face="Verdana">Flash
  Design</font></span>
  <p><span class="clsBlurb">
  <p><font color="#FFFFFF" face="Verdana">With our database and ASP page
  built to query it, we next to put together our Flash movie to produce the
  front end to our address book. Let's start off with a new movie and insert
  a blank movie clip into it.</font>
  <p><font color="#FFFFFF" face="Verdana">The movie clip is going to be our
  address book, and it will consist of five text fields (for us to display
  our information in) and two buttons (left and right arrows used to
  navigate through the records.) </font>
  <p> 
  <p><font color="#FFFFFF" face="Verdana">The text fields have been created
  as dynamic text and have each been given a variable name. This will allow
  us to control their contents from within ActionScript.</font>
  <p><font color="#FFFFFF" face="Verdana">Look at what happens when the
  movie clip is first loaded. We add an action to the clip to tell it to
  load up our ASP page with the first record as soon as it finishes loading.
  The ActionScript looks like this:</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
onClipEvent(load)
{
	CurrentRecord = 0;
	loadVariables ("getdetails.asp?Record=0", this);
}
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">It simply initializes our
  CurrentRecord variable (which we will use to keep track of our position in
  the address book) and then loads our GetDetails.asp page, which asks for
  the first record (i.e., Record 0).</font>
  <p><font color="#FFFFFF" face="Verdana">One of the features of the
  loadVariables function is that it does its stuff asynchronously. This
  means that after Flash has executed a loadVariables command, it doesn't
  hang around waiting for the results to come back. Therefore, the data
  hasn't necessarily been loaded by the time the program reaches the line
  following the loadVariables function. So we need a mechanism to tell the
  movie to update our text fields whenever the data has finally loaded in.
  To achieve this we use the onClipEvent(data) action. This action is called
  whenever Flash has finished loading a set of variables. Our ActionScript
  looks like this:</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
onClipEvent(data)
{
	strName = Name;
	strTelephone = Telephone;
	strCity = City;
	strNotes = Notes;
	strPosition = "Record " add String(CurrentRecord+1) add " of " add String(TotalRecords);
}
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">This code simply transfers the
  variables retrieved from the ASP page into the text boxes that we added to
  our movie clip. It also updates a text field to show which record we are
  currently displaying.</font>
  <p><font color="#FFFFFF" face="Verdana">Finally, we need to assign actions
  to the left and right arrows so we can navigate through the address book.
  Here's the code for the right (move to next record) arrow:</font>
  <pre><font color="#FFFFFF" face="Verdana"><font size="2">
on (release)
{
	CurrentRecord++;
	if (CurrentRecord == TotalRecords)
 		CurrentRecord = 0;
	loadVariables ("getdetails.asp?Record=" add String(CurrentRecord), this);
}
</font>
</font></pre>
  <font color="#FFFFFF" face="Verdana">This code increases the CurrentRecord
  variable by 1 and checks to see whether we have gone past the last record
  in the address book. If we have, CurrentRecord is reset to 0, and the user
  is sent back to the first record in the address book. The code next loads
  the variables associated with the record from the ASP page. When the
  record has been loaded, Flash will call the onClipEvent(data) action
  again, and this will update the text boxes our users see.</font>
  <p><font color="#FFFFFF" face="Verdana">The code for the left (move to
  previous record) arrow is virtually the same, except that we are
  decreasing the current record rather than increasing it.</font>
  <p><font color="#FFFFFF" face="Verdana">And that's all we need to do. When
  we launch the SWF file from a browser, it will load the first record into
  Flash variables within the onClipEvent(load) action. After the variables
  have been loaded, Flash will call the onClipEvent(data) action, where we
  update our text fields to display the information to our user.</font>
  <p><font color="#FFFFFF" face="Verdana">Clicking on either navigation
  button will trigger actions that retrieve our ASP page and load in the new
  record, again calling on the onClipEvent(data) action. </font>
  <p> 
  <p></span>
  <p><span class="clsTitle"><font color="#FFFFFF" face="Verdana">Conclusion</font></span>
  <p><span class="clsBlurb">
  <p><font color="#FFFFFF" face="Verdana">By combining the capabilities of
  Flash and ASP it's possible to create solutions that unite the graphical
  appeal of Flash with the data-retrieval capabilities of ASP. In this
  demonstration, we showed how it is possible to connect a Flash movie to an
  Access database. However, modifications to the ASP code would allow us to
  connect to an SQL server or any other data source for that matter.</font></p>
  </span>
  <p> </td>
 </tr>
</table>