Integrating Flash & ASP: Part II

Multiple Recordsets

Introduction

For those of you who read my last article, "Flash and ASP Integration," I really hope you enjoyed it and that it helped you to further your knowledge of dynamic Flash. A lot of you responded, asking how to display data from multiple recordsets in a Flash movie, and I promised you another article. So, without further ado, here we go!

The Concept

You have probably dabbled in this before, and most likely, it brought about some frustration. This can be quite difficult, particularly in Flash 4 and earlier. Now, Flash 5 supports HTML in dynamic text fields. This means that you can actually put hypertext in text fields, and this prevents fooling around with the Duplicate Movie Clip command. However, Flash 5 is still relatively new (at the time of this writing) so I will show you how to do this using two different methods.

As before, we will be passing variables to Flash via our URL-encoded response string. Luckily, there's no character limit for the variable definition string, unlike the 255-character limit on query strings. Therefore, you can define as many variables in Flash as you like.

Example 1: Displaying Records in Flash 4

First, I'll show you how to put together a Flash movie which displays an input number of records from a data source. This method is best suited for Flash 4.

Example 1's Support Package

The materials in the support package are as follows:

The database has fifteen records in it with fabricated names. If one of these names is yours, it's purely by accident! :)

The system we'll be creating using this method will be a multi-functional search engine. Let's begin by designing the Flash front end. We will not be using buttons to link out to your e-mail program, because that's an entirely different tutorial. We'll just list the contents of what we find.

Step 1: Designing the Front End

Load up Flash 4 and create a new document. Add any little frills or color niceties you like, or just download the support package above. Ready? OK, let's get cracking.

Add a keyframe and these actions in the first frame:

Set Variable: "NameLast" = ""

Set Variable: "success" = ""

Stop

These lines allow you to input text, and also eliminate stray variable values (which crop up from time to time).

Next, add a descriptive instruction near the top of the page. Mine reads:

Create a text box with the |ab button depressed, and go to its Properties. Change TextField# to NameLast. I have provided a Search button in the .fla file. You can use mine or make your own, but drop one on the stage and right-click it. Go to its instance properties and click the Actions tab. Paste the following script into that window:

On (Release)

      Go to and Play (Results, 1)

End On

Right-click in the second keyframe and choose Insert Blank Keyframe. Right click again, and go to Properties. Click the Frame tab, and paste this line into the window (by right-clicking in the code window and choosing Paste):

Load Variables ("ex1-emails.asp", 0, vars=POST)

Load Variables is a subcommand of the Load Movie command in Flash 4. This command loads variables from ex1-emails.asp, which will return a long response string to the HTML header and put the variables in Flash's memory. The 0 indicates the level at which to load the variables (similar to the Z-order of application windows). vars=POST indicates that we're sending the value of our variables to ASP for processing.

Now, insert a keyframe at Frame 3. Let's use frames 3 through 6 for our loading animation. I just used the words Loading Data with an ellipsis (...) following the text.

Go back to Frame 3 and go to its Actions tab. Paste the following into the window:

If (success ne "")

      If (success eq "False")

            Go to and Stop (8)

      Else

            Go to and Stop (7)

      End If

End If

In Frame 6, add an action that looks like this:

Go to and Play (3)

This will loop the animation over and over again until the data is returned.

Now, in Frame 7, choose Insert Blank Keyframe, and let's add another textbox (with the |ab button depressed). Make it about 1/2 the height and 1/2 the width of your movie, and center it. Right-click it and go to its Properties. Change TextField# to the word "results" and check the "Disable Editing" checkbox. Click OK. Insert another blank keyframe in frame 8, and write a small error message to the effect of "The person you are looking for does not exist in our database." I have provided a Back button in the .fla file, which you can put on this screen to send the user back to the first scene.

That's it. You're done with the Flash side of things.

Step 2: ASP Coding

You'll be making an easy, DSN-less connection to our data source, which is ex1-emails.mdb. The code below makes up ex1-emails.asp.

<%@Language="VBScript"%>

<%

	' Very good practice to include this line

	Option Explicit



	' RecordSet and Connection objects, and SQL string

	Dim oRS, oConn, strSQL



	Dim results



	Set oConn = Server.CreateObject("ADODB.Connection")

	Set oRS = Server.CreateObject("ADODB.Recordset")



	' Make a DSN-less connection to the DB

	oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _

		Server.MapPath("ex1-emails.mdb")



	' Open our recordset accordingly

	If UCase(Request("NameLast")) = "ALL" Then

		strSQL = "SELECT * FROM Emails"

	Else

		strSQL = "SELECT * FROM Emails WHERE NameLast LIKE '" & _

			Request("NameLast") & "'"

	End If



	oRS.Open strSQL, oConn, 2, 3



	' 2 and 3 are numeric equivalents of adOpenDynamic and

	' adLockOptimistic.  These are the best choices for

	' what we're trying to accomplish.

	' See ADO documentation for other cursor and lock types.



	If oRS.EOF Then

		Response.Write "success=False"

	Else

		Response.Write "success=True&results="

		Do While Not oRS.EOF

			results = results & oRS("NameLast") & ", " & _

				oRS("NameFirst") & vbCr

			results = results & oRS("EmailAddress") & vbCrLf

			oRS.MoveNext

		Loop

	End If



	oRS.Close

	Set oRS = Nothing

	oConn.Close

	Set oConn = Nothing



	Response.Write Server.URLEncode(results)

%>

Now, just publish the .fla file by choosing File -> Publish, and access it via your web server.

This will not work if you try to run it off of your hard drive (i.e. opening your browser and navigating to "c:\inetpub\wwwroot\examples\ex1-emails\ex1-emails.html") because ASP needs a server to run on! This means you must access it by the http:// protocol.

Modifying This Example for Flash 5

To make this more useful, you can take advantage of Flash 5's ability to read HTML in its text boxes. For those of you who use Flash 5, here's the code for this very same Flash movie in Flash 5.

Example 2's Support Package

The materials in the support package are as follows:

Download this support package if you're using Flash 5. It includes the exact same .fla file, because 5 will automatically parse 4's code (a VERY cool new feature). Flash 5 has a more java-type syntax and true OOP, which makes this a lot easier for us web developers!

I'll take a moment to plug Macromedia's excellent work. You can get the latest version of Flash 5 at http://www.macromedia.com/software/flash.

I've modified the code a little bit for Flash 5 to actually include a link to invoke the e-mail client.

Here's what I've done. See the commented section for directions.

<%@Language="VBScript"%>

<%

	'================

	' ex2-emails.asp

	' For Flash 5

	'================



	' Very good practice to include this line

	Option Explicit



	' RecordSet and Connection objects, and SQL string

	Dim oRS, oConn, strSQL



	Dim results



	Set oConn = Server.CreateObject("ADODB.Connection")

	Set oRS = Server.CreateObject("ADODB.Recordset")



	' Make a DSN-less connection to the DB

	oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _

		Server.MapPath("ex1-emails.mdb")



	' Open our recordset accordingly

	If UCase(Request("NameLast")) = "ALL" Then

		strSQL = "SELECT * FROM Emails"

	Else

		strSQL = "SELECT * FROM Emails WHERE NameLast LIKE '" & _

			Request("NameLast") & "'"

	End If



	oRS.Open strSQL, oConn, 2, 3



	' 2 and 3 are numeric equivalents of adOpenDynamic and

	' adLockOptimistic.  These are the best choices for

	' what we're trying to accomplish.

	' See ADO documentation for other cursor and lock types.



	If oRS.EOF Then

		Response.Write "success=False"

	Else

		Response.Write "success=True&results="

		Do While Not oRS.EOF

			results = results & oRS("NameLast") & ", " & _

				oRS("NameFirst") & vbCr



			'=======================================

			' The line below has been changed to

			' invoke the email client upon clicking.

			'=======================================



			results = results & "<a href=""mailto:" & _

			oRS("EmailAddress") & """><u>" & _

			oRS("EmailAddress") & "</u></a>" & _

			vbCrLf



			'=========================================

			' End modification

			' The _ operator allows the concatenation

			' of two strings to occur over the span of

			' more than one line.

			'=========================================



			oRS.MoveNext

		Loop

	End If



	oRS.Close

	Set oRS = Nothing

	oConn.Close

	Set oConn = Nothing



	Response.Write Server.URLEncode(results)

%>

That's about all there is to it! I hope I've helped you and I look forward to reading your comments. If you have problems, make sure you take these steps:

1. Did you access the html file via IIS or PWS (that is, via the http:// protocol on a server that has ASP and ADO installed) ?
2. If you changed something, did you empty your temporary Internet files?
3. Sometimes PWS croaks for no apparent reason (the latest one does, anyway!), so stop the service and restart it. If that doesn't work, feel free to e-mail me at dan@catapultic.com

Thanks a lot, and I hope you enjoy the article!