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

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

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

* "Stu Lock" <s.lock@cergis.com> scripsit:[color=blue]
> I have a stored procedure:
>
> --/ snip /--
> CREATE PROCEDURE sp_AddEditUsers
> (
> @Users_ID int,
> @UserName nvarchar(80),
> @Password nvarchar(80),
> @NewID int output
> )
> AS[/color]

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

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

"Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
news:uzyF6D0ZEHA.1356@TK2MSFTNGP09.phx.gbl...[color=blue]
>* "Stu Lock" <s.lock@cergis.com> scripsit:[color=green]
>> I have a stored procedure:
>>
>> --/ snip /--
>> CREATE PROCEDURE sp_AddEditUsers
>> (
>> @Users_ID int,
>> @UserName nvarchar(80),
>> @Password nvarchar(80),
>> @NewID int output
>> )
>> AS[/color]
>
> 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/>[/color]

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

Hi Stu,

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

microsoft.public.dotnet.adonet

Cor

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

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.lock@cergis.com> wrote in message
news:eTm$BC0ZEHA.2840@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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
>
>[/color]

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

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
[color=blue]
> SELECT @NewID = @@IDENTITY;[/color]
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.lock@cergis.com> wrote in message
news:eTm$BC0ZEHA.2840@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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[/color]
am[color=blue]
> 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.[/color]
What[color=blue]
> am I doing wrong?
>
> Thanks in advance,
>
> Stu
>
>[/color]

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

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

No! My post was only a suggestion.

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