Retrieving the @@IDENTITY value from a SP in VB.Net

  • Thread starter Thread starter Stu Lock
  • Start date Start date
S

Stu Lock

Hi,

I have a stored procedure:

--/ snip /--
CREATE PROCEDURE sp_AddEditUsers
(
@Users_ID int,
@UserName nvarchar(80),
@Password nvarchar(80),
@NewID int output
)
AS

IF @Users_ID = 0
BEGIN
/*do insert here */
SET NOCOUNT ON; INSERT INTO UsersNEW (UserName,Password) VALUES
(@UserName,@Password);
SELECT @NewID = @@IDENTITY;
SET NOCOUNT OFF:
END
ELSE
BEGIN
/* It's an update */
UPDATE UsersNEW SET UserName = @UserName, Password = @Password WHERE
Users_ID = @Users_ID;
SELECT @NewID = @Users_ID;
END
GO
--/ snip /--

I'm trying to get the SP to return the User_ID within VB.Net. Currently I am
trying:

--/ snip /--
Dim dr As SqlDataReader
Dim cn As New SqlConnection("MyConnStr")
cn.Open()
Dim cmd As New SqlCommand("sp_AddEditUsers", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Users_ID", SqlDbType.Int)
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
cmd.Parameters.Add("@Password", SqlDbType.NVarChar)
cmd.Parameters.Add("@NewID", SqlDbType.Int)
cmd.Parameters("@NewID").Direction = ParameterDirection.Output
cmd.Parameters("@Users_ID").Value = m_Users_ID
cmd.Parameters("@UserName").Value = m_UserName
cmd.Parameters("@Password").Value = m_Password
'Grab new users ID???
m_Users_ID = CType(cmd.ExecuteScalar(), Integer)
--/ snip /--

But this returns nothing. I have also tried the ExecuteReader method and
tried to read the value from a datareader but no records are returned. What
am I doing wrong?

Thanks in advance,

Stu
 
Hi Stu,

Did you know that there is an special newsgroup for this kind of question.

microsoft.public.dotnet.adonet

Cor
 
Wouldn't ExecuteScalar only give you the the sproc's Return value.

You want something like
m_Users_ID=CType(cmd.Parameters("@NewID").Value, Integer)

If using SQL 2000, I would suggest switching to new SCOPE_IDENTITY() instead
of using @@IDENTITY

Greg
 
Stu,
You need to use code similar to what Greg showed:

You want something like
m_Users_ID=CType(cmd.Parameters("@NewID").Value, Integer)


ExecuteScalar is used when you are running a Select statement that will only
return 1 row & 1 column.

Example of using ExecuteScaler:

Const cmdText As String = "Select Count(*) From UsersNEW"
Dim cmd As New SqlCommand(cmdText, cn)
Dim numberOfUsers As Integer
numberOfUsers = CType(cmd.ExecuteScalar(), Integer)

If you really want to use ExecuteScalar
SELECT @NewID = @@IDENTITY;
Click to expand...
Select @NewID

Note: As Greg pointed out you should use SCOPE_IDENTITY if you are using SQL
Server 2000 or higher...

For detailed information on when you should use Output Paramters & when you
would use ExecuteScaler see David Sceppa book "Microsoft ADO.NET - Core
Reference" from MS Press.

If you are doing a lot with ADO.NET I strongly recommend Sceppa's book,
which is a good tutorial on ADO.NET as well as a good desk reference once
you know ADO.NET.

NOTE: You should use output parameters here.

Hope this helps
Jay
 
Back
Top