ADO命令执行方法未填充ADO记录集-ADOrecordsetnotpopulatedbyADOcommandexecutemethod

I am doing some clean up to protect from SQL injection attacks happening in a older internal website that uses ASP. Here's the gist of it all in code...

我正在做一些清理，以防止在使用ASP的旧内部网站中发生SQL注入攻击。以下是代码中所有内容的要点......

Database connection is setup in a separate asp file named connect.asp

数据库连接在名为connect.asp的单独asp文件中设置

<%
on error resume next

Set DB = Server.CreateObject("ADODB.Connection")
DB.CommandTimeout = 180
DB.COnnectionTimeout= 180

cOnnStr= "Provider=SQLOLEDB;Data Source=xxx-xxxx-xxxxxx;Initial Catalog=xxxx;Persist Security Info=True;User ID=xxxxx;Password=xxxxxxxxxxxx;"

DB.Open connStr

' Check DB connection and go to Error Handler is error exists
if DB.state=0 then
    Response.Write "
Cannot connect to database.
"
    TrapError Err.description
    Response.end
end if
%>

This works and the db connections is opened.

这有效，并且数据库连接已打开。

I have a file named DBFunctions.asp that I use to sort of map functions to stored procedures and their parameters. I am trying to use the function below to return a ADO recordset to another asp front end page.

我有一个名为DBFunctions.asp的文件，用于将映射函数排序到存储过程及其参数。我试图使用下面的函数将ADO记录集返回到另一个asp前端页面。

Function GetFacilityByFID(fid)
    set rs = server.CreateObject("ADODB.Recordset")
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveCOnnection= DB
    cmd.CommandText = "GetFacilityByFID"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("@FID", adVarChar, adParamInput, 20)
    cmd("@FID") = fid
    Set rs =  cmd.Execute
    Set GetFacilityByFID = rs       
End Function

Here is the code from the calling front end asp page, facDetail.asp

这是来自调用前端asp页面facDetail.asp的代码

<%

Dim FID, FCBI, Error
FID = Request("FID")
FCBI = Request("FCBI") 

' Check DB connection and go to Error Handler is error exists
if DB.state=0 then
    Response.Write "
Cannot connect to database.
"
    TrapError Err.description
    Response.end
else
    if FID then
        Set RS = GetFacilityByFID(FID) 
    elseif FCBI then
        Set RS = GetFacilityByFCBI(FCBI)
    end if

    if RS.EOF then
        Response.Write "
No record found
"
        response.End
    end if
end if
%>

The calling page is displaying that there are no records returned

调用页面显示没有返回记录

but the stored procedure works when executed in SSMS.

但是存储过程在SSMS中执行时有效。

Updated code

更新的代码

Here's the SQL Code for the GetFacilityByFID stored procedure.

这是GetFacilityByFID存储过程的SQL代码。

CREATE PROCEDURE [dbo].[GetFacilityByFID] 
    @FID varchar(20) 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [FAC_CBI_NBR]
  ,[FAC_ID]
  ,[FAC_TYPE]
  ,[FAC_SUBTYPE]
  ,[FAC_REGION]
  ,[FAC_COST_CENTER]
  ,[FAC_SUPPLY_CODE]
  ,[FAC_UPLINE]
  ,[FAC_SERVICE]
  ,[FAC_LOCATION_NAME]
  ,[FAC_LOCAL_ADDR1]
  ,[FAC_LOCAL_ADDR2]
  ,[FAC_LOCAL_CITY]
  ,[FAC_LOCAL_STATE]
  ,[FAC_LOCAL_ZIP]
  ,[FAC_MAIL_ADDR1]
  ,[FAC_MAIL_ADDR2]
  ,[FAC_MAIL_CITY]
  ,[FAC_MAIL_STATE]
  ,[FAC_MAIL_ZIP]
  ,[FAC_COUNTRY]
  ,[FAC_PHONE]
  ,[FAC_FAX]
  ,[FAC_MANAGER]
  ,[FAC_CONTACT]
  ,[FAC_CONTACT_PHONE]
  ,[FAC_CONTACT_EXT]
  ,[FAC_CONTACT_EMAIL]
  ,[FAC_COMMENTS]
  ,[FAC_CHANGED_BY]
  ,[FAC_LAST_UPDATE]
  ,[FAC_MAILOUT]
  ,[FAC_CONTRACTION]
  ,[FAC_PROPERTY_CODE]
  ,[FAC_ATTN_TO]
FROM [cbid].[dbo].[FACILITY] 
WHERE [FAC_ID]=@FID
END

GO    

Can anyone tell me what is going wrong? I have been looking at this too long and have grown frustrated with it.

谁能告诉我出了什么问题？我一直在关注这个问题并且对此感到沮丧。

Any help will be greatly appreciated!

任何帮助将不胜感激！

Edit:

编辑：

Current Status of issue: getting the following from the ADO provider

当前问题状态：从ADO提供商处获取以下信息

Error Number: -2147217904 Error Desc: Procedure or function 'GetFacilityByFID' expects parameter '@FID', which was not supplied. Error Source: Microsoft OLE DB Provider for SQL Server

错误号：-2147217904错误描述：过程或函数'GetFacilityByFID'需要参数'@FID'，这是未提供的。错误源：用于SQL Server的Microsoft OLE DB提供程序

4 个解决方案

Function GetFacilityByFID(fid)
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveCOnnection= DB
    cmd.CommandText = "GetFacilityByFID"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("@returnVal", adVarChar, adParamOutput, 255, "")
    cmd.Parameters.Append cmd.CreateParameter("@FID", adVarChar, adParamInput)
    cmd("@FID") = fid
    cmd.Execute
    GetFacilityByFID = cmd("@returnVal")
End Function

 Function GetFacilityByFID(fid)

    Set rs = server.CreateObject("ADODB.Recordset")
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveCOnnection= DB
    cmd.CommandText = "GetFacilityByFID"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandTimeout = 900
    set prm = cmd.CreateParameter("@FID",adVarChar, adParamInput, 20, fid)
    cmd.Parameters.Append prm
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
    Set GetFacilityByFID = rs

On Error GoTo 0   
End Function

Dim param
Set param = cmd.CreateParameter("@FID", adVarChar, adParamInput)
cmd.Parameters.Append param
param.Value = fid