Yehuda,

Just checking as it's not completely clear -- GetNewRowID is a separate call to the database AFTER the insert?  One issue to consider is the possibility of someone adding a record at the exact time you are ... the second call to get the GetNewRowID may possibly get the other row.   Depends on your site ... but possible.

In such a case I return the new row id as the status of the Create. 

Here is the stored procedure .... return 0 or the row id ..

CREATE PROCEDURE CreateCarrierContacts
 @CustomerCode as varchar(5),
    @CarrierCode as varchar(10),
    @Name as varchar(50),
    @Role as varchar(100),
    @Email as varchar(100),
    @WebSite as varchar(100),
    @Contact1 as varchar(250),
    @Contact2 as varchar(250),
    @CreatedDT as datetime
AS

Set NoCount On
Declare @Rtn as Int

Insert FZ_CarrierContacts with (ROWLOCK) (
    [CustomerCode], [CarrierCode], [Name], [Role], [Email], [WebSite], 
    [Contact1], [Contact2], [CreatedDT] )
Values (
    @CustomerCode, @CarrierCode, @Name, @Role, @Email, @WebSite,
    @Contact1, @Contact2, @CreatedDT )

Set @Rtn = 0
If @@RowCount = 1   -- update successful
  Begin
    Set @Rtn = cast(SCOPE_IDENTITY() as int)
  End
Return @Rtn

So the Function then becomes the following...  it return an Integer value of the New row ID

 Public Overrides Function CreateCustomers(ByVal psCustomerCode As String, ByVal psName As String,
     ByVal psAddress1 As String, ByVal psAddress2 As String, 
  ByVal psCityCode As String, ByVal psPostalCode As String) As Integer

  Dim sProc As String = DatabaseOwner & ObjectQualifier & "CreateCustomers"
  Return CType(SqlHelper.ExecuteScalar(ConnectionString, sProc, psCustomerCode, psName, 
               psAddress1, psAddress2, psCityCode, psPostalCode, System.DateTime.Now),
               Integer)
End Function

Here is how I use the example.

    oInfo.ItemID = SavedItemID
    oInfo.CustomerCode = txtCustomerCode.Text
    oInfo.Name = txtName.Text
    oInfo.Address1 = txtAddress1.Text
    oInfo.Address2 = txtAddress2.Text    
    oInfo.CityCode = ctlCityCode.SelectedCityCode
         oInfo.PostalCode = txtPostalCode.Text
    oInfo.ModifiedDT = txtModifiedDT.Text
    oInfo.CreatedDT = txtCreatedDT.Text

    If SavedItemID < 0 Then
        SavedItemID = oController.Create(oInfo)
        oInfo = oController.ReadByItemID(SavedItemID)
    Else
        oController.Update(oInfo)
    End If

Notice that I user the Returned value to read the newly inserted record. 

Hope this helps
Paul.

Paul,

Thanks. Sorry for the delayed response, the forum's notification does not notify somehow.

The motivation for my initial question was the fact that I must get the current user's newly inserted rowID of course.

   Public Function ATR_PSCH_Questions_Add_GetNewRowID is the actual insert function. I do not fully understand the theory behind it but after I used the code Yen sent and played with it it worked (my original sub was replaced with the new function I posted)

If i get it right, the main part of your code is:

Set @Rtn = 0
If @@RowCount = 1   -- update successful
  Begin
    Set @Rtn = cast(SCOPE_IDENTITY() as int)
  End
Return @Rtn

where you reset the rows count and then you check the counter to see if it counts only 1

Is that correct?

Also, I thought that the sql code

SELECT cast(SCOPE_IDENTITY() as int) AS newQID
 

returns the current insert new id, isn't that correct?

Yehuda