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

[Stu Lock]

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

 

[Cor Ligthert]

Hi Stu,

Why not make your procedure first simple and set all constants in it, mostly
is than changing that for parameters a piece of cake

And not a SP however a normal Select changing that is the same piece of cake

Just an Idea

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

 

[Ron Allen]

Stu,
Set the Direction property of the @NewID parameter to
ParameterDirection.Output to get the return value.
Ron Allen

 

[Stu]

That did it.
Many thanks.

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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Greg Burns]

guys,

he has already set the parameter direction to Output and he is not even
using a Return value in his sproc.

Greg

 

[Frans Bouma [C# MVP]]

Answer inline ->

** Repost from sqlserver group **

Hi,

I have a stored procedure:

--/ snip /--
CREATE PROCEDURE sp_AddEditUsers

do not prefix your procs with 'sp_'. SqlServer will first check the main db
for procs with 'sp_' prefix, prefix with pr_ or other prefix.

(
@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

NOCOUNT ON will disable concurrency checks in ADO.NET, as a proc will then
always return -1. Only do this when you don't use datasets which are saved in
one go.

(@UserName,@Password);
SELECT @NewID = @@IDENTITY;

if you're on SqlServer 2000, use SCOPE_IDENTITY() instead of @@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

You don't need to do this, every parameter is InOut by default.

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?

You should do:
cmd.ExecuteNonQuery()
m_Users_ID = cmd.Parameters("@NewID").Value

FB

 

[Cor Ligthert]

Hi Frans,

This answer is so old that you can search for it a on Google already and
find the answer by Greg Burns given in this thread in this newsgroup and in
the language.VB group.

He gave it about 8 hours ago with a better alternative too and Stu said
about that did it, many thanks to Greg.

Cor

 

[Greg Burns]

This answer is so old that you can search for it a on Google already
LOL

But he makes a lot of good points!

I should have mentioned the sp_ prefix issue too.

He is also correct about using ExecuteNonQuery. Since, all output is coming
back via parameters, it makes no sense to use ExecuteScalar.

I do have to disagree with the default direction for a parameter. It is not
InputOutput, it is just Input.

I am curious about the SET NOCOUNT ON. I use this all the time. Frans,
could you elaborate? Should he not be using it here, given his sproc?

Greg

 

[Frans Bouma [C# MVP]]

Hi Frans,

This answer is so old that you can search for it a on Google already and
find the answer by Greg Burns given in this thread in this newsgroup and in
the language.VB group.

He gave it about 8 hours ago with a better alternative too and Stu said
about that did it, many thanks to Greg.

Errr.... the answer given was execute scalar, which was wrong, as it wasn't
a scalar query, but a normal 'action' query. A scalar query is SELECT
@@IDENTITY or SELECT SCOPE_IDENTITY().

Second, if I want to answer a question, I do so, mr. Ligthert and the last
person I want to ask permission to is you. Since when is answering questions
forbidden after a person named Cor Ligthert has 'answered' it, with a wrong
answer to begin with and skipping all kinds of errors in the proc as well.

Frans.

 

[Frans Bouma [C# MVP]]

LOL

But he makes a lot of good points!

yeah, on the contrary of some 'specialists' who think they are so damn good
they have to flame a person who answers the darn question as well.

I should have mentioned the sp_ prefix issue too.

He is also correct about using ExecuteNonQuery. Since, all output is
coming back via parameters, it makes no sense to use ExecuteScalar.

I do have to disagree with the default direction for a parameter. It is
not InputOutput, it is just Input.

no, it's not. SqlServer's IN is always INOUT. But that's a minor detail

I am curious about the SET NOCOUNT ON. I use this all the time. Frans,
could you elaborate? Should he not be using it here, given his sproc?

NOCOUNT ON makes every query to return -1 for the amount of rows affected.
Now, if you write solid ADO.NET code, you have stuff like this:

// set up cmd here

// execute query
int amountRowsAffected = cmd.ExecuteNonQuery();
if(amountRowsAffected<=0)
{
// failed

}
else
{
// succeeded
}

so you can anticipate on things, like an update with a timestamp predicate,
which fails when the timestamp column has been changed: rows affected will be
0, so the query failed so you can report something back to the user. With
NOCOUNT ON you always have -1 as a result, which looks like a failure.

DataAdapters which execute UPDATE queries, throw a DBConcurrencyException
when the query returns 0 or lower. You therefore should be careful when you
use NOCOUNT ON. It can save some of performance, but your code should be
written with the usage of NOCOUNT ON in mind. It is only useful also when you
for example update a lot of rows, say 1000 or more. Rowcounting when 1 row
is updated is hardly a performance hit, if at all.

FB

 

[Cor Ligthert]

Frans,

Second, if I want to answer a question, I do so, mr. Ligthert and the last
person I want to ask permission to is you. Since when is answering questions
forbidden after a person named Cor Ligthert has 'answered' it, with a wrong
answer to begin with and skipping all kinds of errors in the proc as well.

Frans.

In the other thread, you write I am flaming you, I think this is Flaming. I
nowhere wrote that you need my permission, I nowhere wrote about you "a
person named Frans Bouma", and by setting things between quotes in the other
message give an opinion about me I did not about you. I assume that you can
prove what you wrote.

I only showed you that the right answer was placed 8 hours before you and in
my opinion, it is than a simple thing to give credits to Greg, in by
instance the same way as it was done by someone else in the language.vb
group and which is at least by me appreciated. It is just a question of
politeness.

You have written that:
I have told you need my permission; show it where I wrote it?
I was flaming you; show me where I did it?
I am not a 'specialist'; show me where I wrote that about this problem?
I gave a wrong answer; show me what is wrong in my answer?
I have forbidden you to answer, show me where?

So prove that, however when you cannot, than it shows in my opinion only
something about you.

Cor

 

[Frans Bouma [C# MVP]]

Frans,

In the other thread, you write I am flaming you, I think this is Flaming. I
nowhere wrote that you need my permission, I nowhere wrote about you "a
person named Frans Bouma", and by setting things between quotes in the other
message give an opinion about me I did not about you. I assume that you can
prove what you wrote.

Errr, I answer a question of a person. You and others answered that person
as well, with wrong answers. I thought, lets give this person a real answer.
Then YOU find it necessary to reply to my posting that my answer was way too
late and someone else has answered it already, i.o.w.: why on earth did I
answer the question ?

No offence, but I invested time to answer the question. who are you then to
tell me that I didn't have to do that, with a lame remark about google
groups? Did you answer the question with "look on google groups" as well, as
the answer was probably there already in another thread? No.

I only showed you that the right answer was placed 8 hours before you and in
my opinion, it is than a simple thing to give credits to Greg, in by
instance the same way as it was done by someone else in the language.vb
group and which is at least by me appreciated. It is just a question of
politeness.

If I answer a question, I look at the question and answer with my own
knowledge. Sorry to tell you this, but I definitely didn't need the other,
wrong, answers to answer this question. Why would I answer a question with
the same answer given by another person? And even if I did, why would that
make YOU think I first read that other answer, thought "whoa, that's clever"
and use that new knowledge to answer the question?

Like I didn't know the answer in the first place. I breath ADO.NET code now
for over 2.5 years for 7 days a week, what do you think? And about the .vb
newsgroup, I read the ADO.NET newsgroup, if someone in the VB.NET newsgroup
answers teh same question, I definitely won't see it, as I don't read that
newsgroup (perhaps you didn't think of that, but that can happen
occasionally, especially if you're more interested in C# than VB.NET)

You have written that:
I have told you need my permission; show it where I wrote it?

I answered a question, PROPERLY, (which you didn't btw), then MY reply gets
a reply from you I am too late because it was on google groups already
blablabla. WHo are you to tell me when to answer questions?

I was flaming you; show me where I did it?

Cor, als je het niet snapt wat de impact is van wat je zelf opschrijft, denk
dan eerst ff na, WAT je opschrijft, want je reply op mijn reply was op zn
hollands gezegd "mierengeneuk doordenkt met gezeik".

I am not a 'specialist'; show me where I wrote that about this problem?

well, apparently your answer as well as the others was the right one and my
answer was not appropriate?

I gave a wrong answer; show me what is wrong in my answer?

"Why not make your procedure first simple and set all constants in it,
mostly is than changing that for parameters a piece of cake"

Does that answer the person's problems with sp_, @@IDENTITY, ExecuteScalar,
NOCOUNT ON, OUTPUT parameter direction?

I have forbidden you to answer, show me where?

I answered a question, because I thought that would be appropriate. You then
thought it was NECESSARY to tell me that what I did was not appropriate, as I
was apparently way too late.

"He gave it about 8 hours ago with a better alternative too and Stu said
about that did it, many thanks to Greg."

where is he doing things 'better' ? he missed some vital points.

But, Cor, if you want to answer questions here so I can spend more time on
my own projects, be my guest. If not, why not spend your time on answering
questions instead of telling others that they are out of line with their
answers?

FB

 

[Greg Burns]

I do have to disagree with the default direction for a parameter. It is

no, it's not. SqlServer's IN is always INOUT. But that's a minor detail

The online help says different:

SqlParameter.Direction Property

Gets or sets a value indicating whether the parameter is input-only,
output-only, bidirectional, or a stored procedure return value parameter.

Property Value
One of the ParameterDirection values. The default is Input.


But since you can't always trust what you read, I threw together a test.

CREATE PROCEDURE usp_GetValue
@value int OUTPUT -- removing OUTPUT doesn't work either
AS
SELECT @value=@value+1


Dim cmd As New SqlCommand("usp_GetValue", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@value", SqlDbType.Int).Value = 1
cmd.Parameters("@value").Direction = ParameterDirection.InputOutput
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch
Finally
cn.Close()
End Try

Dim x As Integer = CType(cmd.Parameters("@value").Value, Integer)

Without this line:
cmd.Parameters("@value").Direction = ParameterDirection.InputOutput
x = 1
with the line
x = 2

I was thinking maybe things would be different in Query Analyzer, but I
couldn't get it to do as you say there either.

DECLARE @x int
SET @x=1
EXEC usp_GetValue @x --OUTPUT (again, commented out for test)
PRINT @x

returns 1, not 2

I don't mean to sound like a smart*ss. Just want to get to the bottom of
this.

Greg

 

[Frans Bouma [C# MVP]]

Thanks for the tests, Greg.

I did some checking after reading your tests as well, and got the same
results.

Digging deeper into what seemed to be the cause of my understanding of the
matter was a confusion on my side, where I confused a different situation and
what is in context with the situation discussed. In all my code generators I
do it as required, but messed up with the posting. Mainly it comes down to
the fact that you can write InputOutput for an input or an output, which was
the root of the confusion on my side and I take all the blame for this crappy
misunderstanding/mess

Sorry to have wasted your time, Greg.

Frans.

 

[Greg Burns]

No problem. Now if we can just get you and Cor to friends again. :^)

 

[Frans Bouma [C# MVP]]

No problem. Now if we can just get you and Cor to friends again. :^)

heh Well, I don't have a problem with anybody, as long as people don't
critisize me putting effort in answering questions

FB

 

[Cor Ligthert]

Cor, als je het niet snapt wat de impact is van wat je zelf opschrijft, denk
dan eerst ff na, WAT je opschrijft, want je reply op mijn reply was op zn
hollands gezegd "mierengeneuk doordenkt met gezeik".

Why are you afraid to do this even more offending message in English?

When you could read my message and answered that agressivly, you could have
read the messages from others as well and respect the messages from people
who are not often a visitor to this newsgroups and help to contribute.

When you had did this to me, it would not have bottered me. I am to much a
regular to this newsgroup that it would show only something about you.

Although I can not remember me that I saw yours behaviour earlier in this
newsgroup, there is a lot of respect here.

Cor