Avatar of Aleks
Flag for United States of America

asked on 

ASP - Display Stored Procedure results

I created a very simple stored procedure with one parameter.

CREATE PROCEDURE [dbo].[report_basic_contacts_01_results]
    @Firmid INT 
   SELECT a.Userid, a.Firmid, a.EmployerId, a.LastNm, a.FirstNm, a.Title, a.email, b.MaidenNm
FROM users a  LEFT JOIN users AS b ON a.employerid = b.userid
WHERE a.usertype = 'corpuser' AND a.firmid = @Firmid  AND a.archivedcont = 0
ORDER BY b.MaidenNm, a.LastNm


Open in new window

In the ASP page I run the Stored Procedure as:


Dim sp_results__Firmid
sp_results__Firmid = "0"
if(Session("Firmid")  <> "") then sp_results__Firmid = Session("Firmid") 


set sp_results = Server.CreateObject("ADODB.Command")
sp_results.ActiveConnection = MM_bluedot_STRING
sp_results.CommandText = "dbo.report_basic_contacts_01_results"
sp_results.Parameters.Append sp_results.CreateParameter("@RETURN_VALUE", 3, 4)
sp_results.Parameters.Append sp_results.CreateParameter("@Firmid", 3, 1,4,sp_results__Firmid)
sp_results.CommandType = 4
sp_results.CommandTimeout = 0
sp_results.Prepared = true
set report_results = sp_results.Execute
report_results_numRows = 0


Open in new window

But then when I try to display the results I get an error and nothing shows:

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.report_basic_contacts_01_results'.

Even though it clearly exists.

if I run it in SQL I do get the results just fine:

EXEC[report_basic_contacts_01_results] 2

The whole data set shows. So its an ASP issue.
Can you please help ?
Web DevelopmentASPSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon