• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

Returning Scope_Identity() in VB.NET

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.
0
Globemaster
Asked:
Globemaster
  • 3
1 Solution
 
YZlatCommented:
CREATE pROCEDURE spAddNewMail
@MessageID,@FromAddr,
@ReplyTo,
@MailTime,
@Subject,
@contentType,
@Encoding,
@Charset,
@Bound,
@AttachNum,
@AttachType,
@AttachFile,
@MailBody,
@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=@@IDENTITY




myConnection.Open()

myCommand = New SqlCommand( "spAddNewMail", myConnection )
myCommand.CommandType = CommandType.StoredProcedure

 

''add input parameters here


myCommand.ExecuteNonQuery()
 If myCommand.Parameters( "RETURN VALUE" ).Value Then
 
      Response.write(MyCommand.Parameters( "@identity1" ).Value )
 
END If
myConnection.Close()
0
 
GlobemasterAuthor Commented:
Why is it necessary to create a procedure? what will it help me?
0
 
GlobemasterAuthor Commented:
Also should the procedure be created in the SQL Server?
0
 
GlobemasterAuthor Commented:
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."
0
 
FOnderwaterCommented:
You don't have to use a SP if you don't want to... The command you entered is just fine. I presume MessageID is an int.

Dim result as integer 'We will store the identity in here

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 Scope_Identity()", conn)

comm.parameters.add("@MessageID", MsgID)
etc.

conn.open
result = cmd.ExecuteScalar
conn.close

Now we have the scope_identity available...

Ferry
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now