Value in output parameter not accessable

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
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
>