Recordset paging with stored procedure in MSSQL

[Guest]

Hi all,
I have a problem with recordset paging using a recordset from a stored procedure in MSSQL 2000.

The page is working fine without the recordset paging functionality. I.e. it returns a recordset and displays the information on screen as expected. However, as soon as I add this functionality in, the page returns an error from the database server stating that a parameter was not supplied to the stored procedure. I suspect that this could be to do with the Requery command in the function.

Has anyone come across this problem before and any ideas how to get around it?

Any help appreciated.

Thanks in advance.

Ben.

[Newsgroup_User]

Create the recordset by using the Recordset behaviour rather than the Stored
Procedure behaviour.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[Guest]

Hi Jules,
Thanks for the advice. Unfortunately, using a recordset from the recordset behavior will not work in the context of the page due to the functionality required. The idea of using a stored procedure is to return a recordset based on a number of variables on the page and so a recordset behavior would not suffice. Of course, this works fine on other pages on the site and have used it successfully.

Does DW treat a stored procedures recordset differently from that of a 'normal' recordset in this instance? I would have thought that if there's an option there to return a recordset, then surely it would be handled similarly? I have attached the code here for you/someone to check out? Maybe I'm missing something? Have just used the 'Move to First Record' behavior to illustrate the point. Have also attached the code for the stored procedure.

The URL that is parsed is:

http://localhost/portal/sr/sr.asp?ShowAll=A

The error that appears is as follows:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E10)
Procedure 'GetSR' expects parameter '@CompId', which was not supplied.
/sr.asp, line 217

Even when I supply the @CompId value, I still get the same error.

Anyway, thanks again.

Ben.


CREATE PROCEDURE dbo.GetSR (
@CompId int,
@ShowAll Char(3),
@UserId int,
@SRStatus varchar(20),
@SRType int
)

AS

/* Show All */
if @ShowAll = ''
begin
SELECT * FROM dbo.SR_View
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for current user */
else if @ShowAll = 'U'
begin
SELECT * FROM dbo.SR_View
Where Status <> 'Closed'
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for current company */
else if @ShowAll = 'C'
begin
SELECT * FROM dbo.SR_View
WHERE CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all = P */
else if @ShowAll = 'P'
begin
SELECT * FROM dbo.SR_View
WHERE PeopleId = @UserId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show Status */
else if @ShowAll = 'S'
begin
if @CompId = 0
begin
SELECT * FROM dbo.SR_View
where Status = @SRStatus
ORDER BY [Sequence], Priority, SubmitDate Desc
end

else if @CompId > 0
begin
SELECT * FROM dbo.SR_View
where Status = @SRStatus and CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
end

/* Show Type */
else if @ShowAll = 'T'
begin
if @CompId = 0
begin
SELECT * FROM dbo.SR_View
where TypeId = @SRType
ORDER BY [Sequence], Priority, SubmitDate Desc
end

else if @CompId > 0
begin
SELECT * FROM dbo.SR_View
where TypeId = @SRType and CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
end

/* Show all for admin user */
else if @ShowAll = 'A'
begin
SELECT * FROM dbo.SR_View
Where CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for normal user user */
else if @ShowAll = 'N'
begin
SELECT * FROM dbo.SR_View
Where PeopleId = @UserId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
GO
Error Type: Text

[Newsgroup_User]

"worf99" <benjamin.ey@gmail.com> wrote in message
news:e248g1$3l9$1@forums.macromedia.com...
> Hi Jules,
> Thanks for the advice. Unfortunately, using a recordset from the recordset
> behavior will not work in the context of the page due to the functionality
> required. The idea of using a stored procedure is to return a recordset
> based
> on a number of variables on the page and so a recordset behavior would not
> suffice. Of course, this works fine on other pages on the site and have
> used it
> successfully.

The recordset behavior can also use a number of variables. I see no output
parameters. There's no reason you can't use the recordset behavior to
execute your stored procedure.

>
> Does DW treat a stored procedures recordset differently from that of a
> 'normal' recordset in this instance?

A recordset from a command object doesn't have all the same options that a
regular recordset does, such as changing the cursor type, location, etc.
That kind of stuff matters when you start doing more than displaying the
data in a loop or streaming it to an array.

Use the recordset behavior.

[Guest · Accepted solution]

Thanks for your help everyone. This works a treat!

I did not realise that it was possible to execute a stored procedure from the recordset behavior. I was under the impression that stored procedures must be accessed from the 'Command' behaviour. A new day has dawned!!!

Thankyou!

[Newsgroup_User]

You're welcome.

"worf99" <benjamin.ey@gmail.com> wrote in message
news:e26r05$g54$1@forums.macromedia.com...
> Thanks for your help everyone. This works a treat!
>
> I did not realise that it was possible to execute a stored procedure from
> the
> recordset behavior. I was under the impression that stored procedures must
> be
> accessed from the 'Command' behaviour. A new day has dawned!!!
>
> Thankyou!
>