Value in output parameter not accessable

[Guest]

I am using the Microsoft Data Application Block to access a database and am
trying to execute the following stored procedure:

CREATE PROCEDURE WhiteboardPostItem
@Id int OUTPUT,
@Subject varchar(255),
@Message varchar(2000),
@PostedBy varchar(100)
AS
INSERT INTO Whiteboard(Subject, Message, PostedBy, DatePosted)
VALUES (@Subject, @Message, @PostedBy, getdate())

SELECT @Id = @@IDENTITY
GO

This procedure is called from the following code:

Public Shared Function PostItem(ByVal Subject As String, ByVal Message As
String, ByVal PostedBy As String) As Integer
Dim arParams() As SqlParameter = New SqlParameter(3) {}

arParams(0) = New SqlParameter("@Id", SqlDbType.Int)
arParams(0).Direction = ParameterDirection.Output

arParams(1) = New SqlParameter("@Subject", SqlDbType.VarChar, 255)
arParams(1).Value = Subject

arParams(2) = New SqlParameter("@Message", SqlDbType.VarChar, 2000)
arParams(2).Value = Message

arParams(3) = New SqlParameter("@PostedBy", SqlDbType.VarChar, 100)
arParams(3).Value = PostedBy

' Call to Microsoft Data Application Block
SqlHelper.ExecuteNonQuery(ConnectionString(), "WhiteboardPostItem",
arParams)
Return Convert.ToInt32(arParams(0).Value)
End Function

However, the output parameter (arParams(0)) is never set to the identity
value of the inserted row even though the row does get added.

I've checked the stored procedure in query analyser and the return value is
properly set but it does not work through this code. The code in the above
function is now in the format described in a Microsoft example and it still
doesn't work.

I'm sure I'm missing something obvious, but can anyone tell me how to
resolve this as it's driving me mad!

Many thanks

Alan

 

[Mary Chipman]

I'll confine myself to cleaning up the stored procedure for this
feedback. The first line in your sproc should be: SET NOCOUNT ON.
Assign default values to your parameters when you declare them and
validate them before calling the INSERT. Use a RETURN statement to
terminate with an error code indicating validation failure (or set up
output parameters to return an error code and error message you can
display in your client app). If you pass the validation successfully,
stick some error handling on the INSERT by checking immediately after
in an IF...ELSE block to see if the insert succeeded or failed by
selecting @@error and @@rowcount into local variables. If it
succeeded, use SCOPE_IDENTITY, not @@IDENTITY to fetch the value into
the @Id output parameter.

--Mary

 

[Guest]

Useful advice#: I'll certainly amend the stored procedure (and a few others
as well!).

Thanks for the help.

Alan