Solved

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

Posted on 2004-10-14
35
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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