Avatar of Steve Krile
Steve KrileFlag for United States of America

asked on 

ObjectDataSource Return Value

Read some of the answers and can't seem to apply it to my situation.  I have an ObjectDataSource that calls a class and several of their Functions.  One of those functions is an Insert statement.  That statement is a Stored Proc with a return value.  So, what I'm looking to do is return the New ID from the table in SQL after the insert.

Here are the code bits.

<objectdatasource>
.
.
.
                    <InsertParameters>
                        <asp:Parameter Name="newID" Type="Int64" Direction="ReturnValue" />
                        <asp:CookieParameter Name="FacilityID" Type="string" CookieName="EW_FacilityID" />
                        <asp:Parameter Name="StartDate"  />
                        <asp:Parameter Name="FinishDate"  />
                        <asp:Parameter Name="Concept" type="String" />
                        <asp:Parameter Name="Comments" type="String" />
                        <asp:Parameter Name="Status" type="Int16" />
                        <asp:Parameter Name="ResponsibleID" type="Int64" />
                        <asp:Parameter Name="TotalCost" type="Decimal" />
                        <asp:Parameter Name="CurrencyID" type="String" />
                    </InsertParameters>
.
.
.
Insert Function referenced by the ObjectDataSource:InsertMethod
.
.
Public Function InsertLightningFix(ByVal StartDate As DateTime, ByVal FinishDate As DateTime, ByVal Concept As String, _
                                  ByVal Comments As String, ByVal Status As Int16, ByVal ResponsibleID As Int64, _
                                  ByVal TotalCost As Decimal, ByVal CurrencyID As String, _
                                  ByVal FacilityID As Int16, ByVal newID As Int64) As Int64
        Dim params(9) As SqlParameter
        params(0) = New SqlParameter("@StartDate", utils.setSQLDate(StartDate))
        params(1) = New SqlParameter("@FinishDate", utils.setSQLDate(FinishDate))
        params(2) = New SqlParameter("@Concept", Concept)
        If Comments Is Nothing Then
            params(3) = New SqlParameter("@Comments", DBNull.Value)
        Else
            params(3) = New SqlParameter("@Comments", Comments)
        End If
        params(4) = New SqlParameter("@Status", Status)
        params(5) = New SqlParameter("@ResponsibleID", ResponsibleID)
        params(6) = New SqlParameter("@TotalCost", TotalCost)
        params(7) = New SqlParameter("@CurrencyID", CurrencyID)
        params(8) = New SqlParameter("@FacilityID", FacilityID)
        params(9) = New SqlParameter("@newID", SqlDbType.Int)
        params(9).Direction = ParameterDirection.Output


        Dim strSQL As String = "usp_EW_INSERTLightning"
        Dim connStr As String = ConfigurationManager.ConnectionStrings("HSEConnectionString").ConnectionString

        Try
            SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, strSQL, params)
        Catch ex As Exception

        End Try

        Return params(9).Value



Finally, the SQL:



ALTER      PROCEDURE usp_EW_INSERTLightning
    @StartDate datetime, @Finishdate datetime, @Concept nvarchar(1000), @Comments nvarchar(1000),
    @Status int, @ResponsibleID int, @TotalCost decimal(9,2), @CurrencyID nvarchar(4), @FacilityID int

AS
      Set nocount on
      
      begin tran
      DECLARE @ProjectID int
    /*This saves the bits to the Project table*/
    INSERT INTO EW_Project
    WITH (ROWLOCK)
    (ProjectTypeID,FacilityID,StartDate,FinishDate,Status,EmployeeID)
    VALUES(4,@FacilityID,@StartDate,@FinishDate,@Status,@ResponsibleID)

    /*This saves the bits to the Lightening table*/
    SET @ProjectID = SCOPE_IDENTITY()
    INSERT INTO EW_Lightening
    WITH (ROWLOCK)
    (ProjectID,Concept,Comments,TotalCost,CurrencyID)
    VALUES (@ProjectID,@Concept,@Comments,@TotalCost,@CurrencyID)
   
      Commit tran
      
    set nocount off
   
    Return isnull(@ProjectID,0)



The SQL works.  I've tested it stand-alone, and I get a return value.  The ObjectDataSource Insert "works" in that a new record is created.  What doesn't work is acquiring the new ID.  Any thoughts would be greatly appreciated.
ASP.NETVisual Basic.NET

Avatar of undefined
Last Comment
Steve Krile
ASKER CERTIFIED SOLUTION
Avatar of strickdd
strickdd
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Steve Krile
Steve Krile
Flag of United States of America image

ASKER

You got it.  I was missing a declaration of @ProjectID int OUTPUT in the StoredProc.  Added that and changed my params(9) to ("@ProjectID", int).  Did the trick.

Oh, and I left the RETURN syntax.  Will try SELECT though.

Thanks!
ASP.NET
ASP.NET

The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications

128K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo