Obtaining Scope_Identity() value in VB.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Obtaining Scope_Identity() value in VB.NET

Author

Topic

globemast
Starting Member

32 Posts

Posted - 2005-03-21 : 12:07:45

Hello,

I have the following SQL command in VB.NET to update a Table on my SQL 2000 server and then i wan to retrieve the key of the newly inserted row. I know this can be done either by @@IDENTITY or be Scope_Identity(). Though i don't know how to reference the specific value returned from the SELECT statement.

Dim cmd As New SqlCommand("insert into NewMail(MessageID,FromAddr,ReplyTo,MailTime,Subject,contentType,Encoding,Charset,Bound,AttachNum,AttachType,AttachFile,MailBody)
values (@MessageID,@FromAddr,@ReplyTo,@MailTime,@Subject,@contentType,@Encoding,@Charset,@Bound,@AttachNum,@AttachType,@AttachFile,@MailBody);
Select @Identity1=Scope_Identity()", conn)

How can i store the value returned by Scope_Identity() to a variable so i can use is later on.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-21 : 13:35:20

Do this in a stored procedure and output the identity value.

CREATE PROC SomeProc
(InputList..., @Identity1 int OUTPUT)
AS

DECLARE @err int

INSERT INTO NewMail(...)
VALUES ...

SELECT @Identity1 = SCOPE_IDENTITY(), @err = @@ERROR

RETURN @err

GO

Tara

Kristen
Test

22859 Posts

Posted - 2005-03-21 : 14:47:43

If you don't want to use Stored Procedures you should be able to return it as a result set.

Where are your variables @MessageID, @FromAddr, @ReplyTo, ... getting declared and assigned a value?

Dim cmd As New SqlCommand("insert into NewMail(MessageID, FromAddr, ReplyTo, MailTime, Subject, contentType, Encoding, Charset, Bound, AttachNum, AttachType, AttachFile, MailBody)
values (123, 'xxx@foo.bar', 'Me@MyDomain.com', 'SomeTime', 'My subject', 'contentType', 'Encoding', 'Charset', 'Bound', 'AttachNum', 'AttachType', 'AttachFile', 'MailBody');
SELECT [Identity]=Scope_Identity(), [ErrNo]=@@ERROR, [RowCount] = @@ROWCOUNT", conn)

Kristen

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-21 : 16:01:31

Do yourself a favor, follow Tara's advice...

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

globemast
Starting Member

32 Posts

Posted - 2005-03-21 : 17:07:41

Because i am a newbie regarding SQL, what will a stored procedure benefit me in this case?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-21 : 17:54:24

Security - (Search for "Injection Attack" to find out why)
Efficiency - Since the code resides on the server it can attempt to reuse the execution plan instead of recompiling.
Ease of Rollout - Any changes can be enforced at one spot instead of having to upgrade every copy of the application.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

globemast
Starting Member

32 Posts

Posted - 2005-03-21 : 18:09:11

Well here is the Stored Procedure.

CREATE PROCEDURE spAddNewMail
@MessageID text,
@FromAddr text,
@ReplyTo text,
@MailTime text,
@Subject text,
@contentType text,
@Encoding text,
@Charset text,
@Bound text,
@AttachNum int,
@AttachType text,
@AttachFile text,
@MailBody text,
@identity1 int OUTPUT

AS

insert into NewMail(MessageID,FromAddr,ReplyTo,MailTime,Subject,contentType,Encoding,Charset,Bound,AttachNum,AttachType,AttachFile,MailBody)
values (@MessageID,@FromAddr,@ReplyTo,@MailTime,@Subject,@contentType,@Encoding,@Charset,@Bound,@AttachNum,@AttachType,@AttachFile,@MailBody)

SELECT @identity1=SCOPE_IDENTITY()
GO

Also here are my input parameters:

cmd.Parameters.Add("@MessageID", SqlDbType.Text)
cmd.Parameters.Add("@FromAddr", SqlDbType.Text)
cmd.Parameters.Add("@ReplyTo", SqlDbType.Text)
cmd.Parameters.Add("@MailTime", SqlDbType.Text)
cmd.Parameters.Add("@Subject", SqlDbType.Text)
cmd.Parameters.Add("@contentType", SqlDbType.Text)
cmd.Parameters.Add("@Encoding", SqlDbType.Text)
cmd.Parameters.Add("@Charset", SqlDbType.Text)
cmd.Parameters.Add("@Bound", SqlDbType.Text)
cmd.Parameters.Add("@AttachNum", SqlDbType.Int)
cmd.Parameters.Add("@AttachType", SqlDbType.Text)
cmd.Parameters.Add("@AttachFile", SqlDbType.Text)
cmd.Parameters.Add("@MailBody", SqlDbType.Text)
cmd.Parameters.Add("@identity1", SqlDbType.Int)

Though when i execute the following error appears:

"Procedure 'spAddNewMail' expects parameter '@identity1', which was not supplied."

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-21 : 23:53:30

Hi,

quote:
cmd.Parameters.Add("@identity1", SqlDbType.Int)

The above parameter type should be an "OUTPUT" type, then only you can receive that identity value.
When you create a parameter, you have to specify as "Output".

:) While we stop to think, we often miss our opportunity :)

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 01:58:10

"Do yourself a favor, follow Tara's advice..."

Yeah, I agree, and that's how I would do it. But its a huge learning curve if globemast
has to get something shipped by Friday ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources