Create a search form or quick report in asp
March 25, 2007
ASP | Snippets

The example code below shows how to set up a form using Dreamweaver and ASP that queries a database based on the value of one or more form fields.

We have a database containing the transcripts of the results of courses taken in our online learning management system. Each online SCORM course has several sections or lessons. This form checks the status and score on each individual lesson, given the user's id and the course code.

The tables and fields involved are:

More after the jump...
Ads by Google
Posted by ellen at March 25, 2007 12:22 PM

  1. Create a new ASP/VB script site in Dreamweaver. Follow the steps in setting up a testing server and database connection to your database.

  2. Create a form with the appropriate fields needed to fill in the parameters to run your query. (See "form1" in the example"). Name each field as shown in the example.

  3. Replace the query "Recordset1_cmd.CommandText" with your own query. Replace all mentions of my field names with your own, throughout the script.
  4. In the area "div id="results", create your own HTML results, using appropriate fields from the tables in your query.


    


    <%@LANGUAGE="VBSCRIPT"%>
  <!--#include file="../../../Connections/yourdatabase.asp" -->
  <%
  'table scormmoduletranscript contains scores and statuses for each individual module ("SCOs") in a scorm course. 
  'table 
  'define a variable to use in the query
  Dim Recordset1__courseCode
  'if something is typed into the form field "courseCode" then set the variable Recordset1__courseCode to that value 
  If (Request.Form("courseCode") <> "") Then 
  Recordset1__courseCode = Request.Form("courseCode")
  End If
  'define another variable to use in the query
  Dim Recordset1__userid
  If (Request.Form("userid") <> "") Then 
  Recordset1__userid = Request.Form("user")
  End If 
  %> 
  <%
  
  Dim Recordset1
  
  Dim Recordset1_cmd
  
  Dim Recordset1_numRows
  'define Recordset1_cmd as an ADODB command
  'The ADO Command object is used to execute a single query against a database. The query can perform actions like creating, adding, retrieving, deleting or updating records.
  Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
  Recordset1_cmd.ActiveConnection = MM_lmsp_STRING
  'text of the SQL query
  Recordset1_cmd.CommandText = "SELECT * FROM LESSONTRANSCRIPT WHERE TRANSCRIPTID IN (select ID from TRANSCRIPT where USERID IN (SELECT USERID from USERS WHERE USERID = '" & Recordset1__userid & "') AND LEARNINGACTIVITYID IN (SELECT ID from LEARNINGACTIVITY where CODE LIKE '%" & Recordset1__courseCode & "%'))"
    

    Recordset1_cmd.Prepared = true
  'execute the ADODB command
  Set Recordset1 = Recordset1_cmd.Execute
    

    %>
    

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml">
  <head>
  <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
  <title>Untitled Document</title>
  <style>
  #results td {
  border-bottom:1px solid #CCC;
  padding-left:12px;
  font:12px Arial, Helvetica, sans-serif;
  height:18px;
  }
  </style>
  </head>
    

    <body>
  <p>&nbsp;</p>
  <p>This page will find the individual SCORM module transcript rows for a particular user and SCORM course that you specify. </p>
  <p>Enter a course code (note: use ALL CAPS for the letters) and 8-digit user id:</p>
  <form id="form1" name="form1" method="post" action="aiccTranscript.asp?mode=s" >
  <label>User ID (example: 58461328 )
  <input name="userid" type="text" id="userid" value="" /> <br />
  <br />
  Course code (examples: HIST-20003 or HIST or 20003)
  <input name="courseCode" type="text" id="courseCode" value="" />
  </label>
  <input type="submit" name="Submit" value="Submit" />
  </form>
  <p>
  <div id="results" style="display:block;border:1px solid #000;width:500px;height:auto">
  <table cellpadding=0 cellspacing="0" id="results">
  <tr>
  <td><b>transcript id</b></td><td><b>learningactivity id</b></td><td><b>SCORM module (SCO)</b></td><td><b>score</b></td><td><b>learning status</b></td>
    

    </tr>
  <tr>
    

    <% 
    

    if request.querystring("mode")="s" then
  while not recordset1.eof
  response.write ("<td>" & recordset1("transcriptid") & "</td>")
  response.write ("<td>" & recordset1("learningactivityid") & "</td>")
  response.write ("<td>" & recordset1("userid") & "</td>")
  response.write ("<td>" & recordset1("score") & "</td>")
  response.write ("<td>" & recordset1("lessonstatus") & "</td></tr>")
    

     
    
  recordset1.movenext
  wend
    

    end if%>
    

    </table>
  </div>
  </body>
  </html>
  <%
  Recordset1.Close()
  Set Recordset1 = Nothing
  %>


    Ads by Google

Ads by Google

Related Entries:


 RSS   |   Contact Me
Ads by Google