Stored Proc error when been called from ASP

[gus121]

I know this is got to be basic but i cant understand why this stored proc errors.

Procedure or function usp_ParentCatChildWeightUpdate has too many arguments specified.

/category.asp, line 572


This is the ASP it errors on the ,,adExecuteNoRecords line.

			with cmd
				'response.write "<br>i got here i am a parent<br>"
				'response.write "<br>" & request.form("cid") & " " & request.form("DefProdWeight") & "<br>"
				.ActiveConnection = gConn
				.CommandType = adCmdStoredProc
				.CommandText = "usp_ParentCatChildWeightUpdate" 'update child subcat and products
				.Parameters.Append .CreateParameter("@CategoryID", adInteger, adParamInput, 0)
				.Parameters.Append .CreateParameter("@DefProdWeight", adInteger, adParamInput, 0)
				.Parameters("@CategoryID") = request.form("cid") 
				.Parameters("@DefProdWeight") = request.form("DefProdWeight")
				
				.Execute ,,adExecuteNoRecords
					
			end with

This is the SQL stored proc which runs fine from SQL query Analyser:

SET QUOTED_IDENTIFIER ON 

CREATE  PROCEDURE [dbo].[usp_ParentCatChildWeightUpdate]
	@CategoryID int,	
	@DefProdWeight int
AS

	UPDATE [O]

	SET  
		[O].Weight = @DefProdWeight
	FROM  
		tblCategories AS [ParCat]
	JOIN
		tblCategories AS [SubCat]
	ON 
		[ParCat].Category_ID = [SubCat].SubCategory_ID
	JOIN
		tblProducts AS [P]
	ON 
		[SubCat].Category_ID = [P].Category_ID
 
	JOIN 
		tblOptions AS [O]
 
	ON 
		[P].Product_ID = [O].Product_ID

	WHERE     
		([ParCat].Category_ID = @CategoryID AND [O].Verified_Weight = 0)

	
	UPDATE [SubCat]

	SET  
		[SubCat].DefaultWeight = @DefProdWeight
	FROM  
		tblCategories AS [ParCat]
	JOIN
		tblCategories AS [SubCat]
	ON 
		[ParCat].Category_ID = [SubCat].SubCategory_ID
	WHERE     
		([ParCat].Category_ID = @CategoryID)

thanks

-Gus

 

[gus121]

Hi i think i worked out the issue.

By creating a new command object for each new call i make on the database i have resolved my issue.

e.g.

dim cmd,cm2,cmd3
Set cmd = Server.CreateObject("ADODB.Command")
with cmd
...call to db
end with

Set cmd2 = Server.CreateObject("ADODB.Command")
with cmd2
..call to db
end with

...etc...
Set cmd = nothing
Set cmd2 =nothing

However if anyone can explain why this is necessery then it would be most appreciated. I am hazy on the ADO object and its scope.

thanks



-Gus

 

[Sheco]

The command object has a parameters collection. When you go in to configure the object for the second stored procedure, that instance of the object still has all of the settings that you set for the first stored procedure.

Even though you change the value of the CommandText property, it still has all of the parameter objects in its parameters collection.

If you want to only use one command object, you'll need to purge the parameters collection, something like this:

Dim ParamCount, X
ParamCount = cmd.Parameters.Count
For X = ParamCount to 1 Step -1
  cmd.Parameters.Delete X
Next

Hmm, well I wrote that as if the Parameters index has a base of 1... but i don't remember, it might have a base of 0 in which case just adjust it down one.


Also you could also just call cmd.Parameters.Refresh after setting the value of the CommandText property and ADO will magically go out and talk to your database and make all of your parameter objects for you. This is somewhat less speedy then creating them yourself in code because it involves talking to the database but it sure is handy and quick. The only thing to be aware of is that sometimes it messes up on SQLServer parameters that are data type decimal... or at least with older versions of the MDAC you would want to check those params and change them to numeric... newer versions may have fixed that.

 

[jakeyg]

how do you retreive @@IDENTITY from your stored procedure in ASP?

 

[Sheco]

Try using an OUTPUT parameter and doing something like this at the bottom of your stored procedure:

SET @MyOutputParam = @@IDENTITY

IF that doesnt work we have an entire forum for SQLServer programming here at tek-tips where you can get a better answer: forum183

 

[jakeyg]

already got the
SET @MyOutputParam = @@IDENTITY
bit in the stored procedure, it's how I get ASP to read it I'm stuck with
Looked at a few sites and tried the examples, but had no luck retrieving the value

 

[gus121]

At the top of the stored proc put something like this,
e.g

CREATE  PROCEDURE [dbo].[mystoreproc]
    @ID int,    
    @ID2 int,
    [COLOR=red]@MyOutputParam int OUTPUT[/color]
AS
.....
SET @MyOutputParam = @@IDENTITY

And in the asp to call the output parameter

Set cmd		= CreateObject("ADODB.Command")	
Set rs = CreateObject("ADODB.RecordSet")	

With cmd  

.CommandText = "mystoreproc"                                
.CommandType = adCmdStoredProc                                  
.ActiveConnection = gConn
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 0)
.Parameters.Append .CreateParameter("@ID2", adInteger, adParamInput, 0)   

 									 
.Parameters("@ID")		= a  
.Parameters("@ID2")		= b
			

End With                                                            
								   
With rs
                                                          
  .CursorLocation = adUseClient                                   
  .Open cmd, ,adOpenStatic, adCmdStoredProc   
  [COLOR=red]OutputParam = cmd.Parameters(" @MyOutputParam ")[/color]
  Set .ActiveConnection = Nothing
                                 
End With

-Gus

 

[gus121]

ops i missed of this:

.Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 0)
.Parameters.Append .CreateParameter("@ID2", adInteger, adParamInput, 0)
.Parameters.Append .CreateParameter("@MyOutputParam ", adInteger, adParamOutput)

-Gus

 

[jakeyg]

This is my stored procedure

CREATE PROCEDURE InsertSol2
    @SolName  VARCHAR(255),
    @MyOutputParam int OUTPUT
AS

SET NOCOUNT ON

  INSERT INTO Solictor (SolictorName)
  VALUES (@SolName)
  
SET @MyOutputParam = @@IDENTITY

SET NOCOUNT OFF
GO

and this is my code

  Dim dbConn, rsResult
  Set dbConn = Server.CreateObject("ADODB.Connection")
  dbConn.Open  Application("Connection2_ConnectionString"), Application("Connection2_RuntimeUserName"), Application("Connection2_RuntimePassword")
  Set rsResult = CreateObject("ADODB.RecordSet")
 
set dbComm = Server.CreateObject("ADODB.Command")

dbComm.CommandText = "InsertSol2"
dbComm.CommandType = adCmdStoredProc
dbcomm.ActiveConnection = dbConn

dbComm.Parameters.Append dbComm.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
dbComm.Parameters.Append dbComm.CreateParameter("@SolName", adVarchar, adParamInput, 0)
dbComm.Parameters.Append dbCom.CreateParameter("@MyOutputParam ", adInteger, adParamOutput)

dbComm.Parameters("@SolName") = "fred bloggs"

rsResult.CursorLocation = adUseClient                                   
rsResult.Open cmd, ,adOpenStatic, adCmdStoredProc   
OutputParam = cmd.Parameters(" @MyOutputParam ")
Set rsResult.ActiveConnection = Nothing

I just get a "The page cannot be displayed" page

I've cut it down to 1 parameter to make testing easier (nice theory anyway ) and not used the With so I can make sure what's what. I've got a global.asa holding the connection information, don't know if that affects anything?!?

Apart from that I think everything is the same syntactically

thanks

 

[gus121]

Hi i noticed a few syntax errors. If you are inputing text you have to explicitly state the size of the fields (this is not needed for interger values as they are not fixed size fields). You will also need to include an abovbs.inc file on the page if you do not have one microsoft puts one in your web directory by default.
Does the stored proc work from Query analyser? What error do you get back?

See this article

http://www.asp101.com/articles/john/adovbs/default.asp

thanks


Dim dbConn, rsResult
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open Application("Connection2_ConnectionString"), Application("Connection2_RuntimeUserName"), Application("Connection2_RuntimePassword")
Set rsResult = CreateObject("ADODB.RecordSet")

set dbComm = Server.CreateObject("ADODB.Command")

dbComm.CommandText = "InsertSol2"
dbComm.CommandType = adCmdStoredProc
dbcomm.ActiveConnection = dbConn

dbComm.Parameters.Append dbComm.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
dbComm.Parameters.Append dbComm.CreateParameter("@SolName", adVarchar, adParamInput, <length of field>)
dbComm.Parameters.Append dbComm.CreateParameter("@MyOutputParam", adInteger, adParamOutput)

dbComm.Parameters("@SolName") = "fred bloggs"

rsResult.CursorLocation = adUseClient
rsResult.Open cmd, ,adOpenStatic, adCmdStoredProc
OutputParam = cmd.Parameters("@MyOutputParam")
Set rsResult.ActiveConnection = Nothing

-Gus

 

[jakeyg]

Looking at

http://sqlteam.com/forums/topic.asp?TOPIC_ID=16356

I've got the SP as

CREATE PROCEDURE InsertSol3
    @SolName  VARCHAR(255)
AS
declare @MyOutputParam int

INSERT INTO Solicitor (SolicitorName) VALUES (@SolName)
set @MyOutputParam = @@IDENTITY
return @MyOutputParam
GO

the ASP code is the same with the field length added and I've #INCLUDE'd abovbs.inc

The record gets created in the database fine, but I can't retrieve the return parameter it goes to a "The page cannot be displayed" page

if I comment out the line

OutputParam = dbComm.Parameters("@MyOutputParam")

then it doesn't error , but I don't get my parameter back :-(

and sorry for low-jacking this thread

 

[gus121]

Hi it sounds if you dont have error handling set up correctly. On your development web server. As you are unable to get an error message back.

I would find developing impossible if i did not have some error handling

I dont know if this artical is relevent to you:

http://www.asp101.com/articles/stuart/asperrormsgs/default.asp

-Gus

 

[gus121]

The above stored proc will not output a parameter. Declared Parameters are just used internaly within a stored proc.

Try this:

CREATE PROCEDURE InsertSol2
    @SolName  VARCHAR(255),
    @MyOutputParam int OUTPUT
AS
SET NOCOUNT ON

  INSERT INTO Solictor (SolictorName)
  VALUES (@SolName)
  
SELECT @MyOutputParam = @@ROWCOUNT

Print @MyOutputParam --this only used for testing
GO

In Query Analyser open a new window

Select results in text from the top menu.

Type the following to test run the stored proc:

Exec InsertSol2 'test1',''

do you get a result

-Gus

 

[gus121]

sorry i meant to type this:
SELECT @MyOutputParam = @@IDENTITY

-Gus

 

[jakeyg]

yeah, that brings back the right number in query analyzer and my code too

I'll have a look at that error message page and implement them next I think, should make it easier

thanks for the awesome help