ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example (JScript)

Article
12/21/2006

This example uses the ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction properties to execute a stored procedure. Cut and paste the following code to Notepad or another text editor, and save it as ActiveConnectionJS.asp.


<!-- BeginActiveConnectionJS -->
<%@LANGUAGE="JScript"%>
<%// use this meta tag instead of adojavas.inc%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->

<html>
<head>
    <title>ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties</title>
<style>
<!--
BODY {
   font-family: 'Verdana','Arial','Helvetica',sans-serif;
   BACKGROUND-COLOR:white;
   COLOR:black;
    }
.thead {
   background-color: #008080; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.thead2 {
   background-color: #800000; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.tbody { 
   text-align: center;
   background-color: #f7efde;
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
    }
-->
</style>
</head>

<body bgcolor="White">

<%
    var iRoyalty = parseInt(Request.Form("RoyaltyValue"));
    // check user input
    
    if (iRoyalty > -1)
    {
            // connection and recordset variables
        var Cnxn = Server.CreateObject("ADODB.Connection")
        var strCnxn = "Provider='sqloledb';Data Source=" + Request.ServerVariables("SERVER_NAME") + ";" +
            "Initial Catalog='pubs';Integrated Security='SSPI';";
        var cmdByRoyalty = Server.CreateObject("ADODB.Command");
        var rsByRoyalty = Server.CreateObject("ADODB.Recordset");
        var rsAuthor = Server.CreateObject("ADODB.Recordset");
        // display variables
        var filter, strMessage;        
        
        try
        {
            // open connection
            Cnxn.Open(strCnxn);
    
            cmdByRoyalty.CommandText = "byroyalty";
            cmdByRoyalty.CommandType = adCmdStoredProc;
            cmdByRoyalty.CommandTimeOut = 15;
    
            // The stored procedure called above is as follows:
                //    CREATE PROCEDURE byroyalty
                //  @percentage int
                //    AS
                //  SELECT au_id from titleauthor
                //  WHERE titleauthor.royaltyper = @percentage
                //  GO
        
            prmByRoyalty = Server.CreateObject("ADODB.Parameter");
            prmByRoyalty.Type = adInteger;
            prmByRoyalty.Size = 3;
            prmByRoyalty.Direction = adParamInput;
            prmByRoyalty.Value = iRoyalty;
            cmdByRoyalty.Parameters.Append(prmByRoyalty);
        
            cmdByRoyalty.ActiveConnection = Cnxn;
        
            // recordset by Command - Execute
            rsByRoyalty = cmdByRoyalty.Execute();
        
            // recordset by Recordset - Open
            rsAuthor.Open("Authors", Cnxn);
    
            while (!rsByRoyalty.EOF)
            {
                // set filter
                filter = "au_id='" + rsByRoyalty("au_id")
                rsAuthor.Filter = filter + "'";
                
                // start new line
                strMessage = "<P>";
                    
                // get data
                strMessage += rsAuthor("au_fname") + " "; 
                strMessage += rsAuthor("au_lname") + " ";
                    
                // end line
                strMessage += "</P>";
                
                // show data
                Response.Write(strMessage);
                    
                // get next record
                rsByRoyalty.MoveNext;
            }
        }
        catch (e)
        {
            Response.Write(e.message);
        }
        finally
        {
            // clean up
            if (rsByRoyalty.State == adStateOpen)
                rsByRoyalty.Close;
            if (rsAuthor.State == adStateOpen)
                rsAuthor.Close;
            if (Cnxn.State == adStateOpen)
                Cnxn.Close;
            rsByRoyalty = null;
            rsAuthor = null;
            Cnxn = null;
        }
    }
%>

<hr>


<form method="POST" action="ActiveConnectionJS.asp">
  <p align="left">Enter royalty percentage to find (e.g., 40): <input type="text" name="RoyaltyValue" size="5"></p>
  <p align="left"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
&nbsp;


</body>

</html>
<!-- EndActiveConnectionJS -->

Share via

ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example (JScript)

See Also

Additional resources