ASP - Display Stored Procedure results
I created a very simple stored procedure with one parameter.
In the ASP page I run the Stored Procedure as:
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
Even though it clearly exists.
if I run it in SQL I do get the results just fine:
EXEC[report_basic_contacts
The whole data set shows. So its an ASP issue.
Can you please help ?
CREATE PROCEDURE [dbo].[report_basic_contacts_01_results]
@Firmid INT
AS
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
GOIn 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
%>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
Even though it clearly exists.
if I run it in SQL I do get the results just fine:
EXEC[report_basic_contacts
The whole data set shows. So its an ASP issue.
Can you please help ?
Make sure you have GRANTed permission to the user that asp is connecting as.
ASKER
It has full permission. Something is wrong with the asp code
ASKER
For example I tried to display this value:
<%=(dbo.report_basic_conta
got this error:
<%=(dbo.report_basic_conta
got this error:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/bluedot/Intranet/reports/basic_contacts_01_results.asp, line 119 
the 2nd piece of your code can be simplified to this:
set cn = Server.CreateObject("ADODB
cn.open MM_bluedot_STRING ' assuming MM_bluedot_STRING is your connection string
set rs= cn.execute("exec [dbo].[report_basic_contac
But then you probably have to somehow show the results. The code you posted runs stored procedure, but it has nothing that would actually fetch the results and send them as response. You probably need something like this:
set cn = Server.CreateObject("ADODB
cn.open MM_bluedot_STRING ' assuming MM_bluedot_STRING is your connection string
set rs= cn.execute("exec [dbo].[report_basic_contac
But then you probably have to somehow show the results. The code you posted runs stored procedure, but it has nothing that would actually fetch the results and send them as response. You probably need something like this:
do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop
rs.close
response.write rs(0) & "<br>"
rs.movenext
loop
rs.close
ASKER
I would like to leave the SP code as is, this way I can use the program I am using to modify it, unless it NEEDS to be modified but if its to simplify I rather keep as such.
I changed my code as follow, but I get the same error
This is a dbo user and this is the SP:
Seems like it should be pretty simple. I am just passing one variable. If I do this on the SQL Enterprise manager it returns the results fine :$
I changed my code as follow, but I get the same error
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html>
<%
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
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
sp_results_numRows = rs_sp_results_numRows + Repeat1__numRows
%>
<html>
<head>
</head>
<!--#Body-->
<body>
<div id="wrapper">
<nav class="navbar-default navbar-static-side" role="navigation">
<div class="sidebar-collapse">
</div>
</nav>
<div id="page-wrapper" class="gray-bg">
<div class="row border-bottom">
</div>
<!-- Breadcrumbs navigation -->
<div class="row wrapper border-bottom white-bg page-heading">
<div class="col-lg-10">
<h2>Basic | Contacts</h2>
<ol class="breadcrumb">
<li><a href="../default.asp">Home</a></li>
<li >Reports</li>
<li>Basic</li>
<li class="active"><strong><a href="basic_contacts_index.asp">Contacts</a></strong></li>
</ol>
</div>
</div>
<!-- Breadcrumbs navigation end -->
<!-- Main content -->
<div class="wrapper wrapper-content animated fadeIn" style="animation-delay: 0.4s;">
<div class="row">
<div class="col-lg-12">
<div class="ibox float-e-margins">
<div class="ibox-title">
<h5>Report results - <%=(dbo.report_basic_contacts_01_results.Fields.Item("Firmid").Value)%></h5>
</div>
<div class="ibox-content">
<div class="table-responsive">
<table class="table table-striped table-bordered table-hover dataTables-example" >
<thead>
<tr>
<th width="6%">Actions</th>
<th width="32%">Name</th>
<th width="25%">Signatory</th>
<th width="37%">Signatory email</th>
</tr>
</thead>
<tbody>
<%
While ((Repeat1__numRows <> 0) AND (NOT sp_results.EOF))
%>
<tr class="gradeX">
<td><a href="../employers/corpusers_edit.asp?employerid=<%=(sp_results.Fields.Item("employerid").Value)%>&CorpuserId=<%=(sp_results.Fields.Item("userid").Value)%>" class="btn btn-white btn-sm"><i class="fa fa-folder" text-navy></i></a></td>
<td><%=(sp_results.Fields.Item("MaidenNm").Value)%></td>
<td><%=(sp_results.Fields.Item("FirstNm").Value)%> <%=(sp_results.Fields.Item("LastNm").Value)%></td>
<td><%=(sp_results.Fields.Item("Email").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
sp_results.MoveNext()
Wend
%>
</tbody>
<tfoot>
<tr>
<th>Actions</th>
<th>Name</th>
<th>Signatory</th>
<th>Signatory email</th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- Main content end-->
<!-- Content end-->
</div>
<div id="right-sidebar">
<div class="sidebar-container">
</div>
</div>
</div>
</div>
</body>
</html>
<%
sp_results.Close()
Set sp_results = Nothing
%>This is a dbo user and this is the SP:
ALTER PROCEDURE [dbo].[report_basic_contacts_01_results]
@Firmid INT
AS
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.LastNmSeems like it should be pretty simple. I am just passing one variable. If I do this on the SQL Enterprise manager it returns the results fine :$
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
YES !! Thank you so much !!! it appears to have worked.
ASKER