Return data from SQL Server stored procedure using ASP page

I have an .asp page I am passing data to which needs to send info to a SQL server stored procedure, 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 procedure 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 stored procedure 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 procedure select but it seems as if it's not getting the data. Any help would be appreciated.

Thanks

Solution

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!