For example, let's say we've set up usp_AddEmployee with the same parameters as before. The page we use to run it is the same as before up to here:

With cmdEmployee
  .ActiveConnection = strConn
  .CommandText = "usp_AddEmployee"
  .CommandType = adCmdStoredProc

Then we'd call the Refresh method.

  .Parameters.Refresh

This tells ADO to ask the data store to provide the details of each parameter, and it creates the Parameters collection for you. You can then fill in the values:

  .Parameters("@Emp_Id") = Request.Form("txtEmpID")
  .Parameters("@FName") = Request.Form("txtFirstName")
  .Parameters("@MInit") = Request.Form("txtInitial")
  .Parameters("@LName") = Request.Form("txtLastName")
  .Parameters("@Job_ID") = Request.Form("lstJobs")
  .Parameters("@Job_Lvl") = Request.Form("txtJobLevel")
  .Parameters("@Pub_ID") = Request.Form("lstPublisher")
  .Parameters("@Hire_Date") = Request.Form("txtHireDate")

Notice that we haven't had to create any parameters, including the one for the return value.

This may look like a really good shortcut, but you should be aware that it imposes a performance penalty, since ADO must query the provider for the details of the parameters for the stored procedure. Despite this it's exceedingly useful, especially if you are having trouble getting the values for your parameters correct.

In fact, you can build a little utility to be used as a development tool, which does a refresh and then constructs the Append statements, ready for you to paste into your code. It might look something like this - an ASP page called GenerateParameters.asp:

The code for this is pretty simple. The first thing to do is include the connection string, plus another file for the ADOX constants:

<!-- #INCLUDE FILE="../Include/Connection.asp" -->
<!-- #INCLUDE FILE="../Include/ADOX.asp" -->

Then we create a form, specifying that its target will be the ASP page called PrintParameters.asp:

<FORM NAME="Procedures" METHOD="Post" ACTION="PrintParameters.asp">
 Connection String:<BR>
 <TEXTAREA NAME="txtConnection" COLS="80" ROWS="5">
  <% = strConn %>
 </TEXTAREA>
 <P>
 Stored Procedure:<BR>
 <SELECT NAME="lstProcedures">

We then use ADOX to get a list of stored procedures from the SQL Server, and we construct a list box containing the stored procedure names:

<%
 Dim catPubs
 Dim procProcedure
 Dim strQuote

' Predefine the quote character
 strQuote = Chr(34)

 Set catPubs = Server.CreateObject("ADOX.Catalog")

 catPubs.ActiveConnection = strConn

 For Each procProcedure In catPubs.Procedures
   Response.Write "<OPTION VALUE=" & _
          strQuote & procProcedure.Name & _
          strQuote & ">" & procProcedure.Name
 Next

 Set procProcedure = Nothing
 Set catPubs = Nothing
%>
</SELECT>
<P>
<INPUT TYPE="Submit" VALUE="Print Parameters">
</FORM>

It's a simple form, with a TEXTAREA for the connection string, and a SELECT for the stored procedures. What you won't have seen before is the ADOX bits. ADOX is the ADO Extensions for Data Definition and Security, and gives you access to the catalog (or metadata) of a data store.

More details on ADOX can be found in the ADO Programmer's Reference, from Wrox Press. Either the 2.1 version or the 2.5 version will be OK for ADOX.

In the above example, we are using the Procedures collection, which contains a list of all stored procedures in a data store. When you press the Print Parameters button, you get the following:

The parameter lines can simply be copied from this and pasted into your code. Easy huh! The code's not very difficult either. At the very beginning we have an include file you won't have seen before. This contains several functions that convert the ADO constants (such as data type, parameter direction and so on) into string values:

<!-- #INCLUDE FILE="../Include/Descriptions.asp" -->

Next we have a set of variables, the extraction of the user request, and the creation of the Command object:

<%
 Dim cmdProc
 Dim parP
 Dim strConnection
 Dim strProcedure
 Dim strQuote

' Get the connection and procedure name from the user
 strQuote = Chr(34)
 strConnection = Request.Form("txtConnection")
 strProcedure = Request.Form("lstProcedures")

' Update the user
 Response.Write "Connecting to <B>" & strConnection & "</B><BR>"
 Response.Write "Documenting parameters for <B>" & _
        strProcedure & "</B><P><P>"

 Set cmdProc = Server.CreateObject("ADODB.Command")

' Set the properties of the command, using the name
' of the procedure that the user selected
 With cmdProc
  .ActiveConnection = strConnection
  .CommandType = adCmdStoredProc
  .CommandText = strProcedure

We then use the Refresh method to automatically fill in the Parameters collection:

  .Parameters.Refresh

We can now loop through the collection, writing out a string that contains the details for creating the parameter:

  For Each parP In .Parameters
    Response.Write ".Parameters.Append " & _
           "(" & strQuote & parP.Name & _
           strQuote & ", " & _
           DataTypeDesc(parP.Type) & ", " & _
           ParamDirectionDesc(parP.Direction) & _
           ", " & _
           parP.Size & ")<BR>"
  Next
 End With

 Set cmdProc = Nothing
%>

The functions DataTypeDesc and ParamDirectionDesc are found in the Descriptions.asp include file.

The Descriptions.asp include file, along with the other sample files, can be found on the supporting Web site, at http://webdev.wrox.co.uk/books/2610

This is a really simple technique, and shows a good use for the Refresh method. Well, anything that saves typing has to be good, doesn't it?