Difficulty Retrieving Output Parameters

[Guest]

I am working on a framework for my internal office affairs, but I've run into a problem that I just cannot figure out.

I am writing a new record to the database and expecting to get the @@IDENTITY of the record for my object.

Here's the code that is giving me problems:
Dim Cmd As New SqlClient.SqlCommand("usp_AddClientUser", SQLConn)
Cmd.CommandType = CommandType.StoredProcedure

With Cmd.Parameters
.Add("@ClientID", Me.ClientID)
.Add("@FirstName", Me.FirstName)
.Add("@LastName", Me.LastName)
.Add("@Title", Me.Title)
.Add("@UserID", "")
End With

Try
Dim newUserID As Integer
SQLConn.Open()
Cmd.ExecuteNonQuery()
SQLConn.Close()
newUserID = Cmd.Parameters("@UserID").Value
Return newUserID
Catch sqlex As SqlClient.SqlException
Throw New Exception(sqlex.Message)
Catch ex As Exception
Return -1
End Try

And here's the stored procedure:

ALTER PROCEDURE usp_AddClientUser
@ClientID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Title nvarchar(50),
@UserID int OUTPUT
AS
INSERT INTO tblClientUsers
(ClientID, FirstName, LastName, Title)
VALUES
(@ClientID, @FirstName, @LastName, @Title)

SET @UserID = @@IDENTITY

When I run the procedure with my object, I am getting an empty value; however, this is the result if I run the stored procedure with SQL Query Analyzer:

(1 row(s) affected)

Stored Procedure: TFF_TimeClock.dbo.usp_AddClientUser
Return Code = 0
Output Parameter(s):
@UserID = 21

I have this same routine working with another object and I'm not having any difficulties with it. Any help would be appreciated!

 

[Tom Robertson]

You didn't identify the UserID parameter's direction as output or
identify its type. Try swapping your .Add("@UserID", "") line with the one
below and see if that helps...

..Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output

HTH,

Tom

I am working on a framework for my internal office affairs, but I've run

into a problem that I just cannot figure out.

I am writing a new record to the database and expecting to get the

@@IDENTITY of the record for my object.

Here's the code that is giving me problems:
Dim Cmd As New SqlClient.SqlCommand("usp_AddClientUser", SQLConn)
Cmd.CommandType = CommandType.StoredProcedure

With Cmd.Parameters
.Add("@ClientID", Me.ClientID)
.Add("@FirstName", Me.FirstName)
.Add("@LastName", Me.LastName)
.Add("@Title", Me.Title)
.Add("@UserID", "")
End With

Try
Dim newUserID As Integer
SQLConn.Open()
Cmd.ExecuteNonQuery()
SQLConn.Close()
newUserID = Cmd.Parameters("@UserID").Value
Return newUserID
Catch sqlex As SqlClient.SqlException
Throw New Exception(sqlex.Message)
Catch ex As Exception
Return -1
End Try

And here's the stored procedure:

ALTER PROCEDURE usp_AddClientUser
@ClientID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Title nvarchar(50),
@UserID int OUTPUT
AS
INSERT INTO tblClientUsers
(ClientID, FirstName, LastName, Title)
VALUES
(@ClientID, @FirstName, @LastName, @Title)

SET @UserID = @@IDENTITY

When I run the procedure with my object, I am getting an empty value;

however, this is the result if I run the stored procedure with SQL Query
Analyzer:

(1 row(s) affected)

Stored Procedure: TFF_TimeClock.dbo.usp_AddClientUser
Return Code = 0
Output Parameter(s):
@UserID = 21

I have this same routine working with another object and I'm not having

any difficulties with it. Any help would be appreciated!

 

[Guest]

Tom,

Thanks so much for the help. This worked like a charm!