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 1:
Couple of tips after working with asp-classic for years
- There is no need to create a
ADODB.Connection
you can pass a connection string direct to.ActiveConnection
property of theADODB.Command
object. This has two benefits, you don't have instantiate and open another object and because the context is tied to theADODB.Command
it will be released withSet objCommandSec = Nothing
. - A common reason for
.Execute
returning a closed recordset is due toSET NOCOUNT ON
not being set in your SQL Stored Procedure, as anINSERT
orUPDATE
will generate a records affected count and closed recordset. SettingSET NOCOUNT ON
will stop these outputs and only your expected recordset will be returned. -
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 anArray
.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
Solution 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:
- Do you need to
Open
the connection before callingobjCommandSec.Execute
? - 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.
- 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.