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.
ASKER
Oh, and I left the RETURN syntax. Will try SELECT though.
Thanks!