Difficulty Retrieving Output Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Chris Q. said:
I am working on a framework for my internal office affairs, but I've run
Click to expand...
into a problem that I just cannot figure out.
I am writing a new record to the database and expecting to get the
Click to expand...
@@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;
Click to expand...
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
Click to expand...
any difficulties with it. Any help would be appreciated!
 
Back
Top