Solved

How do I retreive the value of @@Identity in .net?

Posted on 2004-10-14
35
417 Views
Last Modified: 2012-06-21
I need to get the value of Select @Return = @@Identity from my stored procedure Insert statement and use it on my aspx page. Can't figure it out.
I have tried to reduce my code examples  to the pertinent parts.   I have included the code from my business object, stored procedure and aspx page.  The PrimarKey Field in the database is ReturnID

function from the business object:

Public Function AddReturnInfoToCMRCRGA(strOrderID as integer, strEmail as string, _
                  strfirstname as string,) As string

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("spAddYOWRGARequestToCMRCRGA", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure
            Dim parameterstrOrderID As New SqlParameter("@strOrderID", SqlDbType.NVarChar, 50)
            parameterstrOrderID.Value = strOrderID
            myCommand.Parameters.Add(parameterstrOrderID)

            Dim parameterstrEmail As New SqlParameter("@strEmail", SqlDbType.NVarChar, 50)
            parameterstrEmail.Value = stremail
            myCommand.Parameters.Add(parameterstrEmail)

            Dim parameterReturnID As New SqlParameter("@ReturnID", SqlDbType.Int, 4)
            parameterReturnID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterReturnID)    
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()
             Dim returnID As Integer = CInt(parameterReturnID.Value)
               Return returnId.ToString()
        End Function
******************************************************
sql 2000 stored procedure:
CREATE Procedure spAddYOWRGARequestToCMRCRGA
(
 @strOrderId int,
   @strEmail nvarchar(50),
@returnID int output
)
AS
INSERT INTO CMRC_RGA
(
     OrderID,
    EmailAddress

)
VALUES
(
 @strOrderID,
  @strEmail
)
SELECT
    @ReturnID = @@Identity
GO


****************************************************8
aspx page:

   Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim ReturnId as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  

I NEED THE VALUE OF @@Identity here
                          
0
Comment
Question by:dplsr
  • 14
  • 13
  • 7
  • +1
35 Comments
 
LVL 15

Assisted Solution

by:praneetha
praneetha earned 250 total points
ID: 12310868
SqlParameter sqlParam = myCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                        // set the direction flag so that it will be filled with the return value
                        sqlParam.Direction = ParameterDirection.ReturnValue;
                        
and after executenonquery
      myCommand.ExecuteNonQuery();
      returnValue = (int)myCommand.Parameters["@ReturnValue"].Value;
      Console.Write(returnValue);

this is in C#
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12310943
Dim cnn As New SqlClient.SqlConnection(ConfigurationSettings.AppSettings("EmeraldConnStr"))
            Dim cmd As New SqlClient.SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.jobsInsertJobNumber"
            cmd.Parameters.Add("@CreatedBy", Me.User.Identity.Name)
            If Me.txtCustomerID.Text <> "" Then
                cmd.Parameters.Add("@CustomerID", Me.txtCustomerID.Text)
            Else
                cmd.Parameters.Add("@CustomerID", DBNull.Value)
            End If
            cmd.Parameters.Add("@StartDate", Me.txtStartDate.Text)
            cmd.Parameters.Add("@EndDate", Me.txtEndDate.Text)
            cmd.Parameters.Add("@AssignedStaff", Me.txtAssignedStaff.Text)
            cmd.Parameters.Add("@Description", Me.txtDescription.Text)
            cmd.Parameters.Add("@JobNumber", SqlDbType.Int, 4)
            cmd.Parameters("@JobNumber").Direction = ParameterDirection.Output
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
            ReturnValue = CInt(cmd.Parameters("@JobNumber").Value)
            strJobNumber = CStr(ReturnValue)
            Session("JobNumber") = strJobNumber
            cnn.Close()
            cmd.Dispose()
            cnn.Dispose()
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12310959
CREATE PROCEDURE [dbo].[jobsInsertJobNumber]
      @JobNumber INT OUTPUT,
      @CustomerID INT,
      @StartDate SMALLDATETIME,
      @CreatedBy VARCHAR (255),
      @EndDate SMALLDATETIME,
      @AssignedStaff VARCHAR(255),
      @Description VARCHAR(255)
AS

INSERT INTO JobNumbers
(CreatedBy, CustomerID, StartDate, EndDate, AssignedStaff, [Description])
VALUES
(@CreatedBy, @CustomerID, @StartDate, @EndDate, @AssignedStaff, @Description)

SET @JobNumber = @@IDENTITY
GO
0
 

Author Comment

by:dplsr
ID: 12310992


Hi praneetha,

Thanks for the reply!
I do not understand C# Can anybody get praneetha's code into vb?
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12310997
i think aerossage has allready done it in vb.net

0
 
LVL 15

Expert Comment

by:praneetha
ID: 12310999
sorry about that i am not a vb person...
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12311051
Your right I have;)
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12311056
thank you.
0
 

Author Comment

by:dplsr
ID: 12311162
Hi AerosSaga,

Thanks for replying!

I changed Select to Set in my stored procedure, but I am to much of a newbie to understand how to code the aspx page. all of my parameters, connection etc are on my .vb business object. What code do I use on the aspx page under


to get the value of :  (from my vb business object above)
Dim returnID As Integer = CInt(parameterReturnID.Value)
               Return returnId.ToString()
0
 
LVL 17

Accepted Solution

by:
AerosSaga earned 250 total points
ID: 12311197
Dim strJobNumber As String
        Dim ReturnValue As Integer
cmd.Parameters.Add("@JobNumber", SqlDbType.Int, 4)
            cmd.Parameters("@JobNumber").Direction = ParameterDirection.Output
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
            ReturnValue = CInt(cmd.Parameters("@JobNumber").Value)
            strJobNumber = CStr(ReturnValue)
0
 

Author Comment

by:dplsr
ID: 12311211
i left some code off sorry.

On my aspx page i need to get the returnId value under:

 Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim ReturnId as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  


i am experimenting with ReturnValue
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12311212
Thats what returns the value see my stored procedure for the paramaters
0
 

Author Comment

by:dplsr
ID: 12311418


Ok You returnvalue is the same as my returnID in

Dim returnID As Integer = CInt(parameterReturnID.Value)
               Return returnId.ToString()

in my businesobject

How do I code the aspc page to get the value of returnId

i now have
Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim ReturnId as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)
Dim strreturnID As Integer =cint(returnID)




0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12311454
You need to use executeNonQuery or  executescalar
 cmd.ExecuteNonQuery()
            ReturnValue = CInt(cmd.Parameters("@JobNumber").Value)
            strJobNumber = CStr(ReturnValue)
0
 

Author Comment

by:dplsr
ID: 12311790
ok
in my businessobject  (I get no errors when I compile)

i have:
myConnection.Open()
myCommand.ExecuteNonQuery()      
returnID = CInt(myCommand.Parameters("@returnID").Value)
strreturnID = CStr(returnID)                  
 myConnection.Close()

on the aspx page I declared
Protected strreturnID as string
under Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim ReturnId as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)

I have response.write(strreturnID)
and get nothing
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12311811
try running the stored procedure by hand in the server explorer of VS.NET and see if you get a return value, if not then the issues with your stored procedure or business objects.
0
 

Author Comment

by:dplsr
ID: 12311991
ok  my stored procedure is returning the value of the Primarykey column as it should, just got to get it onto the webpage
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dplsr
ID: 12312006
i don't use  visual studio, by the way. I used SQL server Query analyzer
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12312040
Then its an issue with your business object somewhere
0
 

Author Comment

by:dplsr
ID: 12312064
i will send the whole thing
0
 

Author Comment

by:dplsr
ID: 12312138
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace ASPNET.StarterKit.Commerce

 Public Class ReturnOrdersDB
 
  Public strOrderID as integer
  Public strEmail as string      
Public returnId as integer
Public strreturnID as string

      '**************************************************************************
                  
Public Function AddReturnInfoToCMRCRGA(strOrderID as integer, strEmail as string) As string

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand("spAddYOWRGARequestToCMRCRGA", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure
            Dim parameterstrOrderID As New SqlParameter("@strOrderID", SqlDbType.NVarChar, 50)
            parameterstrOrderID.Value = strOrderID
            myCommand.Parameters.Add(parameterstrOrderID)

            Dim parameterstrEmail As New SqlParameter("@strEmail", SqlDbType.NVarChar, 50)
            parameterstrEmail.Value = stremail
            myCommand.Parameters.Add(parameterstrEmail)

            Dim parameterReturnID As New SqlParameter("@ReturnID", SqlDbType.Int, 4)
            parameterReturnID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterReturnID)
                            myConnection.Open()
                myCommand.ExecuteNonQuery()
                 returnID = CInt(myCommand.Parameters("@returnID").Value)
            strreturnID = CStr(returnID)
                   myConnection.Close()
        End Function
      End Class

End Namespace

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12312200
and it doesn't produce any errors, it just returns null?
0
 

Author Comment

by:dplsr
ID: 12312270
correct, the stored procedure is returning 243 as it should but on the aspx page strreturnID returns nothing
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12312287
CREATE PROCEDURE [dbo].[jobsInsertJobNumber]
     @JobNumber INT OUTPUT,
     @CustomerID INT,
     @StartDate SMALLDATETIME,
     @CreatedBy VARCHAR (255),
     @EndDate SMALLDATETIME,
     @AssignedStaff VARCHAR(255),
     @Description VARCHAR(255)
AS

INSERT INTO JobNumbers
(CreatedBy, CustomerID, StartDate, EndDate, AssignedStaff, [Description])
VALUES
(@CreatedBy, @CustomerID, @StartDate, @EndDate, @AssignedStaff, @Description)

SET @JobNumber = @@IDENTITY
return @jobnumber //u have to have return in stored procedure

GO
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12312289
Your going to have to step through it and ensure the value of each paramater as you go down then.
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12312296
I don't and it works fine for me???
0
 

Author Comment

by:dplsr
ID: 12312327
here is the sub that calls it all:

   Sub SendYOWEmail(ByVal sender As Object, ByVal e As EventArgs)  
   orderinfo()
   Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim returnID as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
response.write (strReturnId)

                            Dim SB as New StringBuilder()
                        Dim SW as New StringWriter(SB)
                        Dim htmlTW as New HtmlTextWriter(SW)
                        YOWRGAREQUEST.RenderControl(htmlTW)
                            Dim dataGridHTML as String = SB.ToString()            
                              Dim objMM as New MailMessage()
                              objMM.To = "dlappin@maine.rr.com"
                              objMM.From = "PLF_Inc@EmailAddress.com"      
                              objMM.BodyFormat = MailFormat.Html
                              objMM.Subject = "Request for RGA on Order PO " & OrderID & strReturnId
                              objMM.Body = dataGridHTML
                              SmtpMail.SmtpServer = "smtp-server.maine.rr.com"
                              SmtpMail.Send(objMM)
            
   End Sub
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12312341
Dim returnID as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
response.write (strReturnId)// not strrerurnid it is just returnID
0
 

Author Comment

by:dplsr
ID: 12312402
thats what i started out with this norning praneetha. then we changed the business object to

     returnID = CInt(myCommand.Parameters("@returnID").Value)
            strreturnID = CStr(returnID)
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12312430
 Dim returnID as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
response.write (strReturnId)
yeah but returnID is in business object...

addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
it is returning to returnID....

not strReturnID


Dim returnID as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
response.write (returnID)

or

Dim strReturnId as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail)  
response.write (strReturnId)

copy paste either of that


0
 

Author Comment

by:dplsr
ID: 12312524
i think i got it!  I went back to the original code simply changed Select to set in the SP. it works
using

 myConnection.Close()
    Dim returnID As Integer = CInt(parameterreturnID.Value)

                Return returnID.ToString()

0
 

Author Comment

by:dplsr
ID: 12312541
Well, I used help from u both. Is it fare to split points?
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12313800
no objection here

Aeros
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12313805
although

 CREATE PROCEDURE [dbo].[jobsInsertJobNumber]
     @JobNumber INT OUTPUT,
     @CustomerID INT,
     @StartDate SMALLDATETIME,
     @CreatedBy VARCHAR (255),
     @EndDate SMALLDATETIME,
     @AssignedStaff VARCHAR(255),
     @Description VARCHAR(255)
AS

INSERT INTO JobNumbers
(CreatedBy, CustomerID, StartDate, EndDate, AssignedStaff, [Description])
VALUES
(@CreatedBy, @CustomerID, @StartDate, @EndDate, @AssignedStaff, @Description)

SET @JobNumber = @@IDENTITY
GO
ehmm....lol ;)
0
 
LVL 10

Expert Comment

by:Hans Langer
ID: 12314050
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now