当我将2个参数传递给存储过程。基本上,我想发送1个参数来覆盖我正在寻找的列一个用于搜索项。例如纽约大学imSchool。我对发送的数据和记录集进行了数据检查。状态代码显示,每次我选择带有两个参数的SQL查询时,都会显示错误"对象关闭时不允许操作"。我试图在代码的"While Not rs.EOF"行打开对象,但它出错了,运气不好。我认为我的SQLQuery很糟糕,因为当我在MSSQL中使用相同的输入运行存储过程时,每次都会得到一个返回表。SQL Server 2008 R2和经典ASP。
这是我的IF STATMENT WITH SQLQUERY CODE(请记住,第一个很好用,数据是从数据库中选择的)
SQLQuery = "Exec sp_vw_InternImport"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open SQLQuery, OBJdbConnection
If filterColmn <> "" Then
SQlQuery = "Exec sp_vw_InternImport_ColID @LookUpID=N'" + filterID + "'" + ", @LookUpCol=N'" + filterID + "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open SQLQuery, OBJdbConnection
End If
这是我的存储过程代码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_vw_InternImport_ColID]
(
@LookUpID nvarchar (255),
@LookUpCol nvarchar (50)
)
AS
SET NOCOUNT ON
BEGIN
IF @LookUpCol = 'imYear'
SELECT * FROM v_InternImport WHERE imYear = @LookUpID
ELSE
IF @LookUpCol = 'imSchool'
SELECT * FROM v_InternImport WHERE imSchool = @LookUpID
ELSE
IF @LookUpCol = 'imDiscipline'
SELECT * FROM v_InternImport WHERE imDiscipline = @LookUpID
IF @LookUpCol = 'imDegree'
SELECT * FROM v_InternImport WHERE imDegree = @LookUpID
END
当直接向存储过程传递参数时,不必"分配"参数。这样做可能会导致传递整个值(例如,@LookUpCol
的值为@LookUpCol ='imYear'
),因此SP不会选择任何内容,并且您有空且已关闭的记录集。
尝试使用这样的代码:
SQlQuery = "Exec sp_vw_InternImport_ColID '" & filterID & "', '" & filterID & "'"
不能同时使用同一个对象两次(如rs
),必须将其命名为另一个名称,否则必须关闭它并重新开始。。这应该有效:
SQLQuery = "Exec sp_vw_InternImport"
If filterColmn <> "" Then SQLQuery = "Exec sp_vw_InternImport_ColID @LookUpID=N'" + filterID + "'" + ", @LookUpCol=N'" + filterID + "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open SQLQuery, OBJdbConnection
rs.Close
或者在您的示例中,如果在转到下一个对象之前关闭第一个rs
对象,这可能会有所帮助。
set rs = nothing ... then .... set rs = server.createobject
就像这样:
SQLQuery = "Exec sp_vw_InternImport"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 3
rs.Open SQLQuery, OBJdbConnection
rs.Close ------ Close it before you re-open it
If filterColmn <> "" Then
SQlQuery = "Exec sp_vw_InternImport_ColID @LookUpID=N'" + filterID + "'" + ", @LookUpCol=N'" + filterID + "'"
rs.Open SQLQuery, OBJdbConnection
rs.Close ------ Always Close your Objects!
End If