Search code examples
sqlasp-classicado

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

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
Solution

  • 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.