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

[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

[Herfried K. Wagner [MVP]]

* "Stu Lock" <s.****@cergis.com> scripsit:

I have a stored procedure:

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

There is a separate group for .NET + database related questions available:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

[Stu Lock]

"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:uz**************@TK2MSFTNGP09.phx.gbl...

* "Stu Lock" <s.****@cergis.com> scripsit:

I have a stored procedure:

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

There is a separate group for .NET + database related questions available:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

[Cor Ligthert]

Hi Stu,

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

microsoft.public.dotnet.adonet

Cor

[Greg Burns]

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 Lock" <s.****@cergis.com> wrote in message
news:eT**************@TK2MSFTNGP11.phx.gbl...

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

[Jay B. Harlow [MVP - Outlook]]

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

"Stu Lock" <s.****@cergis.com> wrote in message
news:eT**************@TK2MSFTNGP11.phx.gbl... 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

[Herfried K. Wagner [MVP]]

* "Stu Lock" <s.****@cergis.com> scripsit:
[...]

No! My post was only a suggestion.

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>