Windows Script Components came on the scene around 1998 and have become very popular with web developers, for several good reasons:

1) Easy to write using either VBScript or Javascript
2) Windows scripting host handles all the COM interface details for you automatically.
3) In certain situations WSC's can be instantiated using GetObject even though they have never been registered.
4) Great way to encapsulate frequently -used routines for DHTML, HTML controls, ADO and filesystem function and more - rather than using ASP <!--#include files. Eliminate "spaghetti code"!
5) You can write a component that's smart enough to be able to run either on the server or the client.
6) We can export a TypeLibrary and use the component - complete with Intellisense and Object Browsing - in Visual Basic or any other COM-compliant IDE.
7) Because its textual script, it's easy to add, modify, etc. and re-register the component using nothing more sophisticated than Notepad.
8) Because you can! (e.g., impress your boss and co-workers instead of doing things the same old way every time)

For those who are not familiar with WSC's, don't be intimidated. A WSC is just a text file written to follow a specific XML schema. Each WSC file contains two main blocks of information: a descriptive section and a script section. The descriptive section defines the programming interface of the component. It's completely declarative - here you specify the registration information (CLSID, progID, and version number) and the full set of exposed interfaces with a simple set of XML tags and attributes. By default, a WSC exposes the IDispatch interface for automation; all we need to do is specify the prototype of the various methods, properties, and events.

Rather than launching into a treatise on Windows Script Components here (which is really unecessary because all you need to do is search the MSDN Onlne under "Windows Script Components" and you'll find complete documentation, along with some good articles) what I'm going to do instead is something USEFUL by example. I'll write a Windows Script Component that you can use to do one of the most common things we all do as Windows DNA developers - use ADO to get recordsets and run stored procedures in SQL Server from our ASP pages.

You'll see that the use of the WSC "PAB.ADOFunctions" is very clean and easy, and can save you a bunch of coding headaches. Hopefully, this will give you the basis to put some of your own coding inventions into WSC's and make them available either here or at other sites to share with others.

I"m a big fan of giving examples "Inline" as I code so the logic flows, so let's look at the actual code of the WSC below, and you'll see my comments in red.

<!-- This is an XML-compliant document, so we need to declare it and follow XML-compliant form. This means our
attributes must be surrounded by quotation marks, our tags must all be closed, and our script which contains illegal XML characters should generally appear inside CDATA blocks -->

<?XML version="1.0"?>

<!-- opening required component tag -->
<component>

<!-- registration information. Need to get a unique classid (use uuidgen, or the Windows Script Component Wizard, which autogenerates one for you -->
<registration
progid="PAB.ADOFunctions"
version ="1"
classid="{7851f441-5344-11d2-a42d-00c04f8ec80b}"
remotable="true" >

<!-- here is the first script block with some nice little functions to popup a message box when component is registered / unregistered -->
<script language="VBScript">
<![CDATA[
function register()
MsgBox("Component 'ADOFunctions' is registered!")
end function
function unregister()
MsgBox("Component 'ADOFunctions' is unregistered!")
end function
]]>
</script>
</registration>

<!--beginning of the public interface definition block. This is where we define our COM interface descriptions for the typelibrary -->
<public>

<!-- here I define the only property for the component, a read/write "connect" to set the connection string -->
<property name="connect">
<get/>
<put />
</property>

<!-- Here we define each method in the component and its parameters-->
<method name="GetRecordSet">
<parameter name="strSQL" />
</method>
<method name="RunSP">
<parameter name="strSP" />
<parameter name="params" />
<parameter name="OutArray" />
</method>
<method name="RunSPReturnRS">
<parameter name="strSP" />
<parameter name="params" />
<parameter name="OutArray" />
</method>
<method name="RunSQL">
<parameter name="strSQL" />
<parameter name="params" />
</method>
<method name="RunSQLReturnRS">
<parameter name="strSQL" />
<parameter name="params" />
</method>
<method name="PageRecords">
<parameter name="rs" />
<parameter name="intPage" />
<parameter name="PageSize" />
<parameter name="NumPages" />
<parameter name="NumRecords" />
</method>
<method name="ViewHelp" />
</public>

<!-- here I define a resource which in this case is just some HTML that displays general help when you call the ViewHelp method which you'll see later down-->
<resource id ="hlpGeneral"><![CDATA[
<Basefont face=Verdana>
<TABLE cellspacing="2" cellpadding="2" border="0" width="80%" align="center">
<TR>
<TD bgcolor="#999999">
<h2 align="center"><font size="-1">ADO FUNCTIONS Windows Script Component</font></h2>
</TD>
<TR>
<TR>
<TD bgcolor="#00CCFF"> <b><i><font size="2">USAGE: set oDB = CreateObject("PAB.ADOFunctions")</font></i></b></TD>
</TR>
<TR>
<TD bgcolor="#CCCCCC">
<p><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Example:</b><br>
oDB.connect = "Provider=SQLOLEDB.1;Data Source=(local);User ID=sa;Initial
Catalog=NORTHWIND"<BR>
sSQL = "Sales by Year"<BR>
sParams = array(array("@Beginning_Date",adDateTime, AdParamInput, 0, "04/01/1998"),
_<BR>
array("@Ending_Date",adDateTime,AdParamInput,0,"04/30/2001")<BR>
set oRS = oDB.RunSPReturnRS(sSQL, sParams, OutArray)</font></p>
</TD>
</TR>
<TR>
<TD bgcolor="#FFFFCC"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
The line that sets the sParams variable looks weird because it calls the
Array function twice. <BR>
First, the function sets up the array of parameter arrays; then, each parameter
calls the function once to <BR>
set up its own little subarray of param attributes. The result is an array
of parameters, with each parameter being <BR>
an array of five entries: ParamName, Type, direction, size, value, e.g.:</font></TD>
</TR>
<TR>
<TD bgcolor="#CCCCCC"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
Set rs = oDB.RunSPReturnRS("sp_BeyondArticlesUpdate", Array( _<BR>
Array("@RETURN_VALUE", adInteger, adParamReturnValue, 0), __<BR>
Array("@ArticleId", adInteger, adParamInput,0,clng(msArticleID)), _<BR>
Array("@InstructorId", adInteger, adParamInput,0,clng(msInstructorID)),
__<BR>
Array("@ArticleCategoryID", adInteger, adParamInput, 0, Clng(msArticleCategoryID)),
_<BR>
Array("@Title", adVarWChar, adParamInput, 100, msTitle), _<BR>
Array("@URL", adVarWChar, adParamInput, 200, msURL), _<BR>
Array("@BodyText", adVarWChar, adParamInput, 1073741823, msBodyText)),outArray)</font></TD>
</TR>
<TR>
<TD bgcolor="#FFFFCC"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Seven
array statements are nested in the first array, and each of the subarrays
sets one parameter.</font></TD>
</TR>
<TR>
<TD bgcolor="#00CCFF">
<div align="left"><font face="Verdana, Arial, Helvetica, sans-serif" size="2">THE
FUNCTIONS:<br>
A:) Set / Get connection string:<BR>
(obj).connect ="your conn string"<BR>
myconnstring = (obj).connect<BR>
1) Run SP with no return recordset and optional output params:<BR>
<i><b>RunSP(ByVal strSP, params, byRef OutArray)</b></i><BR>
2) Run sp with params and return disconnected recordset and optional return
values:<BR>
<i><b>RunSPReturnRS(ByVal strSP, params, byRef OutArray)</b></i><BR>
3) Run prepared SQL text and return recordset:<BR>
<i><b>RunSQLReturnRS(ByVal strSQL,ByVal params)</b></i><BR>
4) Run prepared SQL text with no return recordset:<BR>
<i><b>RunSQL(ByVal strSQL, byRef params)</b></i><BR>
5)Run prepared SQL text and return disconnected recordset<BR>
<i><b>GetRecordset(ByVal strSQL)</b></i><BR>
6) Page large recordset object for display in more than one page:<BR>
<i><b>PageRecords(rs, byRef intPage, byVal PageSize, NumPages, NumRecords)
</b></i></font></div>
</TD>
</TR>
</TABLE>
]]>
</resource>

<!-- That's the end of the interface definitions, now we have our main code block, roughly equivalent to a "Class" in VB-->

<script language="VBScript">
<![CDATA[

' Set the ADO constants we know we'll need
'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adPropVariant = 138
Const adVarNumeric = 139
Const adArray = &H2000
Const adUseClient = 3
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdStoredProc = &H0004
Const adExecuteNoRecords = &H00000080
Const adOpenStatic = 3
Const adCmdText = &H0001
Const adBookmarkCurrent = 0
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'Instantiate the connect property variable and set it to something, provide the 'connect function get and put methods. Note the get_connect and put_connect 'syntax
Dim connect
connect = ""
function get_connect()
get_connect = connect
end function

function put_connect(newValue)
connect = newValue
end function

' Code that follows is the same as you would write in VB or an ASP Page. You can ' also use Javascript (which I prefer, but this code was already written in an 'ASP include file I had written)...
' Run prepared SQL text and return disconnected recordset...
Function GetRecordset(ByVal strSQL)
Dim rs
Set rs = CreateObject("adodb.Recordset")
rs.CursorLocation = adUseClient
rs.Open strSQL, get_connect() , adOpenForwardOnly, adLockReadOnly
Set rs.ActiveConnection = Nothing
Set GetRecordset = rs
End Function
' Run SP with no return recordset and optional output params...
Function RunSP(ByVal strSP, params, byRef OutArray)
Dim cmd, OutPutParms
Set cmd = CreateObject("adodb.Command")
cmd.ActiveConnection = Get_connect()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params, OutPutParms
cmd.Execute , , adExecuteNoRecords
if OutPutParms then OutArray = collectOutputParms(cmd, params)
Set cmd.ActiveConnection = Nothing
set cmd = Nothing
RunSP = 0
End Function
' Run sp with params and return disconnected recordset and optional return values
Function RunSPReturnRS(ByVal strSP, params, byRef OutArray)
Dim rs, cmd, OutPutParms
Set rs = CreateObject("adodb.Recordset")
Set cmd = CreateObject("adodb.Command")
cmd.ActiveConnection = get_connect()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params, OutPutParms
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockReadOnly
if OutPutParms then OutArray = collectOutputParms(cmd, params)
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
Set RunSPReturnRS = rs
End Function
' Run prepared SQL text and return recordset...
Function RunSQLReturnRS(ByVal strSQL,ByVal params)
Dim rs, cmd, OutPutParms
Set rs = CreateObject("adodb.Recordset")
Set cmd = CreateObject("adodb.Command")
cmd.ActiveConnection = get_connect()
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Prepared = true
collectParams cmd, params, OutPutParms
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
Set RunSQLReturnRS = rs
End Function
' Run prepared SQL text with no return recordset
Function RunSQL(ByVal strSQL, byRef params)
Dim cmd, outPutParms
Set cmd = CreateObject("adodb.Command")
cmd.ActiveConnection = get_connect()
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
collectParams cmd, params, OutPutParms
cmd.Execute , , adExecuteNoRecords
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
End Function
' Page large recordset object for display in more than one page ...
Function PageRecords(rs, byRef intPage, byVal PageSize, NumPages, NumRecords)
If intPage < 1 Then intPage = 1
rs.PageSize = PageSize
NumPages = rs.PageCount
If intPage > NumPages Then intPage = NumPages
rs.AbsolutePage = intPage
PageRecords = rs.GetRows(PageSize, adBookmarkCurrent)
If (intPage < NumPages) Or (rs.RecordCount Mod PageSize = 0) Then
NumRecords = PageSize
Else
NumRecords = rs.RecordCount Mod PageSize
End If
End Function
' collect and add input and output params to params object
Private Sub collectParams(ByRef cmd, ByVal argparams, ByRef OutPutParms)
Dim params, v
Dim i, l, u
If Not IsArray(argparams) Then
Exit Sub
end if
OutPutParms = false
params = argparams
For i = LBound(params) To UBound(params)
l = LBound(params(i))
u = UBound(params(i))
If u - l >= 3 Then
If VarType(params(i)(4)) = vbString Then
if params(i)(4) = "" then
v=null
else
v=params(i)(4)
end if
Else
v = params(i)(4)
End If
if params(i)(2) = adParamOutput then OutPutParms = true
cmd.Parameters.Append cmd.CreateParameter(params(i)(0), params(i)(1), params(i)(2), params(i)(3), v)
Else
err.raise "ADO Error", "collectParams(): incorrect number of parameters"
End If
Next
End Sub
' create output params array
Private Function collectOutputParms(ByRef cmd, argparams)
Dim params, v, OutArray(20)
Dim i, l, u
params = argparams
For i = LBound(params) To UBound(params)
OutArray(i) = cmd.Parameters(i).Value
Next
collectOutputParms = OutArray
End Function

' Note the getResource function call to display the help resource block
' You can use this method to display forms, popups, any DHTML..
Public Function ViewHelp()
ViewHelp=getResource("hlpGeneral")
end Function

'Close our CDATA, script and component tags and we're done!
]]>
</script>
</component>

Now when you save this file and right click on it in WIndows Explorer, you'll be offered the context menu choices to register the component and to generate a Type Library. From this point on you treat it just like any other COM component, e.g.:

(This is client-side code:)

set n = CreateObject("PAB.ADOFunctions")
n.connect = "Provider=SQLOLEDB.1;Data Source=(local);User ID=sa;Initial Catalog=NORTHWIND"
'document.write "Using: " & n.connect & "<BR/>"
document.write n.ViewHelp
Set rs = createobject("ADODB.Recordset")
sSQL = "SalesByCategory"
sParams = Array(Array("@CategoryName", adVarWChar, adParamInput, 15, "Beverages"), Array("@OrdYear", adVarWChar, adParamInput, 4, "1998"))
outArray = Array("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
Set rs = n.RunSPReturnRS(sSQL, sParams, outArray)

... etc.

This short arficle barely scratches the surface of what you can do with Windows Script Components - you can call other components, expose the ASP Server model (Response, Request, etc.) and much more. You can even get limited access to the Windows API (boy, that should get the juices flowing with you hackers). The samples in the download include a fully working copy of the WSC (without the comments), a client -side HTM page, and a server - side ASP page, both in VBScript, to show how to use the component. Bring up a reference to it in VB with CreateObject("PAB.ADOFunctions") and you'll see you can look through it in the Object Browser and it even exposes Intellisense when you go to use one of the functions! Don't forget that if your ASP or HTM page passes in ADO constants, they will need to be defined in advance. My client - side page has an "if" test that instantiates them if you don't already have a reference to the ADO type library in your global.asa, and OBJECT tag, or as an include such as ADOVBS.INC.

And finally, if you look at the code in "TestADOFunctions.asp", it uses the GetObject() directive, which can be used to instantiate a WSC even when it is not registered on the server.   All you need to know is where it is in the filesystem, or use Server.MapPath("ADOFunctions.wsc") if it is in the same folder as the ASP script calling it. The client-side example, TestADOFunctions.htm, show how to use the WSC on the client and provide a great DHTML experience without the mess of posting and reloading the page, something that has, at least for IE developers, well-- really become passé.

Enjoy! And remember - If debugging is the process of finding and removing errors from code, this doesn't mean that programming has to be the process of putting them in!

Download the code that accompanies this article.

Peter Bromberg is an independent consultant specializing in distributed .NET solutionsa Senior Programmer / Analyst at in Orlando and a co-developer of the NullSkull.com developer website. He can be reached at info@eggheadcafe.com