<HTML>
<HEAD><TITLE>Films</TITLE></HEAD>
<BODY>
<H2>Film Information</H2>

<CFTRY>
  <!--- Retrieve Films from live database --->
  <CFQUERY NAME="GetFilms" DATASOURCE="#REQUEST.DataSource#">
    SELECT FilmID, MovieTitle
    FROM Films
    ORDER BY Films.MovieTitle
  </CFQUERY>

  <!--- If any database errors occur during above query, --->
  <CFCATCH TYPE="Database">
    <!--- Construct connection string so we can query Films.txt file --->
    <CFSET Driver = "{MERANT 3.70 ColdFusion OEM 32-BIT TextFile (*.*)}">
    <CFSET DBDir  = GetDirectoryFromPath(GetCurrentTemplatePath())>
    <CFSET ConStr = "DRIVER=#Driver#;DB=#DBDir#;FLN=1;tT=COMMA;UT=GUESS">
  
    <!--- Retrieve Films from Text file --->
    <CFQUERY NAME="GetFilms" DBTYPE="dynamic" CONNECTSTRING="#ConStr#">
      SELECT FilmID, MovieTitle
      FROM Films.txt
      ORDER BY Films.MovieTitle
    </CFQUERY>

    <!--- Let user know that text file is being used --->
    <I><P>NOTE: 
    We are not able to connect to our real-time Films database at the moment.<BR>
    Instead, we are using data from our archives to display the Films list.<BR>
    Please try again later today for an up to date listing.<BR></I>  
  </CFCATCH>
</CFTRY>

<!--- Attempt database operation --->
<CFTRY>
  <!--- Retrieve Ratings from database --->
  <CFQUERY NAME="GetRatings" DATASOURCE="#REQUEST.DataSource#">
    SELECT RatingID, Rating
    FROM FilmsRatings
    ORDER BY Rating
  </CFQUERY>  
    
  <!--- Silently catch any database errors from above query --->
  <CFCATCH TYPE="Database"/>
</CFTRY>

  
<!--- Create self-submitting form --->
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="Post">

  <!--- If, after all is said and done, we were able to get Film data --->
  <CFIF IsDefined("GetFilms")>
    <!--- Display Film names in a drop-down list --->
    <P>Films:
    <CFSELECT
      QUERY="GetFilms" NAME="FilmID"
      VALUE="FilmID" DISPLAY="MovieTitle"/>
    <INPUT TYPE="Submit" VALUE="Go">
  </CFIF>
      
  <!--- If, after all is said and done, we were able to get Ratings data --->
  <CFIF IsDefined("GetRatings")>
    <!--- Display Ratings in a drop-down list --->
    <P>Ratings:
    <CFSELECT
      QUERY="GetRatings" NAME="RatingID"
      VALUE="RatingID" DISPLAY="Rating"/>
    <INPUT TYPE="Submit" VALUE="Go">
  </CFIF>    

</CFFORM>

</BODY>
</HTML>
