Using Stored Procedure in Classical ASP .. execute and get results

Couple of tips after working with asp-classic for years

1. There is no need to create a ADODB.Connection you can pass a connection string direct to .ActiveConnection property of the ADODB.Command object. This has two benefits, you don’t have instantiate and open another object and because the context is tied to the ADODB.Command it will be released with Set objCommandSec = Nothing.

2. A common reason for .Execute returning a closed recordset is due to SET NOCOUNT ON not being set in your SQL Stored Procedure, as an INSERT or UPDATE will generate a records affected count and closed recordset. Setting SET NOCOUNT ON will stop these outputs and only your expected recordset will be returned.

3. Using ADODB.Recordset to cycle through your data is overkill unless you need to move backwards and forwards through and support some of the more lesser used methods that are not needed for standard functions like displaying a recordset to screen. Instead try using an Array.

   Const adParamInput = 1
   Const adVarChar = 200
   Dim conn_string, row, rows, ary_data
   
   conn_string = "PROVIDER=SQLOLEDB;DATA SOURCE=X;DATABASE=Y;UID=Z;PWD=W;"
   
   Set objCommandSec = CreateObject("ADODB.Command")
   With objCommandSec
     .ActiveConnection = conn_string
     .CommandType = 4
     .CommandText = "usp_Targets_DataEntry_Display"
     .Parameters.Append .CreateParameter("@userinumber", adVarChar, adParamInput, 10, inumber)
     .Parameters.Append .CreateParameter("@group", adVarChar, adParamInput, 50, "ISM")
     .Parameters.Append .CreateParameter("@groupvalue", adVarChar, adParamInput, 50, ismID)
     .Parameters.Append .CreateParameter("@targettypeparam", adVarChar, adParamInput, 50, targetType)
   
     Set rs = .Execute()
     If Not rs.EOF Then ary_data = rs.GetRows()
     Call rs.Close()
     Set rs = Nothing
   End With
   Set objCommandSec = Nothing
   
   'Command and Recordset no longer needed as ary_data contains our data.
   If IsArray(ary_data) Then
     ' Iterate through array
     rows = UBound(ary_data, 2)
     For row = 0 to rows
       ' Return our row data
       ' Row N column 2 (index starts from 0)
       Call Response.Write(ary_data(1, row) & "")
     Next
   Else
     ' Nothing returned
     Call Response.Write("No data returned")
   End If