title: Return data from SQL Server Stored Proc using ASP page
body: <p>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.</p>
  <p>This is the store proc the asp page is sending data to:</p>
  <pre><code>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
  </code></pre>
  <p>This is the asp page. I am using the code by &quot;Lankymart&quot; from this thread &quot;<a href="https://stackoverflow.com/questions/21915874/using-stored-procedure-in-classical-asp-execute-and-get-results">Using Stored Procedure in Classical ASP .. execute and get results</a>&quot;</p>
  <pre><code>&lt;%
  
  
      Dim conn_string, row, rows, ary_data
   
    
  
  conn_string = &quot;Provider=SQLOLEDB; Data Source = (Local) ; Initial Catalog = Testonline ; User Id = sa; Password=Test123&quot;
  
  Set objCommandSec = CreateObject(&quot;ADODB.Command&quot;)
  With objCommandSec
    .ActiveConnection = conn_string
    .CommandType = 4
    .CommandText = &quot;dbo.Insertpurchase&quot;
    .Parameters.Append .CreateParameter(&quot;@UserID&quot;, 200, 1, 10, 1)
    .Parameters.Append .CreateParameter(&quot;@purchaseID&quot;, 200, 1, 50, 1)
    .Parameters.Append .CreateParameter(&quot;@MemberID&quot;, 200, 1, 50, 1)
    .Parameters.Append .CreateParameter(&quot;@DependentID&quot;, 200, 1, 50, 1)
    .Parameters.Append .CreateParameter(&quot;@ServiceDate&quot;, 200, 1, 10, &quot;01012020&quot;)
    .Parameters.Append .CreateParameter(&quot;@purchaseDate&quot;, 200, 1, 50, &quot;01012020&quot;)
    .Parameters.Append .CreateParameter(&quot;@purchaseNumber&quot;, 200, 1, 50, 1)
    .Parameters.Append .CreateParameter(&quot;@Amount&quot;, 200, 1, 50, &quot;&quot;)
    .Parameters.Append .CreateParameter(&quot;@Status&quot;, 200, 1, 50, &quot;&quot;)
    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) &amp; &quot;&quot;)
    Next
  Else
    ' Nothing returned
   
    Call Response.Write(&quot;No data returned&quot;)
  End If
   
  %&gt;
  </code></pre>
  <p>I need the asp page to &quot;Response.Write&quot; 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</p>