传递参数到存储过程的传统的ASP(passing parameters into stored procedures classic asp)

561 IT屋

I've asked a similar question before and although I've attempted to fix my previous code i now end up with an error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". I want to pass a single character as a parameter to query a database then use recordset to print out to a webpage. My classic asp code is below

Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
set objComm = CreateObject("ADODB.Command")

objCon.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=Movies;Data Source=xxxx-PC"
objCon.open objCon.ConnectionString

objComm.ActiveConnection = objCon.ConnectionString
objComm.CommandText = "Paging_Movies"
objComm.CommandType = adCmdStoredProc

Set objParameter = objComm.CreateParameter
objParameter.Name = "alphaChar"
objParameter.Type = adChar
objParameter.Direction = adParamInput
objParameter.Value = "a"

objComm.Parameters.Append objParameter

set objRs = objComm.Execute

Also my stored procedure is below -

CREATE PROCEDURE Paging_Movies
@alphaChar char(1)
AS
if @alphaChar = '#'
    select * from Movies where movies like '[^a-z]%'
else
    select * from Movies where movies like @alphaChar + '%'
解决方案

Perhaps you're adding the parameter twice. Try to replace:

objComm.ActiveConnection = objCon.ConnectionString
objComm.CommandText = "Paging_Movies"
objComm.CommandType = adCmdStoredProc

Set objParameter = objComm.CreateParameter
objParameter.Name = "alphaChar"
objParameter.Type = adChar
objParameter.Direction = adParamInput
objParameter.Value = "a"

Set objParameter = objCommand.CreateParameter ("alphaChar", adChar, _
    adParamInput, "a")

objComm.Parameters.Append objParameter

with just:

objCommand.CreateParameter ("alphaChar", 129, 1, 1, "a")

Edited as your comment suggests to use the numeric values for adChar (129) and adParamInput (1) from the W3Schools CreateParameter page.

我以前问过类似的问题,虽然我试图解决我的previous $ C $词现在用一个错误"参数的错误类型的结束,超出可接受的范围或在与另一个冲突"。我想通过一个单一的字符作为参数来查询数据库,然后使用记录打印出来的网页。我传统的ASP code低于

 设置objCon =的CreateObject("ADODB.Connection")
设置objRS =的CreateObject("ADODB.Recordset")
设置objComm =的CreateObject("ADODB.Command")objCon.ConnectionString ="供应商= SQLOLEDB.1;密码= XXXX;坚持安全信息= TRUE;用户ID = XXXX;初始目录=电影;数据源= XXXX-PC"
objCon.open objCon.ConnectionStringobjComm.ActiveConnection = objCon.ConnectionString
objComm.CommandText ="Paging_Movies"
objComm.CommandType = adCmdStoredProc设置objParameter = objComm.CreateParameter
objParameter.Name ="alphaChar"
objParameter.Type = adChar
objParameter.Direction = adParamInput
objParameter.Value ="一个"objComm.Parameters.Append objParameter设置objRs = objComm.Execute

此外我的存储过程如下 -

  CREATE PROCEDURE Paging_Movies
@alphaChar CHAR(1)

如果@alphaChar ='#'
    从电影中选择*,其中像电影'[^ A-Z]%'
其他
    选择电影*,其中像电影@alphaChar +'%'

解决方案

也许你要添加的参数两次。试图取代:

  objComm.ActiveConnection = objCon.ConnectionString
objComm.CommandText ="Paging_Movies"
objComm.CommandType = adCmdStoredProc设置objParameter = objComm.CreateParameter
objParameter.Name ="alphaChar"
objParameter.Type = adChar
objParameter.Direction = adParamInput
objParameter.Value ="一个"设置objParameter = objCommand.CreateParameter("alphaChar",adChar,_
    adParamInput,"一")objComm.Parameters.Append objParameter

只有

  objCommand.CreateParameter("alphaChar",129,1,1,"A")

编辑为您的意见建议用于 adChar 的数值(129)和 adParamInput (1) W3Schools的CreateParameter页。

本文地址:IT屋 » 传递参数到存储过程的传统的ASP