Stored Procedure Does not return recordset, aka: 800a0e78

[smartLIZARD]

I receive the message:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/floridafind/search/search.asp, line 697


It seems to me that the Stored Procedure is not returning a recordset.

There is no rs.close before I get this error.

Here is a partial of my code...

Parameters = "'5', '', '', 'FL', '', '', '', ''"
SProcedure = "pSearch_Corporations"
rs.Open (SProcedure & "(" & Parameters & &quot&quot, oConn, adOpenStatic, adLockOptimistic

If Not rs.EOF Then ' This is where my error happens


Here is the Stored Procedure...

CREATE PROCEDURE pSearch_Corporations
@Top_Count varchar(4),
@Filing_Name varchar(48),
@City varchar(28),
@State varchar(2),
@Zip varchar(10),
@FEI_No varchar(14),
@FName varchar(21),
@LName varchar(21)

AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT TOP '
SELECT @SQL = @SQL + @Top_Count
SELECT @SQL = @SQL + ' * FROM t_FL_Corporations WHERE '
SELECT @SQL = @SQL + 'Name LIKE ' + "'%" + @Filing_Name + "%' AND "
SELECT @SQL = @SQL + 'FEI_No LIKE ' + "'%" + @FEI_No + "%' AND "
SELECT @SQL = @SQL + 'Registered_Agent_Name LIKE ' + "'%" + @FName + "%' AND "
SELECT @SQL = @SQL + 'Registered_Agent_Name LIKE ' + "'%" + @LName + "%' AND "
SELECT @SQL = @SQL + 'Mailing_City LIKE ' + "'%" + @City + "%' AND "
SELECT @SQL = @SQL + 'Mailing_State LIKE ' + "'%FL%' AND "
SELECT @SQL = @SQL + 'Mailing_Zip LIKE ' + "'%" + @Zip + "%'"

Exec ( @SQL)
GO

 

[mukund]

Hi smartLIZARD
Put a SET NOCOUNT ON statement after Declare @SQL
statement, and put a SET NOCOUNT OFF statement just before EXEC(@SQL) statement and see whether this works.

Mukund.

 

[moonshadow]

I don't believe this to be a SQL error, are you sure that your connection (oConn) is open. Alternatively, as you are using EXEC to produce a recordset, is it possible that ADO thinks this SP doesn't return records, and closes the recordset immediately after running it. If you can think of a way of eliminating @Top_Count from the equation (e.g. always returning the top 20 records), the SP could be rewritten more efficiently. Alternatively you can use the SET ROWCOUNT option to limit the number of records returned. Not as good as using TOP, but I imagine better than using EXEC

 

[balves]

I would get rid of the dynamic SQL. Try to avoid it whenever possible.

To solve the TOP N issue:

From VB:

rs.MaxRecords = <the integer value you want>
rs.open