SQL/.NET - Multiple Transactions 1

[Terwin]

Hi all,

I usually don't ask such general questions, but I'm having trouble finding anything useful on the web - so here goes.

I have a user control that inserts a record into an MSSQL database. After inserting the record, I need to insert a second record (into a different table). The second record needs to contain the ID of the first record as one of its fields.

So my question is, what is the best way to find out the id of the first record when it is inserted? Is there a way to have the stored procedure for the first insertion return the id of the record it just inserted?

I'd rather not make a select statement using all of the parameters to return the id, because there may be duplicate records.

Thanks in advance,
T

 

[hneal98]

Could you read it into a recordset and then extract the info before or after wrting it to the table?

 

[Terwin]

Well, the issue is that the record id is an autonumbered field, so I don't have that information until the record is actually inserted.

I need to insert the record, then somehow get the auto-generated id of the record I just inserted returned to me.

Thanks!
T

 

[hneal98]

The the only way to do that would be to read the table after you have written to it and take the max autonumber from that field, and then write to the the next table using that number.

 

[jebenson]

You can get the autoincrement value of a record that has just been inserted from the stored procedure. In the stored proc, declare a parameter as Output. Here's an example of one of my stored procs:

CREATE PROCEDURE [dbo].[spInsertAuth] 
	@AuthYear int,
	@Amount money,
	@RemainingAmt money,
	@AuthDesc varchar(128),
	@Identity int OUTPUT
AS
INSERT INTO Authorizations (AuthYear, Amount, RemainingAmt, AuthDesc) 
VALUES (@AuthYear, @Amount, @RemainingAmt, @AuthDesc)
SET @Identity = SCOPE_IDENTITY()

GO

The @Identity parameter is the output. The recently generated autoincrement value is obtained with the "SET @Identity = SCOPE_IDENTITY()" line.

Now, in your VB code, add a parameter to read the output to your insert command. Another example from my code:

daAuth.InsertCommand() = New SqlCommand("spInsertAuth", Conn)

With daAuth.InsertCommand
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add("@AuthYear", SqlDbType.Int, 4, "AuthYear")
    .Parameters.Add("@Amount", SqlDbType.Money, 8, "Amount")
    .Parameters.Add("@RemainingAmt", SqlDbType.Money, 8, "RemainingAmt")
    .Parameters.Add("@AuthDesc", SqlDbType.VarChar, 128, "AuthDesc")
End With
[red]
Dim IDParam As SqlParameter = daAuth.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 4, "ID")
IDParam.Direction = ParameterDirection.Output[/red]

I've highlighted the important code in red. This is the code that creates the command parameter to read the output from the stored procedure. The returned value will actually be placed in the 'ID' field in the datatable in the dataset being used, and can subsequently be read with:

dsAuth.Tables("Authorizations").Rows(<row number>).Item("ID")

Note: <row number> would be the appropriate value to access the just-added row.

Post again if you have any questions.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira