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.

   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
   

Answer 2

Looked at this for a few minutes, and it's been a long time since I've worked with classic asp, but I did see three things to look at:

1. Do you need to Open the connection before calling objCommandSec.Execute?
2. Can you try writing out a string literal inside the loop, that does not depend at all on the recordset... only that you are in fact looping through the code, so see if records are coming back to the recordset.
3. Have you checked the html source, to see if perhaps malformed html is hiding your results? I remember this happening a few times with tables in classic asp loops, where data would be hidden somehow between two rows, or a closing table tag in the wrong place would end the table, and later rows would not be visible.

Answer 3

Although this might not answer OPs question directly, it might help someone else looking for a solution.

recently I had a maintenance job that required me to modify something in a running ASP classic code (which I haven't write in ages). Procedure calls were written the same way as OP did and that wasn't how I did it in the past.

Here is the syntax I used in the past and I think it is a little more clean than other solutions provided here.

The following code shows how to read an output parameter, pass parameters to stored procedure, pass null value to parameter, read record count, and iterate in RecordSet.

dim conn, cmd, rs

set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};Server=servername;Uid=username;Pwd=password;Database=dbname;"

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "procedurename"
cmd.Parameters.Refresh
cmd.Parameters("@nullparam") = null
cmd.Parameters("@strparam") = "1"
cmd.Parameters("@numparam") = 100

set rs = Server.CreateObject ("ADODB.RecordSet")
rs.CursorLocation = adUseClient ' to read recordcount'

rs.open cmd, , adOpenStatic, adLockReadOnly

Response.Write "Return Value: " & cmd.Parameters("@RETURN_VALUE") & "<br />"
Response.Write "Record count: " & rs.RecordCount & "<br />"
while not rs.EOF 
    ' or do whatever you like with data'
    Response.Write rs("colname") & "<br>"
    rs.MoveNext
wend