ADO recordset not populated by ADO command execute method
<p>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...</p>

<p>Database connection is setup in a separate asp file named connect.asp</p>

<pre><code>&lt;%
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 "&lt;p&gt;Cannot connect to database.&lt;/p&gt;"
    TrapError Err.description
    Response.end
end if
%&gt;
</code></pre>

<p>This works and the db connections is opened.</p>

<p>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.</p>

<pre><code>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
</code></pre>

<p>Here is the code from the calling front end asp page, facDetail.asp</p>

<pre><code>&lt;%

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 "&lt;p&gt;Cannot connect to database.&lt;/p&gt;"
    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 "&lt;BR&gt;&lt;p class=alert&gt;No record found&lt;/p&gt;"
        response.End
    end if
end if
%&gt;
</code></pre>

<p>The calling page is displaying that there are no records returned</p>

<p><a href="https://i.stack.imgur.com/yC0qk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yC0qk.png" alt="enter image description here"></a></p>

<p>but the stored procedure works when executed in SSMS.</p>

<p><strong>Updated code</strong></p>

<p>Here's the SQL Code for the GetFacilityByFID stored procedure.</p>

<pre><code>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    
</code></pre>

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

<p>Any help will be greatly appreciated!</p>

<p><strong>Edit:</strong> </p>

<p>Current Status of issue: getting the following from the ADO provider</p>

<blockquote>
  <p>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</p>
</blockquote>