retrieve resultset from MSSQL stored procedure in classic asp

Question

I'm not well versed in ASP, but that's what I've got to work with for the moment. I have a stored procedure that generates a resultset which i'd like to handle in classic ASP. I can see the resultset if I run the SP in SQL Management Studio with the same parameter so I know its working.

The stored procedure takes 1 parameter @part, i've testing with the following code and can see the SP is executed using SQL Profiler, but I get a '500 - Internal server error' returned from ASP instead of a resultset displayed.

How do correctly retrieve the resultset?

Dim fpart, objCommand, objRecordset
fpart = "SMF10320BRNU12"

Set objCommand = Server.CreateObject("ADODB.Command")

With objCommand
  .ActiveConnection = db
  .CommandText = "sp_movements"
  .CommandType = 4
  .CommandTimeout = 240

  .Parameters.Append .CreateParameter("@part", 200, 1, 20)
  .Parameters("@part") = fpart

  Set objRecordset = .Execute
End With

  for each x in objRecordset.Fields
    Response.Write(x.name)
    Response.Write(" = ")
    Response.Write(x.value & "<br />")
  next
  Response.Write("<br />")
  objRecordset.MoveNext

objRecordset.Close
Set objRecordset = nothing

objCommand.Close
Set objCommand = nothing

It might find this useful - [Answer I've given to working with the `ADODB.Command` object](http://stackoverflow.com/a/21698468/692942). — user692942, May 07 '14 at 10:58

fracture · Accepted Answer · 2014-05-07T11:30:56.950

So I figured this out on my own. After digging though some logs I saw an error which I hadn't spotted previously:

Object_doesn't_support_this_property_or_method:_'Close'

Turns out I was incorrectly trying to close the command object: objCommand.Close

My working solution is:

Dim fpart, objCommand, objRecordset
fpart = "SMF10320BRNU12"

Set objCommand = Server.CreateObject("ADODB.Command")

With objCommand
  .ActiveConnection = db
  .CommandText = "sp_movements"
  .CommandType = 4
  .CommandTimeout = 240

  .Parameters.Append .CreateParameter("@part", 200, 1, 20)
  .Parameters("@part") = fpart

  Set objRecordset = .Execute
End With

Do until objRecordset.EOF
  for each x in objRecordset.Fields
    Response.Write(x.name)
    Response.Write(" = ")
    Response.Write(x.value & "<br />")
  next
  Response.Write("<br />")
  objRecordset.MoveNext
loop

objRecordset.Close
Set objRecordset = nothing
Set objCommand = nothing

Note:

This code also has a Do until loop around added to output all the rows from the recordset, but that wasn't strictly necessary to get something output on the page.

EDIT

Edited to reflect comments from Lankymart below, I did need to close the recordset.

Actually you do need to use `Call objRecordset.Close()` to close the `ADODB.Recordset` your issue was `ADODB.Command` doesn't have a `Close()` method. — user692942, May 07 '14 at 10:40
You might also benefit by setting `500-100.asp` script in your error pages so you get detailed output of error messages while developing. — user692942, May 07 '14 at 11:05
Thanks Lankymart, i've updated the answer to reflect your comment. — fracture, May 07 '14 at 11:32

retrieve resultset from MSSQL stored procedure in classic asp

1 Answers1