Return data from SQL Server Stored Proc using ASP page

Question

I have a .asp page I am passing data to which needs to send info to a SQL server stored proc, get the return value, and pass it back to the main page. I currently have the page sending the data and can verify this as the stored proc is an insert and select. So I see the data being inserted. For some reason, the select part is not getting passed back to the page.

This is the store proc the asp page is sending data to:

ALTER PROCEDURE [dbo].[Insertpurchase]
 
      @UserID  bigint,
      @purchaseID bigint,
      @MemberID bigint,
      @DependentID bigint,
      @ServiceDate varchar(20),
      @purchaseDate varchar(20),
      @purchaseNumber bigint,
      @Amount  varchar(20),
      @Status  varchar(20) 

AS

DECLARE @purchaseNo bigint
SET  @purchaseNo = (SELECT  [LastNumber]  FROM [purchaseAdmin] where [id] = @purchaseID );


DECLARE @day varchar(20)
DECLARE @month varchar(20)
DECLARE @year varchar(20)

DECLARE @ServiceOFDate varchar(20)
DECLARE @purchaseOFDate varchar(20)
 

set @ServiceOFDate = SUBSTRING(@ServiceDate, 1, 2)+'/'+SUBSTRING(@ServiceDate, 3, 2)+'/'+SUBSTRING(@ServiceDate, 5, 4);

set @purchaseOFDate = SUBSTRING(@purchaseDate, 1, 2)+'/'+SUBSTRING(@purchaseDate, 3, 2)+'/'+SUBSTRING(@purchaseDate, 5, 4);

insert into [NewPurchase] (
       [UserID]
      ,[purchaseID]
      ,[MemberID]
      ,[DependentID]
      ,[ServiceDate]
      ,[purchaseDate]
      ,[purchaseNumber]
      ,[Amountpurchase]
      ,[Status]
) values (    @UserID,  @purchaseID ,  @MemberID ,   @DependentID ,  CONVERT(datetime,@ServiceOFDate) , CONVERT(datetime,@purchaseOFDate) ,    (@purchaseNo+1)  ,    @Amount ,  @Status        ) ;
  
 select   (@purchaseNo+1)  as purchases

This is the asp page. I am using the code by "Lankymart" from this thread "Using Stored Procedure in Classical ASP .. execute and get results"

<%


    Dim conn_string, row, rows, ary_data
 
  

conn_string = "Provider=SQLOLEDB; Data Source = (Local) ; Initial Catalog = Testonline ; User Id = sa; Password=Test123"

Set objCommandSec = CreateObject("ADODB.Command")
With objCommandSec
  .ActiveConnection = conn_string
  .CommandType = 4
  .CommandText = "dbo.Insertpurchase"
  .Parameters.Append .CreateParameter("@UserID", 200, 1, 10, 1)
  .Parameters.Append .CreateParameter("@purchaseID", 200, 1, 50, 1)
  .Parameters.Append .CreateParameter("@MemberID", 200, 1, 50, 1)
  .Parameters.Append .CreateParameter("@DependentID", 200, 1, 50, 1)
  .Parameters.Append .CreateParameter("@ServiceDate", 200, 1, 10, "01012020")
  .Parameters.Append .CreateParameter("@purchaseDate", 200, 1, 50, "01012020")
  .Parameters.Append .CreateParameter("@purchaseNumber", 200, 1, 50, 1)
  .Parameters.Append .CreateParameter("@Amount", 200, 1, 50, "")
  .Parameters.Append .CreateParameter("@Status", 200, 1, 50, "")
  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
 
          objOutFile.WriteLine row
    ' Return our row data
    ' Row N column 2 (index starts from 0)
    Call Response.Write(ary_data(0, row) & "")
  Next
Else
  ' Nothing returned
 
  Call Response.Write("No data returned")
End If
 
%>

I need the asp page to "Response.Write" with the data from the stored proc select but it seems as if it's not getting the data. Any help would be appriciated. Thanks

Add `SET NOCOUNT ON` to the beginning of the proc to suppress `DONE_IN_PROC` (rowcount) messages generated by the insert. Those will hide the result set returned by the subsequent select unless you invoke NextRecordSet in your ADO classic code (not needed in the ADO.NET world). Also, specify data type `date` for the proc date parameters and use the parameters directly in the insert and remove the substring and convert ugliness. — Dan Guzman, Jul 19 '20 at 15:01
@M-Corp So you used the sample code from [my answer](https://stackoverflow.com/questions/25803116/operation-not-allowed-when-the-object-is-closed-when-running-more-advanced-query) but didn’t read point 2. - *"Setting `SET NOCOUNT ON` will stop these outputs and only your expected recordset will be returned."*? The irony is the answer to this question is in the answer you have linked. — user692942, Jul 19 '20 at 15:43
@Lankymart, Not going to lie. I went into this expecting the same results I would get if I ran a normal stored proc SQL query through a c# or VB.net application. By the time I found your answer I had already spent two days on this and went through 3 other methods. the only thing i really read from your post was "Couple of tips after working with asp-classic for years"... — M-Corp, Jul 19 '20 at 16:01
@M-Corp Answered this particular problem that many times I've actually linked the wrong answer in [the comment](https://stackoverflow.com/questions/62981794/return-data-from-sql-server-stored-proc-using-asp-page?noredirect=1#comment111376467_62981794) meant [this one](https://stackoverflow.com/a/21944948/692942). — user692942, Jul 19 '20 at 16:05
@M-Corp If you do find yourself having to work with Classic ASP and ADODB would recommend reading through those tips for future reference. — user692942, Jul 19 '20 at 16:06

allmhuran · Accepted Answer · 2020-07-19T15:12:41.283

Inside the stored procedure you have two statements, and each statement will return a "result". The result of the insert statement will not be a table of data, but rather a message saying how many rows were inserted. When you call Set rs = .Execute(), the value of rs will contain the first "result", which will be the result given by the insert, not the result given by the select.

There are two ways to solve this. The first is to tell SQL not to return a "result" for the insert statement, so that the only thing you get back is the table of data from the select statement. You can do this by adding the set nocount on statement to the start of your procedure.

You might as well add a begin to the start, and an end to the end of the procedure too, it's a common pattern to follow (but it's not required, so if you don't like it, no problem):

ALTER PROCEDURE [dbo].[Insertpurchase]
 
      @UserID  bigint,
      @purchaseID bigint,
      @MemberID bigint,
      @DependentID bigint,
      @ServiceDate varchar(20),
      @purchaseDate varchar(20),
      @purchaseNumber bigint,
      @Amount  varchar(20),
      @Status  varchar(20) 

AS
begin
   set nocount on; -- < tell SQL not to return the "rows affected" 
   DECLARE @purchaseNo bigint
   -- the rest of your procedure code
end

Alternatively, you can tell the record set that you don't want the first result, by asking for the next set of results, using rs.NextRecordSet

Do one or the other, not both!

Yeah, has been covered [multiple times before](https://stackoverflow.com/a/25824266/692942). — user692942, Jul 19 '20 at 15:41

Return data from SQL Server Stored Proc using ASP page

1 Answers1