对ASP中的参数化SQL语句进行故障排除 [英] Troubleshooting a Parameterized SQL Statement in asp

问题描述

我正在尝试保护一些我认为是VB或asp编写的旧代码（不确定是否有区别）。当我尝试执行该语句时，页面收到内部服务器错误。我坚信这是这种连接的结果，但是我对这种语言的了解不深，无法知道如何解决它。

我对语言的了解和ADODB库来自W3Schools 文档和此帖子。

这是我编写的代码（标识已编辑的信息）

  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] WYE FY = @ fy 
 
 countCmd.Parameters.Append countCmd.createparameter（ @ fy，200，1，255，fy）
 SET pcount = countCmd.Execute（）'这是错误所在发生
  

我的最终目标不仅是从该表中获取计数，而且还要很好地了解adodb库足够我可以继续在此旧代码库中对所有需要它的查询进行参数化。

我非常感谢您的帮助，并希望提供详细的解释。

编辑

我希望我可以接受这两个答案作为可接受的答案，因为我认为它们是完美的答案。我最终都使用了这两种方法，所以都赞成这些家伙。

解决方案

使用 CommandType adCmdText 的c $ c> ADODB期望的占位符是？并尝试传递<$ c $之类的命名参数 CommandText 中的c> @fy 将失败。

  countCmd.NamedParameters = True 
  是ADODB中的不幸失败。 pre> 
 
 
仅适用于 CommandType 的 adCmdStoredProc 和
 
 
 
不过，对于SQL Server （还有可能取决于其支持的其他提供程序）有一个简单的解决方法像这样在 CommandText 中建立命名参数； 
  countCmd.commandText = _ _ 
 DECLARE @fy AS VARCHAR（255）; & vbCrLf& _ 
 SET @fy =？; & vbCrLf& _ 
从[table1]中选择COUNT（*），其中FY = @ fy; 
  
 
 
 
 
 
 
有用的链接
 
 
 
 
 
 
  ADO参数化查询未返回任何结果 
 
 
 
 
  ADODB.Parameters错误'800a0e7c'参数对象定义不正确。提供的信息不一致或不完整 
 
 
 
 
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.
解决方案
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


                        
这篇关于对ASP中的参数化SQL语句进行故障排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！