Troubleshooting a Parameterized SQL Statement in asp

Question

I'm trying to secure some legacy code written in what I guess is VB or asp(Not really sure if there is a difference). When I try to execute the statement the page gets an internal server error. I'm convinced this is a result of the connection but I don't know the language well enough to know how to troubleshoot it.

What I know of the language and the ADODB library has come from W3Schools documentation and this post.

Here is the code I have written (Identifying information redacted)

SET Conn=server.CreateObject("adodb.connection")
Conn.Open "Provider=sqloledb;SERVER=I;DATABASE=Hate;UID=My;PWD=Life;"

SET countCmd = createobject("adodb.command")
countCmd.ActiveConnection = Conn
countCmd.commandText = "SELECT COUNT(*) FROM [table1] WHERE FY=@fy"

countCmd.Parameters.Append countCmd.createparameter("@fy", 200, 1, 255, fy)
SET pcount = countCmd.Execute() 'This is where the error happens

My end goal is not just to get a count from this table but to understand th adodb library well enough that I could continue parameterizing all of the queries in this legacy code base that need it.

I appreciate any help and would love a detail explanation.

EDIT

I wish I could accept both of these answers as the accepted answer because together I think they are the perfect answer. I ended up using both so upvote these guys por favor.

Answer 1

When using a CommandType of adCmdText the placeholder expected by ADODB is ? and trying to passed named parameters like @fy in the CommandText will fail. It is an unfortunate failing in ADODB that

countCmd.NamedParameters = True

only works with a CommandType of adCmdStoredProc and only with certain providers.

However there is a simple workaround for SQL Server (and possibly other providers depending on what they support) which is to build the named parameters in the CommandText like so;

countCmd.commandText = _
    "DECLARE @fy AS VARCHAR(255);" & vbCrLf & _
    "SET @fy = ?;" & vbCrLf & _
    "SELECT COUNT(*) FROM [table1] WHERE FY=@fy;"

---

Useful Links

• ADO parameterised query not returning any result

• ADODB.Parameters error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided