My classic ASP page thorws up an error when I pass 2 paremeters to the Exec code for my stored procedure. Basically I want to send 1 paremeter to cover the column I am looking for and one for the search-term. For example imSchool, New York University. I have data checks on the data sent as well as Record Set.State code showing that everytime i choose SQL Query with the two paramerts the error "Operation is not allowed when the object is closed." always shows. I tried to open the object at " While Not rs.EOF" line of code where it error out bit no luck. I am thinking my SQLQuery is bad because when I run the Stored Procedure in MSSQL with the same input I get a return table everytime. SQL Server 2008 R2 and Classic ASP.
HERE IS MY IF STATMENT WITH SQLQUERY CODE (keep in mind the 1st one works fine and data is selected from the DB)
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
HERE IS MY STORED PROCEDURE CODE
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
When passing arguments to stored procedure directly, you don't have to "assign" the parameters. Doing this probably results in the whole value passed (e.g. @LookUpCol
will have the value of @LookUpCol ='imYear'
) thus your SP won't select anything and you have empty and closed recordset.
Try having such code instead:
SQlQuery = "Exec sp_vw_InternImport_ColID '" & filterID & "', '" & filterID & "'"