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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 07/11/04 

Date: Sun, 11 Jul 2004 12:09:28 -0700

Ah no. Set the direction to .ReturnValue to get the RETURN value.
See my articles on handling @@Identity and stored procedure parameters.
www.betav.com/articles.htm 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ron Allen" <rallen@_nospam_src-us.com> wrote in message
news:ub75mH3ZEHA.3112@TK2MSFTNGP09.phx.gbl...
> Stu,
>     Set the Direction property of the @NewID parameter to
> ParameterDirection.Output to get the return value.
> Ron Allen
> "Stu Lock" <s.lock@cergis.com> wrote in message
> news:uSJdDG0ZEHA.3664@TK2MSFTNGP12.phx.gbl...
> > ** Repost from sqlserver group **
> >
> > 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
> >
> >
>
>

Relevant Pages