Using a stored procedure in classic ASP .. executing and retrieving results

Question

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

The problem is one of two things: either I get an empty answer, or there is a problem with my code. I know for sure that this command should display several rows from the database. I added response.writeinside the loop, but they never print.

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's answer, solution:

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

instead

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

Answer 1

, asp, , :

• Open objCommandSec.Execute?
• , ... , , , .
• html, , HTML- html? , asp, - , , .

Answer 2

asp-classic

• ADODB.Connection, .ActiveConnection ADODB.Command. : , , ADODB.Command, Set objCommandSec = Nothing.
• .Execute SET NOCOUNT ON, SQL, INSERT UPDATE , . SET NOCOUNT ON , .

• ADODB.Recordset , , , . 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
  

Answer 3

OPs , - .

, - ASP ( ). , OP, , .

, , , , .

, , , null , 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