I'm trying to use a SQL Server stored procedure with ASP:
ALTER PROCEDURE [dbo].[user_insert]
@firstName NVARCHAR(50) ,
@lastName NVARCHAR(50) ,
@cityid INT ,
@email NVARCHAR(100) ,
@password NVARCHAR(12) ,
@Affiliate INT
AS
BEGIN
DECLARE @index1 INT
SET @index1 = 0
IF ( NOT EXISTS ( SELECT * FROM dbo.tbl_users
WHERE user_email = @email ) )
BEGIN
INSERT INTO dbo.tbl_users
( user_first_name ,
user_last_name ,
city_id ,
user_email ,
user_password ,
Affiliate_id
)
VALUES ( @firstName ,
@lastName ,
@cityid ,
@email ,
@password ,
@Affiliate
)
SET @index1 = ( SELECT @@IDENTITY
END
RETURN @index1
END
and the ASP is
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = conn
oCmd.CommandText = "user_insert"
oCmd.CommandType = 4
oCmd.Parameters.Append oCmd.CreateParameter("firstName", 203, 1, 100, "1")
oCmd.Parameters.Append oCmd.CreateParameter("lastName", 203, 1, 100, "2" )
oCmd.Parameters.Append oCmd.CreateParameter("cityid", 3, 1, 2, 1)
oCmd.Parameters.Append oCmd.CreateParameter("email", 200, 1, 100, txtmail)
oCmd.Parameters.Append oCmd.CreateParameter("password", 203, 1, 12, "2" )
oCmd.Parameters.Append oCmd.CreateParameter("Affiliate", 3, 1, 3, 1)
oCmd.Parameters.Append oCmd.CreateParameter("@index1", 3, 2)
Set rs = oCmd.Execute
response.write oCmd.Parameters("@index1").Value &"<br>"
Set oCmd = Nothing
If I run the SP in the SQL Server terminal it's working, but when I use the asp code, if it's a "new user", I don't get any value returning "index1"
What can I do?
Return Value in SQL Server Stored ProcedureIn default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.
The RETURN statement is used to unconditionally and immediately end an SQL procedure by returning the flow of control to the caller of the stored procedure. When the RETURN statement runs, it must return an integer value. If the return value is not provided, the default is 0.
Change to adParamReturnValue as the other answer states.
You also need to suppress the first resultset, using SET NOCOUNT ON. Also you should stop using @@IDENTITY, use SCOPE_IDENTITY instead.
ALTER PROCEDURE [dbo].[user_insert]
@firstName nvarchar(50),
@lastName nvarchar(50),
@cityid int,
@email nvarchar(100),
@password nvarchar(12),
@Affiliate int
AS BEGIN
SET NOCOUNT ON
IF (not EXISTS (SELECT * FROM dbo.tbl_users WHERE user_email=@email))
begin
INSERT INTO dbo.tbl_users(user_first_name, user_last_name, city_id, user_email, user_password, Affiliate_id)
VALUES(@firstName, @lastName, @cityid, @email, @password, @Affiliate)
--set
RETURN SCOPE_IDENTITY()
end
RETURN 0
end
Note: You can even omit the RETURN 0 at the end, since 0 is the default return result.
Your parameter direction is adParamOutput
(2), but should be adParamReturnValue
(4):
oCmd.Parameters.Append oCmd.CreateParameter("@index1", 3, 4)
See the ParamDirctionEnum
that CreateParameter
takes.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With