[Solved] Return a Value after an insert

[DougieC]

Thanks for looking,

I need to get a new Id from a table that just added a new record. How can I go about getting the returned value back, after this insert is done I need to use that ID for other statments.

[vb_dba]

Are you using SQL to insert the new record? If so, are you using SQL Server, Access, Oracle, etc as your database engine?

If you are using the Recordset.AddNew/Recordset.Update method to add a new record, and the ID field is an autokey (Access) or Identity(SQL Server) column, then you can reference the column name after the .Update statement is issued for the new ID.

With oRS
    .AddNew
 
    'Assume you table has 3 columns: usr_id (autokey), fname (text), lname(text)
    .Field("fname").Value = "blah"
    .Field("lname").Value = "blah-blah"
 
    .Update
 
    Msgbox "ID is " & .Field("usr_id").Value

[DougieC]

Sorry, I am using vb.net and sql server and I have no idea what Recordset.AddNew/Recordset.Update method is . I am doing the insert as such

query = "INSERT INTO NOTE(Modified_Date, Regarding,
 Entered_By_ID)" & _
" VALUES(@Modified_Date, @Regarding, 
@Entered_By_ID)"

if that helps any

[brucevde]

Then execute the following after the Insert. Return its value into a datareader or try the ExecuteScalar method.

Select @@Identity or Select @@Scope_Identity

or use stored procedures and Output parameters.

[DougieC]

Could you show me how to use the Select @@Identity or Select @@Scope_Identity in vb.net I seem to be getting an erorr

[vb_dba]

Not sure about vb.Net, but in SQL Server, you can create a stored procedure with an Output parameter that you can utilize to return the id:

Create Procedure db_abc @Name varchar(10)
                    , @ReturnParm int Output

As

--If the id field is an identity
Insert Into myTable(emp_name)
Values (@Name)

Select @ReturnParm = @@Identity

--if id field is a non-identity numeric field
/*
Select @ReturnParm = Max(id_fld) + 1 From myTable
If @ReturnParm Is NULL Set @ReturnParm = 1

Insert Into myTable (id_fld, emp_name)
Values (@ReturnParm, @Name)
*/

In VB6, I'd create an ADO Command object and create an output parameter. Then I'd reference this output parameter after I executed the stored procedure:

Dim oCmd  As ADODB.Command
 
Set oCmd = New ADODB.Command
With oCmd
     .ActiveConnection = oCN    'oCN is my connection Object
    .CommandType = adCmdStoredProc
    .CommandText = "db_abc"
    .Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 10, sName)
    .Parameters.Append .CreateParameter("@ReturnParam", adInteger, [b]adParamOutput[/b], 4)
 
    .Execute
    MsgBox .Parameters("@ReturnParam").Value
End With

Not sure how you would translate the VB6 part to .Net.

[DougieC]

Thanks vb_dba and brucevde that totally helps me out, Thanks for the help

[jhermiz]

Originally posted by DougieC
Thanks vb_dba and brucevde that totally helps me out, Thanks for the help

You should not rely on @@IDENTITY since it can return bogus data. Use the other suggestion and take @@SCOPE_IDENTITY

Jon