Problem with Stored Procedure in SQL 2000, ASP

[gus121]

Hi i am a newbie to stored procedure i cant get this stored procedure to work see bellow VBscript and stored procedure.

Procedure or function usp_CatSectionUpdate has too many arguments specified the error line is on the .Execute ,,adExecuteNoRecords in the vbscript. i have printed the input on the screen and looks ok. can anyone help.

CREATE PROCEDURE [dbo].[usp_CatSectionUpdate]
@CategoryID int,
@SectionID int,
@SectionMask int
AS
SET NOCOUNT ON

UPDATE [tblCategories]
SET
[Section_ID] = @SectionID,
[Section_Mask] = @SectionMask
WHERE
[Category_ID] = @CategoryID AND SubCategory_ID = 0





GO

with cmd
.ActiveConnection = gConn
.CommandText = "usp_CatSectionUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@CategoryID", adInteger, adParamInput, 0)
.Parameters.Append .CreateParameter("@SectionID", adInteger, adParamInput, 0)
.Parameters.Append .CreateParameter("@SectionMask", adInteger, adParamInput, 0)

.Parameters("@CategoryID") = Cat_ID
.Parameters("@SectionID") = DefaultID_new
.Parameters("@SectionMask") = Mask_total_New
'response.write " " & Clng(Cat_ID) & " " & Clng(DefaultID_new) & " " & Clng(Mask_total_New) & "<br>"

.Execute ,,adExecuteNoRecords
end with

thanks angus

-Gus

-Gus

 

[Sheco]

First thing is to make sure you can run the Stored Procedure from the Query Analyzer and that it behaves the way that you need it to behave.

You know you could also do this another way without using the ADO command object... like this:

Set RS = gConn.Execute("usp_CatSectionUpdate " & Cat_ID & ", " & DefaultID_new & ", " &  Mask_total_New
Response.Write "Return Value is: " & RS(0)

 

[Sheco]

Your stored procedure only has input arguments:
@CategoryID int,
@SectionID int,
@SectionMask int


You don't have any return value specified.

You have NoCount turned on.

And the SQL that you execute is an UPDATE statement...

What exactly are you hoping to get back from this thing?

 

[gus121]

the stored proc works fine in query analyser i have tested it. I will look now into the other things you sugested now.
thanks for your time.

-Gus

 

[gus121]

I dont wish to return anything back i just cobled it together from anouther stored procedure that is why it is in my code i also was in the process of trying various things as i could not understand why it would not work.

If i remove this line i still get the same result
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)



-Gus

 

[Sheco]

Oh I have a syntax error in that .Execute() statement above... I forgot the closing parens.

 

[Sheco]

Ah ok that makes sense you are converting from other code and the return value was just an artifact from the previous version?

 

[fredericofonseca]

And another thing to look at is the size of the parameters. You have them defined as size 0 (zero) which is not very advisable....

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BDC2]

try this:

with cmd
.ActiveConnection = gConn
.CommandText = "usp_CatSectionUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@CategoryID", adInteger, adParamInput,,0)
.Parameters.Append .CreateParameter("@SectionID", adInteger, adParamInput,,0)
.Parameters.Append .CreateParameter("@SectionMask", adInteger, adParamInput,,0)
.Execute ,,adExecuteNoRecords
end with

BDC.

 

[gus121]

Hi BDC,

the suggested method given how do i pass the variables into the code. thanks

Can anyone point me in the right direction of some good tutorials sites for stored procedures and calling stored procedures from Vbscript.

thanks



-Gus

 

[shokti]

gus121,

TRY THIS TO ASSIGN VALUE TO PARAMETERS B4 EXECUTE.

cmd.Parameters("@CategoryID") = INTEGER VALUE HERE
cmd.Parameters("@SectionID") = INTEGER VALUE HERE
cmd.Parameters("@SectionMask") = INTEGER VALUE HERE

 

[gus121]

HI thanks for everybodys help. I managed to resolve my issue.

The problem was that the stored proc update call was nested within a code logic loop and what was happening was that it was updating the first record OK and then failing with error too many arguments when it looped through the secound time.

This was resolved by setting the recordset object too nothing each time before it looped back through the code again.

thanks

Angus


-Gus