Problems returning the ID of the last record added using a Stored Procedure

[MarkT]

I'm making an app to add test questions to a SQL DB. I'm planning on using a Stored procedure to insert the question and then would like to return the Identity so I can use that to do the inserts for the answers into the Answers table. So far I have this as the stored procedure.

CREATE PROCEDURE sp_AddQuestion
(
	@question AS VARCHAR (8000),
	@Identity AS INT
)
AS
	INSERT INTO Questions(questions) values(@question)
	RETURN SELECT @@IDENTITY as 'Identity'
GO

and in the form I'm using this code

Private Sub cmdInsert_Click()
Dim objCmd As ADODB.Command
Dim rs As ADODB.Recordset
 
    Set objCmd = New ADODB.Command
    With objCmd
        Set .ActiveConnection = Cn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_AddQuestion"
        
        .Parameters.Append .CreateParameter("@question", adVarChar, adParamInput, 300, "Test question Input")
        .Parameters.Append .CreateParameter("@Identity", adInteger, adParamOutput, 4)
    
        Set rs = .Execute
        MsgBox rs.Fields(0)
    End With
End Sub

When I try to run this I'm getting the following error on the execute line.

Formal parameter ‘@Identity’ was defined as OUTPUT but the actual parameter not declared OUTPUT.

What does this error mean and how can I return the id of the last record entered?

[szlamany]

Several ways to do this - but since you want to use an OUTPUT parameter try this...

CREATE PROCEDURE sp_AddQuestion
(
	@question AS VARCHAR (8000),
	@Ident_Return AS INT OUTPUT
)
AS
                SET NOCOUNT ON
	INSERT INTO Questions(questions) values(@question)
	SET @IDENT_RETURN=@@IDENTITY
GO

Then maybe something more like this in VB

Private Sub cmdInsert_Click()
Dim objCmd As ADODB.Command
' Dim rs As ADODB.Recordset
 
    Set objCmd = New ADODB.Command
    With objCmd
        Set .ActiveConnection = Cn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_AddQuestion"
        
        .Parameters.Append .CreateParameter("@question", adVarChar, adParamInput, 300, "Test question Input")
        .Parameters.Append .CreateParameter("@Ident_Return", adInteger, adParamOutput, 4)
    
        objCmd.Execute
        MsgBox objCmd.Parameters(1).Value ' rs.Fields(0)
    End With
End Sub

I've done all this free-hand - have no VB or SQL on this machine.

[MarkT]

Thank you szlamany! That worked.