Getting a Return Value from a Stored Procedure in VBA

[KoldKay]

Hi all, I have a Stored procedure, here:

PROCEDURE [dbo].[new_tbox]
-- Add the parameters for the stored procedure here
@slide_id int,
@shape_id int,
@cnt_typ_id int
AS

BEGIN
DECLARE @cnt_id int, @tbox_id int
INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)

SET @cnt_id = @@IDENTITY

INSERT tbox_cnt VALUES(@cnt_id)
SET @tbox_id = @@IDENTITY
RETURN @tbox_id

END
That returns the variable. This works in SQL Server, and I get the
return value

My VBA code from my ADP is as such:

With cmd
.ActiveConnection = ConnStr
.CommandText = "dbo.new_tbox"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@slide_id", adInteger,
adParamInput, , Forms("new_content").slide_id.Value)
.Parameters.Append .CreateParameter("@shape_id", adInteger,
adParamInput, , Forms("new_content").Combo15.Value)
.Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
adParamInput, , Forms("new_content").Combo0.Value)
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Execute
Debug.Print .Parameters("return_value")
End With

However, this does not work, I get that I have too many arguments
assigned to the stored procedure
what am I doing wrong?
thanks
Michael

 

[RoyVidar]

Hi all, I have a Stored procedure, here:

PROCEDURE [dbo].[new_tbox]
-- Add the parameters for the stored procedure here
@slide_id int,
@shape_id int,
@cnt_typ_id int
AS

BEGIN
DECLARE @cnt_id int, @tbox_id int
INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)

SET @cnt_id = @@IDENTITY

INSERT tbox_cnt VALUES(@cnt_id)
SET @tbox_id = @@IDENTITY
RETURN @tbox_id

END
That returns the variable. This works in SQL Server, and I get the
return value

My VBA code from my ADP is as such:

With cmd
.ActiveConnection = ConnStr
.CommandText = "dbo.new_tbox"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@slide_id", adInteger,
adParamInput, , Forms("new_content").slide_id.Value)
.Parameters.Append .CreateParameter("@shape_id", adInteger,
adParamInput, , Forms("new_content").Combo15.Value)
.Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
adParamInput, , Forms("new_content").Combo0.Value)
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Execute
Debug.Print .Parameters("return_value")
End With

However, this does not work, I get that I have too many arguments
assigned to the stored procedure
what am I doing wrong?
thanks
Michael

Try declaring the return value as the first parameter, i e

.Parameters.Append .CreateParameter("return_value", adInteger, _
adParamReturnValue)
.Parameters.Append .CreateParameter("@slide_id", adInteger, _
adParamInput, , Forms("new_content").slide_id.Value)
....

 

[KoldKay]

Hi all, I have a Stored procedure, here:

PROCEDURE [dbo].[new_tbox]
-- Add the parameters for the stored procedure here
@slide_id int,
@shape_id int,
@cnt_typ_id int
AS

BEGIN
DECLARE @cnt_id int, @tbox_id int
INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)

SET @cnt_id = @@IDENTITY

INSERT tbox_cnt VALUES(@cnt_id)
SET @tbox_id = @@IDENTITY
RETURN @tbox_id

END
That returns the variable. This works in SQL Server, and I get the
return value

My VBA code from my ADP is as such:

With cmd
.ActiveConnection = ConnStr
.CommandText = "dbo.new_tbox"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@slide_id", adInteger,
adParamInput, , Forms("new_content").slide_id.Value)
.Parameters.Append .CreateParameter("@shape_id", adInteger,
adParamInput, , Forms("new_content").Combo15.Value)
.Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
adParamInput, , Forms("new_content").Combo0.Value)
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Execute
Debug.Print .Parameters("return_value")
End With

However, this does not work, I get that I have too many arguments
assigned to the stored procedure
what am I doing wrong?
thanks
Michael

Try declaring the return value as the first parameter, i e

.Parameters.Append .CreateParameter("return_value", adInteger, _
adParamReturnValue)
.Parameters.Append .CreateParameter("@slide_id", adInteger, _
adParamInput, , Forms("new_content").slide_id.Value)
...

Hi Roy,
Thanks, that worked perfectly!
Much appreciated.
Michael