GET SCOPE_IDENTITY ON INSERCOMMAND

Hello,

I need to get the scope_identity value after the insert, but I couldn't see how when using the InsertCommand of SqlDataSource. I think I can try using ADO commands, doing it 'mannually', but how?

If I execute the insertcommand and after select, I don't have the value, it only exists 'when inserting'. So, I need to keep that value. It's possible to do it with a session on sql or how?

I'm really newba.

Thanks

Search the forums, this has been discussed a half dozen times already.|||

Motley wrote:

Search the forums, this has been discussed a half dozen times already.

I saw, but I need something like that:

sqlComm.CommandText ="INSERT INTO S_CUSTOMER(C_NAME, C_RECDEL) VALUES ('" & name.Text &"',0); DECLARE @.C_Customer_Id VARCHAR(50); SET @.C_Customer_Id = SCOPE_IDENTITY;"

I need to get the scope_identity after the insert. If I execute a select command after, I don't get anything. I want to know if I can get the @.C_Customer_Id, declarated on the sql command.

|||

JPope

I had your problem and realized that the only way to get the value back was to do it the "old fashioned" way! I actually use @.@.Indentity which I know is more prone to problems than scope_identity but below is some code that I have used many times and found it works fine for me.

By the way, I am not the world's best programmer - so you may be able to refine some of this down to make it a bit more glamorous!

'Set up the objects and variable

Dim connAsNew SqlConnection()

Dim commAsNew SqlCommand()

Dim adpAsNew SqlDataAdapter()

Dim datasetAsNew DataSet

Dim recordidentityAs String

'Create our INSERT command - do NOT put the identity line in here -

comm.CommandText ="INSERT INTO [Table] ([Field1], [Field2], etc...) VALUES (Some values)"

'set the connection up and open it - use the connection name from web.config - in this case I have called it MyConnection, subsitute yours in its place

conn.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString

conn.Open()

'set the insert command up

adp.InsertCommand =New SqlCommand(comm.CommandText, conn)

'execute the insert command

adp.InsertCommand.ExecuteNonQuery()

'now that we have completed the insert command but NOT closed the connection we can set up the select command - in your case it will be SELECT scope_identity as indent

comm.CommandText ="SELECT @.@.Identity as Ident"

adp.SelectCommand =New SqlCommand(comm.CommandText, conn)

adp.Fill(dataset,"Ident")

'Create a datatable to get the data from the first table in the dataset - referred to as "0"

Dim IdentityTableAs DataTable = dataset.Tables(0)

'create a reader for use with the loading in of data

Dim readerAs DataTableReader = dataset.CreateDataReader

'load in data

IdentityTable.Load(reader)

'get a row that we can use - once again the first row referred to as "0"

Dim rowAs DataRow

row = IdentityTable.Rows.Item(0)

'get the inserted record id back as a string so we can use it in another command if required

RecordIdentity = row(0).ToString()

Hope this works - let me know!

Stuart