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

Question

I tried to solve this all day long but it doesn't seem to work for me. I would like to execute a command and get the result back to a recordset.

The problem is one of two things: either I'm getting an empty response or there is a problem with my code. I know for sure that this command should fetch few lines from the DB. I added response.write inside the loop, but they are never printed.

Here is the code:

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=X;DATABASE=Y;UID=Z;PWD=W;"
Set objCommandSec = CreateObject("ADODB.Command")
With objCommandSec
    Set .ActiveConnection = Conn
    .CommandType = 4
    .CommandText = "usp_Targets_DataEntry_Display"
    .Parameters.Append .CreateParameter("@userinumber ", 200, 1, 10, inumber)
    .Parameters.Append .CreateParameter("@group ", 200, 1, 50, "ISM")
    .Parameters.Append .CreateParameter("@groupvalue", 200, 1, 50, ismID)
    .Parameters.Append .CreateParameter("@targettypeparam ", 200, 1, 50, targetType)
End With 
    
set rs = Server.CreateObject("ADODB.RecordSet") 
rs = objCommandSec.Execute

while not rs.eof
    response.write (1)
    response.write (rs("1_Q1"))
    rs.MoveNext
wend
response.write (2)

EDITED After revising the code, following @Joel Coehoorn answer, the solution is:

set rs = Server.CreateObject("ADODB.RecordSet") 
rs.oppen objCommandSec

instead of...

set rs = Server.CreateObject("ADODB.RecordSet") 
rs = objCommandSec.Execute

Answer 1

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.

   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 ", 200, 1, 10, inumber)
     .Parameters.Append .CreateParameter("@group ", 200, 1, 50, "ISM")
     .Parameters.Append .CreateParameter("@groupvalue", 200, 1, 50, ismID)
     .Parameters.Append .CreateParameter("@targettypeparam ", 200, 1, 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