VBScript/ASP User-Defined Class Objects

To define a user-defined Class Object, you use the Class statement to declare a class. The End Class statement defines the termination of the Class. Together, these statements form a Class construct, or Class block. E.g.

Class objName

‘ Place the Class variables, Properties and Methods here

End Class

In this syntax, objName is the name given to the Class Object. The class object name must follow standard VBScript variable naming conventions. Class Objects are usually declared in the variable definition sections. You can have multiple Class blocks in a single VBScript file, but each block must contain the Class …End Class statements. Classes cannot be nested.

Once you have defined the Class Object, you need to create an instance of the Class, similar to how other objects are created. When the Class Object is instantiated, memory is allocated for the Class Object. The Set statement is used with the New keyword to assign an instance of the class to a variable. With VBScript, this is the only time the New keyword is used (i.e. to instantiate a user-defined Class). E.g.

Dim MyObj

Set MyObj = New objName

The Object name MyObj is the Object variable name, and must follow standard VBScript variable naming conventions. The Object variable name is a reference (address) of the Object stored in memory, it is not the Object itself.

Inside the Class block, any Class variables, Properties, Methods and Events can be defined by the developer. The developer does not have to use all of the capabilities of the Class construct, i.e. Classes can be created without Methods or Properties. The design of the Class Object is completely up to the developer.

Class variables are created within the Class structure by using the Dim, Public, or Private statements. Variables defined within the Class structure by any of these statements must follow the standard VBScript variable naming conventions. Variables can be simple variables or arrays. E.g.

Class className

Dim var1, var2

Public var3, var4

Private var5, var6

End Class

The choice of the Dim, Public, or Private statements determine whether the variable is accessible outside of the Class Object. Variables are public by default, i.e. they are accessible outside of the Class Object. Both the Dim and the Public statements create public variables, while the Private statement creates variables that are not public. As a general rule, it is good programming practice to make all Class variables private, since the developer will want to tightly control when these variables are changed.

VBScript does not support Class-level Constants, i.e. named constants declared at the Class level. You cannot use the Const statement at the Class-level so that a constant can be used throughout a Class, but you can use the Const statement within a Property or Method. However, the constant will only have local scope within the Property or Method.

Class Object variables are accessible to VBScript code outside the Class through Class Properties. Class Properties “wrap” the Private variables of a Class. Inside the Class block, the Properties are defined by Property Get [|Let|Set] … End Property statement(s). For VBScript code outside the Class, the Property is accessed by referencing the Object Name.Property.

There are different types of Class Properties, depending on whether the Class variable is to be read, written to, or the Class variable is itself a Class Object. These Properties can be declared Public or Private.

Property Get

The Property Get procedure is used to access (return) private variables inside of the Class structure that are used as a read-only Property, or the read portion of a read-write Property. For VBScript code outside the Class, this type of Class Object Property is generally assigned to a variable or used in a conditional expression. The Property Get procedure returns a value to the calling code, and is general not used with any arguments. [Note: VBScript will let you add arguments to the Property Get procedure, but if you do so you must add the additional argument to the corresponding Property Let or Property Set procedure, since Property Let/Property Set must have one more argument than the corresponding Property Get procedure. It is generally considered bad programming form to have arguments in the Property Get procedure].

Property Let

The Property Let procedure is used to access (assign) private variables inside of the Class structure that are used as a write-only Property or are the write portion of a read-write Property. For VBScript code outside of the Class, this type of Class Object Property is usually assigned by a variable or a constant.

Property Set

The Property Set procedure is exclusively used when the Class Object needs to store

Properties that are object-based instead of numeric, date, boolean or string subtype variables. Property Set replaces the Property Let procedure. While Property Set and Property Let are functionally similar, there are two key differences:

1. With the Property Set procedure, in the VBScript code segment (outside the Class block) you must use the syntax

Set Object1.Property = Object2

This is because VBScript does not let you use the assignment operator (=) to assign objects without the Set command.

2. The Property Set procedure makes it clear that the Property is an object-based Property

Example:

Class FileSpec                     ‘ Define a Class block

Private master_file

Private master_FSO

Public Property Let FileName(strName) ‘ Define a Public Property to assign the file name

master_file = strName

End Property

Public Property Get FileName           ‘Define a Public Property to retrieve a file name

FileName = master_file

End Property

Public Property Set FSO(m_FSO)     ‘ Define a Public Property for an object

Set master_FSO = m_FSO

End Property

End Class

Rem Below is the VBScript code

Dim objFSO              ‘ Declare variables and objects

Dim objFilePointer, cur_file

Set objFSO = CreateObject(“Scripting.FileSystemObject”)

Set objFilePointer = New FileSpec                      

objFilePointer.FileName = “Myfile.mdb”                          

cur_file = objFilePointer.FileName             

Set objFilePointer.FSO = objFSO               ‘ Assigns an Object to the Property

Set objFilePointer = Nothing           ‘ Keyword Nothing releases the object memory

A couple notes on the example above. The CreateObject command is used to instantiate an Object that is known at the system level (e.g. a COM object). Also, so far this example only shows how to assign and retrieve property values. It is generally the Method(s) that control the action an object performs, not the properties.

A Property can be made read-only by only providing a Property Get procedure, or by declaring the Property Let procedure as Private instead of Public. A Property can be made write-only by only providing the Property Let procedure, or by declaring the Property Get procedure as Private instead of Public.

Class Methods are really just Functions and Subroutines inside of a Class block. These functions and subroutines can be either Private or Public. If they are public, they will be accessible to a VBScript code segment outside of the Class block by referencing the obj.Method. If they are private, they will only be available to code within the Class block.

An example of Class Methods is as follows:

Class FileSpec

Private master_file

Private master_FSO Private master_file

Private Sub Class_Initialize  ‘ Class Object initialization code

‘ code goes here

End Sub

Private Sub Class_Terminate  ‘ Class Object termination code

‘ code goes here

End Sub

Public Property Let FileName(strName) ‘ Define a Public Property to assign the file name

master_file = strName

End Property

Public Property Get FileName ‘ Define a Public Property to retrieve a file name

FileName = master_file

End Property

Public Property Set FSO(m_FSO)  ‘ Define a Public Property for an object

Set master_FSO = m_FSO

End Property

Public Sub Delete ‘Method to delete the master file

master_FSO.DeleteFile (master_file)

End Sub

End Class

Rem Below is the VBScript code

Dim objFSO ‘ Declare variables and objects

Dim objFilePointer, cur_file

Set objFSO = CreateObject(“Scripting.FileSystemObject”) Set objFilePointer = New FileSpec ‘ Instantiate the Class Object

objFilePointer.FileName = “Myfile.mdb”

cur_file = objFilePointer.FileName

Set objFilePointer.FSO = objFSO ‘ Assigns an Object to the Property

objFilePointer.Delete ‘ Executes a Method to delete a file

Set objFilePointer = Nothing ‘ Keyword Nothing releases the object memory

VBScript Class Objects automatically supports two type of Class Events; Class_Initialize and Class_Terminate Events. The code inside the Class_Initialize event executes once when an Object based on the Class is first instantiated. Any code put in this event is optional, and is typically used for initialization. Code inside the Class_Terminate event executes once just before the Object based on the

Class is destroyed (i.e. Set to Nothing, or the Object goes out of scope). Usage is as follows:

Class FileSpec

Private master_file

Private master_FSO Private master_file

Private Sub Class_Initialize ‘ Class Object initialization code

‘ code goes here

End Sub

Private Sub Class_Terminate ‘ Class Object termination code

‘ code goes here

End Sub

Public Property Let FileName(strName) ‘ Define a Public Property to assign the file name

master_file = strName

End Property

Public Property Get FileName ‘ Define a Public Property to retrieve a file name

FileName = master_file

End Property

Public Property Set FSO(m_FSO) ‘ Define a Public Property for an object

Set master_FSO = m_FSO

End Property

End Class

VBScript Object Commands

VBScript includes several Functions and Statements that can be used to access objects, including their methods and properties. There are a large variety of objects available to VBSript, including user defined objects, intrinsic objects and extrinsic objects.

VBScript Object Functions

• CreateObject Creates and returns a reference to an Automation object

• GetObject Returns a reference to an Automation object from a file

• IsObject Returns a Boolean value indicating whether an expression references a valid Automation object.

Object Statements

Class Declares the name of a class, as well as a definition of the variables, properties, and methods that comprise the class

Exit Property Forces an exit from inside a Property Set function.

For each…Next repeats a group of statements for each element in an array or a collection.

Property Get Declares, in a Class block, the name, arguments, and code that form the body of a Property procedure that gets (returns) the value of a property

Property Let Declares, in a Class block, the name, arguments, and code that form the body of a Property procedure that assigns (sets) the value of a property

Property Set Sets a reference to an object

Set Assigns an object reference to a variable or property, or associates a procedure reference with an event. Usually used to instantiate an object.

Error Handling Statements

On Error Enables or disables error-handling

Object & Collection Summary

• Debug The Debug object is an intrinsic global object that can send an output to a script debugger, such as the Microsoft Script Debugger.

• Dictionary An associative array that can store any type of data. Data is accessed by a key.

• Drives A collection of Drive objects.

• Err Contains information about the last run-time error. Accepts the Raise and Clear
• methods for generating and clearing run-time errors.

• File An object that refers to a specific File

• Files A collection of File objects.

• FileSystemObject An object model used to access the Windows file system

• Folder An object that refers to a specific Folder

• Folders A collection of Folder objects.

• Match Provides access to the read-only properties of a regular expression match.

• Matches Collection of regular expression Match objects.

• RegExp Provides simple regular expression support.

• Submatches A collection of regular expression submatch strings.

• TextStream An object that refers to a text File

Objects and Classes

Traditional programming is made up of a collection of subroutines and functions that are typically processed in a sequential or looping manner. In contrast, object oriented programming is a different programming methodology where a program is viewed as being composed of a collection of individual objects. These objects process data and can interact with other objects directly without having to be explicitly programmed to do so. The advantages claimed by object-oriented program include code reusability, rapid deployment of large-scale complex tasks, and ease of use/debugging. Today, objectoriented programming is widely used and is supported with both programming languages (e.g. VB.NET, C++, Visual C++) and operating systems (e.g. Microsoft’s .NET architecture). Object-oriented programming has also become popular within scripting languages, such as VBScript. Beginning with VBScript 5.0, developers have been able to use user-defined Classes.

The key concepts with object-oriented programming include:

• Class

The class is the highest level that defines a unit (set) of data and its behavior. Classes form the basis for modularity and structure in an object-oriented program. The class should sufficiently describe the set of data, and the code for a class should be contained within it and be selfsufficient (except for operating system support). While the terms classes and objects often get used interchangeably, classes describe the structure of objects. One way to think of a class is that it is a container for code. It can also be viewed as a template for an object. When a class is declared (instantiated) by the Set statement, it then becomes an object and memory is allocated for it.

• Object

An object is an in-memory instance of a class. In computer science terms, it is a run-time manifestation (instantiation) of a particular exemplar of a class. Each object has its own data, but the code within a class can be shared (for efficiency). Programs generally have multiple objects. Multiple copies (objects) of a given class can be created. Objects are temporary, i.e. they can be created and removed at will, depending on the programming needs. 

• Encapsulation

Encapsulation wraps the data and functions into a single unit, ensuring that the object can be changed only through established interfaces. Encapsulation is sometimes referred to as information hiding. Some of these common interfaces are: 

o Fields

Fields are simply public variables stored within the object, as defined by the class. These variables store items of information about an object. 

o Properties

Properties, like fields, also store items of information on an object. But Properties use Property procedures to control how values are set or returned. VBScript has two primary Property procedures; Let and Get. The Get property procedure retrieves a Property value, while the Let Property procedure assigns a value to the property value. A third Property procedure Set is used with an Object inside of the Class block.

o Methods

Methods are a collection of subroutines (Sub) and function procedures (Function) declared within a class.

o Events

An event is a message sent by an object announcing that something important has happened. followed by a period, then the particular method, property or field of interest. E.g.

Object.Method

Object.Property

Object.Property.Item

• Dynamism

Dynamism relates to the method of allocating computer resources and definition resources required to run an object-oriented program. There are different types, but VBScript used latebound (late-binding) dynamic typing. This means that the VBScript engine will make the object type determination at runtime and allocate sufficient memory at that time. Note that VBScript and VB.NET are slightly different in their approach to dynamism, and therefore they can declare some variables and objects in different manners (although many forms of declaration are the same).

• Outlet Connections

At times, Objects will connect together and this connection needs to be defined. With IWS, an example of a connection would be between a VBScript object (e.g. ADODB) and a Database Provider (a Provider is a front-end to a database). This connection needs to be defined, and then the connection string (of parameters) between the objects gets defined. When the need for the connection is finished, the connection should be closed.

While a full treatment of object-oriented programming is beyond the scope of these materials, the fundamental concepts of Objects and Classes are important to understand. VBScript supports COMbased Objects (Component Object Module, a Microsoft standard) such as the ActiveX controls, ADO.NET, FileSystemObject, and Microsoft Office Automation objects. VBScript also supports userdefined classes, or Class Objects. VBScript COM objects and VBScript Class objects differ from each other in several important respects. These differences lead to each type of object having its unique strengths:

• VBScript classes are more flexible than VBScript COM objects. Class Objects have an abstract subtype that encapsulates the data you want and the functions you need to work with that data. VBScript COM objects have only basic subtypes (integer or string).

• VBScript classes are slightly more efficient than COM objects. The VBScript parser can execute the classes' code directly instead of asking the COM object to execute a method.

• COM objects are binary modules. VBScript classes are ASCII files.

• You can use any scripting language to write COM objects. You can only use VBScript to write VBScript classes.

• You can use COM objects from within any development environment that supports COM automation. VBScript classes can only be used within development and runtime environments that support VBScript (e.g IWS and Microsoft Internet Explorer). 

SQL Query Reference

SQL Queries have 3 basic parts:


1. The Statement

Essentially determines the purpose of the SQL query - to retrieve records, delete them, change them, etc.

2. The Clause(s)

Determines which records are affected by the query and how they are sorted -and can also perform a number of other functions, including grouping of identical records.

3. The Operation(s)

Actually perform operations on the records, such as combining or joining result sets.

Clauses


FROM Clause
Determines the tables in the database that the query is to affect/retrieve.


WHERE Clause
Determines the selection criteria to be used.


ORDER BY Clause

Determines the field(s) to be used to sort the results of a SELECT or SELECT...INTO statement, together with the order – either 'ASC' (ascending) or ‘DESC’ descending).

GROUP BY Clause

Determines the field(s) to be used for the grouping of the results of a SELECT or SELECT...INTO statement. 

HAVING Clause

Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause.

IN Clause
Identifies tables in any external database to which the database engine can connect.


Operations


UNION Operation

Used in combination with both Statements and Clauses for combining the results of two or more independent queries or tables.

INNER JOIN Operation:


LEFT JOIN Operation


RIGHT JOIN


SQL Sub Queries

A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery. You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement )
expression [NOT] IN (sqlstatement )
[NOT] EXISTS (sqlstatement )

comparison An expression and a comparison operator that compares the expression with the results of the subquery.

expression An expression for which the result set of the subquery is searched. 

sqlstatement A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.

You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression. 

Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE [Discount] >= .25);

Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the above example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more.

This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE [Discount] >= .25);

Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

 Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records. 

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees with the same job title. The Employees table is given the alias “T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;


The AS reserved word is optional.


Examples
SELECT LastName, FirstName, Title, Salary
FROM Employees
WHERE Title LIKE "*Sales Rep*" AND Salary > ALL
(SELECT Salary FROM Employees
WHERE (Title LIKE "*Manager*") OR (Title LIKE "*Director*"));

Lists the name, title, and salary of every sales representative whose salary is higher than that of all managers and directors.

SELECT DISTINCTROW ProductName, UnitPrice
FROM Products WHERE UnitPrice =
(SELECT UnitPrice FROM [Products]
WHERE ProductName = "Aniseed Syrup");


Lists the name and unit price of every product whose unit price is the same as that of Aniseed Syrup.


SELECT DISTINCTROW [Contact Name], CompanyName, [Contact Title], Phone
FROM Customers WHERE CustomerID IN
(SELECT DISTINCTROW CustomerID
FROM Orders WHERE OrderDate BETWEEN #04/1/94# AND #07/1/94#);


Lists the company and contact of every customer who placed an order in the second quarter of 1994.


SELECT LastName, FirstName, Title, Salary
FROM Employees T1
WHERE Salary >=
(SELECT AVG(Salary)
FROM Employees
WHERE Employees.Title = T1.Title)
ORDER BY Title;


Lists employees whose salary is higher than the average salary for all employees.


SELECT FirstName, LastName
FROM Employees AS E
WHERE EXISTS
(SELECT *
FROM Orders AS O
WHERE O.EmployeeID = E.EmployeeID);

Selects the name of every employee who has booked at least one order. This could also be done with an INNER JOIN.

Working With Recordsets

In order to read information from a Datasource, you need to open a 'Recordset' -a set of database records based on some type of criteria, either all of the records in a table or those matching some condition or set of conditions. To create a recordset containing all of the records in one table of the database,

without sorting them:

<%
strSQLQuery = "SELECT * FROM tablename "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


To create a recordset containing only those records in one table where the field 'Name' consists of only the word 'Fred':


<%
strSQLQuery = "SELECT * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


To create a recordset containing all of the records in one table and sort them alphabetically based on the field Name starting at 'a': 


<%
strSQLQuery = "SELECT * FROM tablename ORDER BY Name ASC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


To create a recordset containing all of the records in one table and sort them alphabetically based on the field 'Name' starting at 'z': 


<%
strSQLQuery = "SELECT * FROM tablename ORDER BY Name DESC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

If you want to be able to be sure of selecting only one particular record from a database, each table should have a 'Unique Key' field - usually a simple auto incrementing number field. You can then select, update or delete records in the database using this field as the criteria.

<%
strSQLQuery = "SELECT * FROM tablename WHERE RecordID = 15"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

To retrieve the values of various fields in a Recordset use:

<%

strValue = rs("FieldName ")

%>

strValue can of course be any variable name, FieldName is the field name in the table that makes up the recordset. Multiple recordsets can be opened for the same database and/or table - just change the 'rs' to whatever name you want to give to each recordset. Standard SQL queries can be used to update, add or delete records, or create recordsets based on conditions. For example:

<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred' AND Address
= 'Smith St'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred' OR Name =
'John'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


<%
strSQLQuery = "UPDATE tablename SET FieldName1 = " & strValue1 & " WHERE
FieldName2 = " & strValue2 & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>


<%
strSQLQuery = "INSERT INTO tablename (FieldName1, FieldName2) VALUES (" &
strValue1 & ", " & strValue2 & ")"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

There are some minor differences in the SQL queries used for Access versus SQL Server - the above works on SQL Server.

Note:

tablename would normally be just the name of the table in the database when using an Access database for instance, but when using SQL Server you also need to specify a UserName with access permissions to that database - in the format UserName.TableName'. Talk to your server administrator for more information.