I have an Admin page with a list of registered users. I want to be able to resort the recordset using links at the top of the columns.

I can do this by setting up the recordset on the page no problem, but I want to use a stored procedure.

Here is the code for the stored procedure:
--------------------------------------------------------------------
CREATE PROCEDURE spAdminUsersView

@SortBy varchar(100)

AS

SET NOCOUNT ON


Declare @SQL AS varchar(500)
SELECT @SQL = 'SELECT * FROM tblUsers ORDER BY ' + @SortBy
Execute (@SQL)


SET NOCOUNT OFF
GO
--------------------------------------------------------------------

Now, all this works fine, the parameter @SortBy is picked up from the page - no problem. The results are re sorted and displayed correctly.


However whenever I use a stored procedure like this, whereby I delaclare a new pareameter then assign a new SQL statement I have a problem when I try to use the Recordset Navigation Live Object.

The page always fails at this line:

' reset the cursor to the beginning
If (MM_rs.CursorType > 0) Then
MM_rs.MoveFirst
Else
MM_rs.Requery <------Fails at this line
End If


Has anyone else run into this problem? What was your fix?


Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Yeah, I had a similar problem, are you calling the sp via a recordset or command? If command, set it to a recordset and everything should work fine..
HTH's
-Greg

<company> VHE Designs</company>
<services> ASP, SQL, VB, eCommerce, Hosting</services>
<web> www.vhedesignsweb.com </web>
<phone> 908.832.3082 </phone>

Yes, I was using Command(Stored Procedure) then ticking the return recordset box.

I've checked it out, picking up the stored procedure from the advanced view of the Recordset(Query) and it works fine.

Thanks for the tip, that little problem has been bugging me for some time.

By the way, I think I found the problem you were having with your own Stored procedure so check out my post there too.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo