Value in output parameter not accessable

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
Fri Dec 03 08:05:18 CST 2004

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

On Fri, 3 Dec 2004 02:01:02 -0800, Alan Lambert
<AlanLambert@discussions.microsoft.com> wrote:

>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

AlanLambert
Fri Dec 03 08:39:01 CST 2004

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

Thanks for the help.

Alan

"Mary Chipman" wrote:

> 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
>
> On Fri, 3 Dec 2004 02:01:02 -0800, Alan Lambert
> <AlanLambert@discussions.microsoft.com> wrote:
>
> >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
>
>