how to get scope_identity here?

  • Thread starter Thread starter Luc
  • Start date Start date
L

Luc

Hi,

i do an insert in the aspx file from a detailsview.
Now i need the 'id' of the record just inserted.
How can i do that?
I tried this, but the two sql statements are of course disconnected and it
doesn't work.
Thanks for help
Luc

code-behind
------------
Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles
DetailsView1.ItemInserted
Dim sql As String
Dim comd As SqlCommand
Dim id As Integer

Using mConnection As New SqlConnection(param.ConnectionString)
sql = "Select SCOPE_IDENTITY()"
comd = New SqlCommand(sql, mConnection)
mConnection.Open()
id = comd.ExecuteScalar
End Using
....

aspx file
 
Luc said:
Hi,

i do an insert in the aspx file from a detailsview.
Now i need the 'id' of the record just inserted.
How can i do that?
I tried this, but the two sql statements are of course disconnected and it
doesn't work.
Thanks for help
Luc

code-behind
------------
Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles
DetailsView1.ItemInserted
Dim sql As String
Dim comd As SqlCommand
Dim id As Integer

Using mConnection As New SqlConnection(param.ConnectionString)
sql = "Select SCOPE_IDENTITY()"
comd = New SqlCommand(sql, mConnection)
mConnection.Open()
id = comd.ExecuteScalar
End Using
...

aspx file
Click to expand...
etc ...
[/QUOTE]

Well, I don't think it's going to happen for you this way due to that fact
that you can only get the ID of the record inserted in a continuous in-line
execution of the code using one connection that contains the Insert and
Scope .

You need to use a Stored Proc to do this that returns the ID as output.



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4419 (20090912) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
that's what i thought, thanks

Mr. Arnold said:
etc ...
Click to expand...

Well, I don't think it's going to happen for you this way due to that fact
that you can only get the ID of the record inserted in a continuous
in-line execution of the code using one connection that contains the
Insert and Scope .

You need to use a Stored Proc to do this that returns the ID as output.



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4419 (20090912) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
[/QUOTE]
 
Hi,

i do an insert in the aspx file from a detailsview.
Now i need the 'id' of the record just inserted.
How can i do that?
I tried this, but the two sql statements are of course disconnected
and it doesn't work.
Thanks for help
Luc

code-behind
------------
Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal
e As
System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles
DetailsView1.ItemInserted
Dim sql As String
Dim comd As SqlCommand
Dim id As Integer

Using mConnection As New SqlConnection(param.ConnectionString)
sql = "Select SCOPE_IDENTITY()"
comd = New SqlCommand(sql, mConnection)
mConnection.Open()
id = comd.ExecuteScalar
End Using
...

aspx file
-------
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:conn %>"
InsertCommand="INSERT INTO [mytable] ([field1],...) VALUES
(@field1,...)" > etc ...
Click to expand...


You can try @@IDENTITY as the second command, but you could,
concievably, get the wrong answer that way. Best option is a stored
procedure to pull the SCOPE_IDENTITY().

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top