ASP Best Practices.

Introduction

This document is designed to pass on those ASP coding practices that have found to be useful or required when coding  sites. The material has been culled from Microsoft, consulting services, trusted sources in magazines and  developers and most importantly from our experiences with actual deployment. 

One disadvantage to the web is that as many sources of good information exist, so do many sources of bad information. Before implementing a particular tip or technique carefully evaluate:

1. The source of the information. Is it creditable? Do they have data to back it up? 
2. What is the actual performance or safety gain. In doubt experiments under a significant load should be done to insure that the technique or tip scales under "real" conditions. 

Closely following the standards in this document should minimize the most common difficulties resulting from ASP coding. 

Use of Proper File Extensions

1. Never put ASP or other server side scripting code in files other than .ASP. Code in non-ASP files (e.g., .inc) exposes the site to several security holes. If using the FrontPage Server Extensions, put include files in the "_private" directory for added security.
2. If there are not any server side scripts in a file, name the file .HTM. The web server will skip ISAPI calls, thus serving up the page more quickly.
3. Global.ASA specifically is as safe as an ASP file.
4. Dead files can lead to places for people to hack. This is especially true for files other then .HTM or .ASP. .LOG files (for example generated by FTP) can provide details of your file structure and file names best not shared with the world. It is strongly recommended that all dead files should be removed from the web server. 

Type Notation

1. Use proper Hungarian notation for variables and objects. The purpose of Hungarian notation is to be able to interpret the type and purpose of a variable. For local and public variables use mixed case of an initial lowercase Hungarian prefix followed by at least one uppercase letter .
2. See table of prefixes below:
   In general programmers should choose and consistently follow either the long or short Hungarian notation as below:

   1 letter	3 letter	Type	Example
   s	str	string	sLastName, strFirstName
   i	int	Integer	iLoopVar, intQuantity
   l	lng	Long	lCountofRows, lngRecordsAffected
   b	bln	Boolean	bSold, blnFileClosed
   d	dbl	Double	dNationalDebt, dblStarsinUniverse
   c	cur	Currency	cAmount, curPrice
   t	dt	DateTime	tDOB, dtApproved
   o	obj	Objects	oRS, objConnection
   x	x	Structures or other	xOblique, xFiles

   
   
3. Hungarian notation can also be used with form objects to make forms more clear, although many programmers choose to use all uppercase meaningful names for POST type forms and very short uppercase names for GET type forms as most of the HTML form elements are variations on <INPUT>.

   Prefix	Form Component
   txt	Input Box
   chk	Checkbox
   opt	Option/Radio Control
   cmd	Button
   img	Image
   lbl	hidden Input

   
   
4. Use 'c' + all upper case for constants. Constants should be used whenever possible as they have lower overhead then variables. Some expmles of constants:
   Const cPI = 3.14159
   Const cSITENAME = "my site" 

Meaningful Variable and Object Names

1. Variable names should be meaningful. Combined with the use of Hungarian notation, this leads to clear, self documenting code where the purpose and type of each variable is obvious from inspection.
2. Clear code will speed up the process of maintenance and 'feature' debugging. 
3. Examples:
   

   Good	Bad
   strCompanyName	com
   intCartCount	CT
   lngCustomerID	cID
   blnLogedIn	ShpC
   dblGrossWeight	gWt
   curGrandTotal	t
   objCookieCart	oCook

   
   
4. Likewise, it is good to use prefixes for filenames of pages with similar functions. For example, all of the customer maintenance pages could start with the prefix 'cust' e.g. custNew.ASP and custUpdate.ASP.

Use of Scope

1. For IIS5 avoid the use of Public except when data needs to be transferred between page invocations use Dim instead to save processing overhead and memory.
2. Use 'option explicit'. 
3. Keep variables local (inside a sub or function) whenever possible for speed and efficient memory usage. The speed is gained as the variables will be found within the current scope limiting searching of the entire name space.
4. Declare (and comment) all variables at the top of each scope range. Groups of declarations at the beginning of the scope is more efficient.
5. Remember to initialize values before using them to set their type and avoid unpleasant involuntary type conversion issues.
6. Likewise, it is good practice to explicitly convert one type to another using the provided conversion functions.

Application Scope

1. Use arrays to store cached information that changes very infrequently. For example locale information or shipping methods. Since this data can be loaded in Application_OnStart() and is very unlikely to change from DB this gives an immediate performance boost. Be wary however, as very large array objects are copied from application scope to page scope when referenced and this operation can take time. 
2. Do not persist the dictionary object , VB collections, property bags and other such objects in application scope. In general though these objects offer a great deal of flexibility and offer handy methods they are: 
   • Not stable in application scope (particularly the dictionary object) and can cause sites to run slowly or crash.
   • Significantly more resource intensive when compared to arrays. The ratio of stored bytes to overhead bytes is generally poor for the kind of data most frequently persisted in most  applications.
3. In general, with the exception of special objects which is specifically designed to run in application scope, objects should not be persisted in application scope. This does not mean that local variables can't be created in order to generate (for example) arrays used to store information retrieved from various COM objects. 
4. Many COM objects not designed to be run in application scope (apartment threaded instead of both threaded) can cause thread contention problems and reduce performance. For best result COM objects that must be instantiated in application scope should be done on a "just in time" basis, used and then immediately explicitly disposed (ADO objects are good examples of this.). Remember that VB objects should not be persisted in application scope as they are compiled as Single Threaded Apartments (STA) and thus will exhibit the same scalability difficulties as if Sessions were used (e.g. thread affinity, serialization to that thread, and context switching for each instance). 

Sessions are very strongly discouraged

1. IIS4 the default state is sessions are enabled. In IIS5 the default state is that sessions are disabled.
2. Sessions are generally discouraged. 
   • Sessions have thread affinity, hence processor affinity. Thus once a session is attached to a thread running on a specific CPU, CPU utilization can become lopsided, defeating the CPU balancing features in IIS. 
   • Sessions can fail when confronted with certain types of proxy servers e.g. AOL. 
   • When a site is scaled by adding servers and using (for example) Cisco Local Director to load balance your site; sessions present a problem. Sessions are machine specific. Information in sessions stored on one machine is not available if Local Director switches an incoming request to a different machine then the original request. If this is kludged around in code by dynamically generating links to point to the specific machine, load balancing is essentially defeated. 
   • Likewise, using the Cisco Local Director sticky bit is defeated by some proxy server implementations, e.g. AOL, so that workaround is not a good one. 
   • Servers in a farm cannot be brought out of service for maintenance without causing loss of sessions. 
   • The memory usage patterns differ between the implementation of sessions and IIS causing resource conflicts. 
   • Sessions enabled on the server use resources even if they are not used. 
   • Session-less sites are more conducive to book marking as the bookmarks will refer to the "site" rather then a specific server. Thus if a server is taken out of service the bookmark will still work.
   • Data placed in the session scope is not de-allocated until the session is terminated or times out. This results in poor resource allocation.
3. Session state may be turned off on a page by page basis by embedding:
   <% @EnableSessionState = False %>
   The performance gain can be significant on pages containing <FRAMESET> elements as internet explorer will allocate a thread or process for each frame in parallel. Having session state enabled forces IIS to respond to the requests serially thus offsetting the advantages IE gave the frame set. 
4. Sessions can be disabled on an entire server if desired, this can cause additional resources to be available.
5. Alternatives to sessions include (in no particular order)
   1. Form Hidden Fields are also a good method, the disadvantage of which is that they are only persisted when page transitions happen via the posting mechanism.
   2. QueryString variables can also be used to pass information. The disadvantage to passing query strings is that the information passed can not exceed 216 characters including the URL on some browsers and through some proxy servers, and the information passed is very visible to the user. The advantage of passing in a query string is that the passing can happen either by a hyperlink, form post or both.
   3. Other cookies can be generated by Response.Cookie() and retrieved by Request.Cookie().  Be aware, ASP provides no encryption of cookies so the habit of storing credit card information in plain text in a cookie is an anathema. 
   4. You can encode data into XML using ADO. The resulting XML stream can be written to the page in between comments, stored in a DB, or written to the client in a series of cookies. The XML can be turn back into a recordset and used in your pages. You can also use an XML DOM object to make and iterate XML documents. 

Logical Design

1. Use a function when there is a single result and the the result is meant to be used or tested in line. E.g. The vbScript function trim() is usually used in line and has no effect when used as a statement as the trim function has no output arguments. 
   
   For routines that have success/failure indicators, converting them to functions that return a Boolean or Integer success or failure indicator allows the code to be called from with in an IF statement, thus making the code more clear.
   
   'Sample function declaration, implicitly returning success/failure as a long integer
   'Any non-zero number is an error, 0 = Success
   Function DoProcessingofOrder()
   
   'This can be used as follows
   If DoProcessingofOrder() > 0 Then 
       'Redirect to somewhere else
   End if
2. Use a procedure when multiple output arguments are required and/or no return is desired.
3. Use functions and subs to create re-usable code blocks and to chunk your logic into reasonable blocks of functionality. 
4. Good chunking of code into subroutines and functions will make the code more maintainable and speed debugging (as you can step over an entire subroutine if it is known to work.)
5. Chunking also allows the clever use of includes to share code across pages while maintaining a single version. 

Use of Includes

1. Server side includes are preferred over FrontPage includes as they are faster and more stable. FrontPage includes have additional functionality that can be handy such as stripping out unneeded HTML elements e.g. <BODY> and <HEAD> from HTML includes.
2. For all server side notations e.g. #INCLUDE are processed before any of the script on the page, hence the following code will not have the results one might expect: 
   If blnLogedIn Then
       %><!-- #INCLUDE FILE='IsCust.Asp' --><%
   Else
       %><!-- #INCLUDE FILE='DoLogin.Asp' --><%
   End If
   Both files are included and then the script is processed!
3. Server side includes are a powerful tool for organizing code into libraries, however moderation is urged as each server side include consumes CPU resources as the pages are "composed" by merging the includes into the main document. Some reasonable (1-3) number of includes is OK, too many (>5) can seriously degrade performance.
4. Since it is hard to debug included code and even harder to debug code in nested includes the use of nested includes should be minimized. In some circumstances the interpreter/compiler may have difficulty resolving deeply nested includes properly.
5. In general, here are some good guidelines:
   • Avoid making huge utility includes, this avoids the amount of code that must be parsed for each page.
   • Only put code into an include that will be shared between multiple files. The practice of building an entire site using includes to hold all of the code results in poor performance and scalability. 
   • Avoid nesting entirely if possible.

Object References

1. When possible, refer to objects or members of a collection only once. Each object or collection reference is expensive in terms of computation when compared to referencing the same data in a variable. 
   
   Slow:
   if ObjCustomer.Address2 <> "" then 
       Response.Write ObjCustomer.Address2
   end if
   
   Faster, use the reference on once :
   strCustAddress2 = ObjCustomer.Address2
   if strCustAddress2 <> "" then 
       Response.Write strCustAddress2
   end if
   
   Faster Still, use the reference only once and use the Len() function to test: 
   strCustAddress2 = ObjCustomer.Address2
   if Len(strCustAddress2) > 0 then 
       Response.Write strCustAddress2
   end if
    
2. For example, form and query string collection values stored in the request object should be copied into a variable for use:
   For Request objects particularly, this is handy as it makes it so if the name of the control on the form changes, the code need only be modified in one location.
   
   dim strLastName    'Customer Last Name
   strLastName = Request.Form("txtLastName")
   
3. If possible and the object supports it  use <OBJECT> elements instead of Server.CreateObject() as IIS delays creating the object until it is first used, where as CreateObject() causes the object to be created whether is is subsequently used or not. The reason this is not done, is that <OBJECT> tags are very hard to debug and can not be error trapped by the programmer. A low level IIS error is generated instead that causes a 40x error of some sort depending on the machines configuration. So in practice <OBJECT> tags are only used if 1) the object is internal or 2) there is a high degree of confidence that the object can be created without error. The <OBJECT> tag is usually not used for connection oriented objects such as ADO or objects that reside in MTS.
4. All objects created in a page should be explicitly disposed. While IIS will "cleanup" allocations as the page passes out of scope, disposing of objects as soon as possible will result in resources being made available sooner.
5. Particularly for objects residing in MTS or for ADO objects, which are cached by the server, a strategy of "just in time" instantiation is recommended, creating objects shortly before they are needed, using them and then explicitly disposing them. This is effective in minimizing resource use because the "expense" of object creation is moderated by IIS/MTS caching frequently used objects thus proving a pool of frequently used objects that need not be created from "scratch" when needed. In general create object late and dispose of them early.
6. If an object has a method to open it e.g., .OPEN, the corresponding close method, e.g., .CLOSE should be invoked before it is disposed. This is particularly important for ADO objects. In theory, the ADODB implementation is proof against data corruption caused by sudden de-allocation, however explicit use of close provides for an orderly object shutdown, particularly for objects that are in a transaction or are updateable.
7. The Server Side Variables collection is built dynamically by IIS the 1st time it is referenced on a page. If you can capture value of key server side variables (such as browser type etc) on your page once and then propagate them via another method, you can gain a performance increase. 
8. Likewise the MapPath() method is expensive. Consider, capturing the path of the root web in an application variable for re-use on all of your pages. The MapPath() call implicitly causes the Server Variables collection to be built.

Object Validity

1. Never assume an object is valid. 
2. Test all objects after creation for being nothing:
   Set objRS = Server.CreateObject("ADODB.RecordSet")
   If objRS is Nothing Then
       'Take steps, redirect, etc.
   End if
3. Some cases an application should handle gracefully are:
   • DB is unavailable
   • DLL is creatable
   • Method calls to DLL succeed
   • Objects are still in scope
   • Collection items exist

ASP Coding Style

1. Use 'option explicit' to catch undeclared and misspelled variables. This prevents errors as follows:
   
   dim colObject    'as Integer
   colObjects=0
   For Each Object in Collection
       colObject=colObjects + 1
   Next 
   
   Notice mixing of singular and plural as variable is misspelled in two places. This results in the wrong count being returned from the loop.
2. In addition, use of option explicit can cause ASP pages to run faster.
3. Explicitly using <% @LANGUAGE = vbScript %> is not required on  web servers as it is always set to that as the default. Explicitly declaring it is an expensive call as ASP must stop and check the default language for scripting calls. If the programmer does not have explicit control over the server on which the pages are running e.g. the site is hosted by a 3rd party, the use of @Language is still recommended as forces the script on the pages to run correctly.
4. Use good script coding style:
   • Indent blocks of code
   • Use blank lines around blocks
   • Declare one variable per line (faster) and document variables
   • Use comments when required to document tricky or unusual code sections
5. Put ASP delimiters '<%' and '%>' in the left hand margin to make code blocks easy to spot (except for inline blocks e.g. <% = strVar %>).
6. Combine multiple sequential Response.Write lines into one larger single statement when it makes sense to do so. This performance gain can be offset if extensive string concatenations are required.
   
   Slower: (one per line)
   Response.Write Name & "<br>"
   Response.Write Street & "<br>"
   Response.Write City & ", " & State & ", " & Zip & "<br>"
   
   A little faster (de-reference the object) :
   With Response
       .write Name & "<br>"
       .write Street & "<br>"
       .write City & ", " & State & ", " & Zip & "<br>“
   End With
   
   Faster: (Concatenate strings)
   Dim strHTML
   strHTML = strHTML & Name & "<br>"
   strHTML = strHTML & Street & "<br>"
   strHTML = strHTML & City & ", " & State & ", " & Zip & "<br>“
   Response.Write strHTML
   
   Faster Still (Use continuation character):
   Response.Write Name & "<br>" & _
                  Street & "<br>" & ", " & _
                  State & ", " & _
                  Zip & "<br>"
   
   Even Faster (all on one line): 
   Response.Write Name & "<br>" & Street & "<br>" & ", " & State & ", " & Zip & "<br>"
   
7. Avoid extensive string concatenations, especially in loops. The total cost of string concatenations is given by the formula:
           Cost = N(N+1)/2 
8. Likewise, use of the ReDim operator should be limited. Resizing an array is done by allocating a new block of memory of the size desired and then copying the bytes to the new array and finally, de-allocating the old array after reassigning the variable pointer to the new array. If an array must be grown dynamically, growing an array by jumps of multiple elements and keeping a variable to serve as the actual number of elements in use may prove more efficient:
   
   Const cGROWBY = 10   'Number of elements to grow by at one time
   Dim intArrayMax      'Actual Number of Element in Use
   Dim myArray(cGROWBY)
   
   '... processing a data stream whose current value is in myValue
   
   intArrayMax = intArrayMax + 1 'One More Array Element is Needed
   if intArrayMax > UBound(myArray) then 
       ReDim Preserve myArray(UBound(myArray) + cGROWBY)
   end if
   
   myArray(intArrayMax) = myValue
   
   '.... and so on
   
   In this example the array is grown by 10 each time, some experimentation should be done to determine the best amount to grow by each time. In addition, the initial size of the array can be tuned to make the number of allocations minimized while not wasting memory on unused elements. Again, the best strategy is to attempt to compute how much memory is required in advance, if possible.
9. Arrays are the one of the most efficient storage mechanisms, in general they use much less memory then dictionary objects, collections, or property bags. In addition, a clever design using arrays will usually be faster.
10. Avoid context switching.
    • Context switching occurs every time the switch is made between vbScript and HTML code blocks. Each context switched requires a significant amount of time and resources on the server. 
    • Following the previous rule extensive use of <% =VariableName %> should be avoided as well. 
    • Pool or combine small blocks together to make larger blocks of HTML and script. 
    • Avoid switching script types e.g. between vbScript and jScript as this context swap is very expensive as the number of precompiled pages in the cache is limited.
11. While making pages out of process can provide protection against bad page behavior and be helpful in debugging, running pages in process with IIS can result in performance gains. A careful analysis of trade-offs should be done before deciding if a page or set of pages should be run in process or out of process.
12. Never assume that data returned from the browser is correct. Always check order numbers, prices, SKUs, etc against  to make sure data is correct. For example:
    orderhistory.asp?orderid=1234
    
    Does Order 1234 belong to the current customer? It should be checked against . Checking the referring URL is inadequate as that URL may be a) a proxy server "fronting" for 100s of people 2) spoofed 3) wrong for other reasons.
13. Use of white space and indenting significantly improves the maintainability of the code, while costing almost nothing in resources.
14. Use the Len() function to test for empty strings. Much faster. 
    Slow:
    If strX <> "" then 
    
    Faster:
    If len(strX) > 0 then 
    
15. If you are using IIS 5 use Server.Transfer or Server.Execute rather then Response.Redirect. Response.Redirect generates a client side redirection header while, the other two calls perform server side redirection which is much faster.
16. For IIS4 (where buffering is off by default) Use Response.Buffer = True on all ASP pages.
    • Buffering increases performance by decreasing the number of "writes" to the client
    • Allows the page to be redirected in case of an error.
    • However, be wary of  un-moderated buffering on time consuming pages with large outputs (such as large tables).
    • In buffered output you can use Response.IsClientConnected after the first Response.Flush to test to see if the client is still there, and abort by using Response.End if not. Warning: for IIS4 and below the IsClientConnected call may return incorrect values unless some information has been flowed to the client.
    • Strategic use of Response.Flush can keep pages from timing out. For large tables, it may be necessary to additionally end and restart the table. For example:
      
      lngRowCount = 0
      Response.Write "<table width='100%'>"
      while not rs.eof 
          lngRowCount = lngRowCount + 1
          if lngRowCount Mod 100 = 0 then 
              Response.Write "</table>"
              Response.Flush
              Response.Write "<table width='100%'>"
          end if
      
          'Write out row
      
      wend
      
      (See also GetString() in the ADO section)

Dead Code/Comment Removal

1. Remove all dead code. ASP is interpreted, so dead code, even commented out makes the page "heavy" e.g. uses more memory in the page cache, takes longer to load and thus is more processor intensive.
2. Sadly, this rule applies to excessive HTML comments as well as these are flowed to the client browser. 
3. In general comments should be stripped from production code to save "weight" and to avoid reveling secrets about the way the code works. 

Form Design Issues

1. Forms should post to themselves. This has several advantages:
   • when data entered is incorrect (user data entry error) as the fields on the form can be marked as being incorrect or missing easily. 
   • secondly, the number of redirects are minimized
   • lastly, all of the business logic related to a single function is contained in the page.
2. Data entry items on a form should have the correct tab order and be arranged in an order that makes sense to data enter.
3. Data entry fields should be aligned to each other as much as possible to make the data entry experience as pleasant as possible.
4. Use client side scripting (JavaScript for maximum compatibility) to validate user input. This has the effect of reducing round trips to the server. However, it should never be assumed on the server side that the data is correct when it is received. Client side validation supports but does not replace good data validation on the server.

Check HTML for Correctness

1. An automated HTML checking tool should be applied to all pages to make sure that the HTML on the page is correct.
2. Particularly troublesome items include
   • Missing form elements
   • Malformed tables, particularly incorrect spans
   • Missing end tags for matched elements e.g. missing </tr> or </tt>
   • Incorrect target references for frames
   • Invalid nesting of tags
   • Invalid HTML can cause slow loading pages as browsers struggle to interpret "do as I mean, not as I say" code.
3. It is very helpful to reformat pages so that the following HTML standards are followed:
   • Use all lowercase HTML tags. This is a change over old advice that called for all uppercase HTML elements. The new standard is based on the new xHTML standard that makes all uppercase takes depreciated. So it would be good to get into the habit of using all lower case elements now.
   • ASP Code should be title cased
   • vbScript code should use the correct mixed case as in the sections above

Error Handling

1. In general, error prevention is much better than error handling. The error trapping mechanism is computationally expensive.
2. There are certain occasions where explicit error handling is strongly recommended:
   • When using the file system objects
   • When invoking 3rd party objects such as controls or applets 
   • When opening ADO connections or recordsets
3. When setting an error trap using On Error Resume Next make sure to turn it off as soon as possible with On Error Goto 0
4. Setting an error trap without checking Err.Number after calls is not recommended. Once a page has an error it is usually disadvantageous to continue processing.

ADO

ADO is a powerful cursor connection library useful in adding functionality to your sites. Learning the best ADO practices and the associated techniques can make a significant difference in how your pages perform. We suggest visiting some of the resource links at the bottom of this article to learn more.

1. Use OLEDB for connecting to databases
   • OLEDB uses native database drivers to speak to the database instead of requiring ODBC translations
   • OLEDB is nearly twice as fast with large numbers of records
     
2. Here is the sample connection syntax for OLEDB and ODBC for several data source types:
   

   Data Source	ODBC DSN	OLEDB DSN
   SQL*Server	Driver=SQL Server; Server=myServer; Database=myDatabase; UID=student; PWD=student;	Provider=SQLOLEDB.1;Data Source=myServer;Initial Catalog=myDatabase;User ID=student;Password=student;
   MS Access	Driver={Microsoft Access Driver (*.mdb)}; dbq=path\myDatabase.mbd	(n/a)

3. Use GetString(), GetRows(), or disconnected recordsets. These methods reduce the number of trips across the network required to retrieve database data. If your recordset does not need formatting (consider doing that sort of work in the query or in a stored procedure) using GetString cleverly to output tables quickly for tables containing few rows (About 50, if there are many more rows the size of the string variable created becomes an issue, test typical outputs using various techniques to see which is faster in your case).
   
   Response.Write "<table border='1'><tr><td>"
   S = rs.GetString( , , "</td><td>","</td></tr><tr><td>", " " )
   S = Left( S , Len(S) - 8) 'Remove extra <tr><td> 
   Response.Write S & "</table>"
4. Reduce time that database connections are open. Don’t open database connections until immediately before you need them, and close then dispose them as soon as possible. This returns the connection resources to IIS which maintains a pool that can be rapidly be reused.
5. Use connectionless fire hose cursors when possible: Client Side, No Locks, Not Updateable. In general use the very fewest connection features possible to make the "cheapest" cursors.
6. Do not re-use ADO command objects in the same page. There is a bug that can cause your pages to crash. Dispose and re-create your command objects or better yet use the techniques below.
7. If you need to fetch records from multiple queries serially, then use the following technique:
   
   mySQL = "Select * From A;Select * From B" 'Notice the two queries separated by a semi-colon (;)
   Create your record set... The recordset object will point to the 1st cursor. Then use
   rs.NextRecordset  
   This moves to the next cursor. You can not go back to make sure you have done all that you need to with the 1st cursor. This technique is much faster and more resource efficient then using multiple recordsets or the same one serially.
8. Use stored procedures
   • Stored procedures represent server side business logic and can be used from VB or ASP.
   • Stored procedures are much faster then constructing queries "on the fly"
   • Stored procedures can be modified/tested with out recompiling the program or editing the script

   How to run stored procedures from VB/ASP
   The general strategy for performance is to avoid the command object unless you need to create stored procedures on the fly with dynamic parameters or you are pushing a larger number of parameters at the servers.. 
   
   Dim lngRecordsAffected 
   Dim strSQL 
   Dim objADORS     'ADODB.Recordset
   Dim objADOConnection     'ADODB.Connection
   
   '... create and open connection and create recordset object
   
   Instead of creating a command object build a command string that includes the stored procedure and all of its parameters.
   
   strSQL = "usp_DoIt2It " & para1 & "," & para2   'or whatever is required.
   
   For stored procedures that do not return rows:
   Add the 'call' verb and braces:
   strSQL = "{Call " & strSQL & "}"
   
   objConnection.Execute strSQL, lngRecordsAffected , adCmdStoredProc
   'You can check lngRecordsAffected to see how many rows were affected
   
   'For stored procedures that return rows:
   Set rs = objCN strSQL, lngRecordsAffected , adCmdStoredProc
   
   Notes:
   Simple and very fast (especially so in SQL*Server 7.x or higher). 
   
   Notice that the optional parameter adCmdStoredProc will improve performance somewhat as well. 
   
   For all parameters the rules are the same as for all other queries, strings and dates must be delimited on either side with either quotes or apostrophes.

Resource Links

Here are some recommended links

Valtara Digital Design  http://www.valtara.com/csc123/   
Copyright 1999, 2001, Valtara Digital Design, Blitzkrieg Software