What is the proper way to return the identity of a newly inserted row from a stored procedure? Using a return value or a select statement? (I guess as an output parameter should also be considered...) As in
RETURN SCOPE_IDENTITY()
or
SELECT SCOPE_IDENTITY()
What are the pros/cons of using one approach over the other?
Using scope_identity I am using SQL2005 and I need to insert a record and return ID. I am using scope_identity() in the stored procedure to return the ID for the record just inserted. Do you see any problem with this when it comes to multi-user and multi-threaded environment.
Hi, i need the DiagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement. I was told to use scope_identity(), but i'm not sure how to implement it. Below is my code behind in vb.net. pls help. Dim cmd1 As New SqlCommand("insert into Diagnosis(TypeID, SeverityID, UniBilateral, PatientID, StaffID) values ('" & typevalue & "','" & severityvalue & "','" & unibivalue & "','" & Session("PatientID") & "','" & Session("StaffID") & "')", conn) cmd1.ExecuteNonQuery() Dim i As Integer For i = 0 To hearingarray.Count - 1 Dim li As New ListItem li = hearingarray(i) Dim cmd As New SqlCommand("insert into DiagnosisManagement(ManagementID) values ('" & li.Value & "')", conn) //i need the DIagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement here cmd.ExecuteNonQuery() Next
Hi All, I'm trying to return the last id entered via the following code, and I'm only getting '0' back. 1 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString)) 2 { 3 connection.Open(); 4 using (SqlCommand command = new SqlCommand("REG_S_CustomerIDFromPhoneNumber", connection)) 5 { 6 command.CommandType = CommandType.StoredProcedure; 7 command.Parameters.AddWithValue("@Mobile_Telephone", MobileNumber); 8 9 int test = Convert.ToInt32(command.ExecuteScalar()); 10 11 Response.Write(test.ToString()); 12 13 14 } 15 } My SP is as follows (I'm trying to use one that's already been written for me) 1 SET QUOTED_IDENTIFIER ON 2 GO 3 SET ANSI_NULLS ON 4 GO 5 6 7 8 ALTER PROCEDURE [dbo].[REG_I_CreateBlankCustomer] 9 @Mobile_Telephone varchar(255), 10 @CustomerID int OUTPUT 11 12 AS 13 14 INSERT INTO Customer (Mobile_Telephone) 15 VALUES (@Mobile_Telephone) 16 17 --SET @CustomerID = @@Identity 18 SELECT SCOPE_IDENTITY(); 19 20 21 GO 22 SET QUOTED_IDENTIFIER OFF 23 GO 24 SET ANSI_NULLS ON 25 GO 26 27 28
when I'm running this via Query Analyser, I get the ID returned correctly, however as mentioned when ran via that code above - I get a 0 outputted to me. What am I doing wrong?
but i dont know where to put that scope_identity to retrieve a value. SELECT SCOPE_IDENTITY() AS [@Car_id] GO ALTER procedure [dbo].[insertuser]( @Make nchar(10), @Model nchar(10), @SellerID varchar(50), @MileAge nchar(10), @Year_Model int, @Price money, @Date_added datetime, @Thumb_ID varchar(50), @Image_id varchar(50), @Car_id int ) AS INSERT INTO dbo.tbcar VALUES(@Make,@Model,@SellerID,@MileAge,@Year_Model,@Price,@Date_added);
INSERT INTO dbo.tbimages values (@Thumb_ID,@Image_id,@Car_id)
Hello altogether, my problem ist that I get following error message: Create Stored ProcedureUnable to chances to the stored procedure.Error Details:'Scope_Identity' is not a recognized function name. This is my Stored Procedure: CREATE PROCEDURE sp_HyperSoftCustomer @Name varchar(25), @Adress varchar(250)as insert into HyperSoftCustomer(Name, Adress, Date) values (@Name, @Adress, GetDate()) Select SCOPE_IDENTITY() GO I am using MSDE - MSSQLServer I hope there is anybody who can help me? Thanks, mexx
I have seen plenty of messages about using scope_index by creating parameters using HTML but I would like to do it from my .aspx.vb page. Does anybody know if this is possible? I have got as far as the code below and get stuck when trying to add a new parameter with direction of output. Any help would be much appreciated, cheers, Doug. Dim NewProperty As SqlDataSource = New SqlDataSource NewProperty.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectIt").ToString() NewProperty.InsertCommand = "INSERT INTO Test (Name) VALUES (@Name); SET @NewID=SCOPE_IDENTITY()" NewProperty.InsertParameters.Add(NewID, id) NewProperty.Insert()
I have four tables: 1- customer details 2- bank details 3- car details 4- contract details
All these tables are linked with the contract ID which is the primary key in table 4 and foriegn key in the rest. When a new customer inputs all the above data from the VB front, I want table 4 to give contract ID with a autonumber, which should be sent to the other tables, so that the contract in all tables are the same so that it is linked properly.....
I think I do this using scope-Identity? if so hoe do I do this? I'm using enterprise manager.....
Another question, customer table has a customer ID. What would be the primary key- customer ID, contract ID or both
In SQL Server stored procedure SCOPE_IDENTITY() will return the IDENTITY value inserted in Table, which was the last INSERT that occurred in the same scope.
Just a simple application I created in ASP.NET and C# with those tables in an SQL database. The user enters their name, clicks Submit, and their information is put into Customers. After that, I want a new Order to be created with the CustID from the Customer just created.
I know I'm supposed to SCOPE_IDENTITY() to create it, but I'm not sure how to use it. I've been told to use a stored procedure, but I'm not sure how to do that either. Here's my code:
SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand("INSERT INTO tblCustomers(Name)VALUES('"+TextBox1.Text+"');", conn); //cmd2 with SCOPE_IDENTITY() inserting into tblOrders
I am seeing a problem with an ASP application, where I have 2 tables.In the first table, the ASP inserts just 1 row and retrieves theprimary key of the new row using SCOPE_IDENTITY. It then uses thatprimary key in the column of a second table (foreign key) to insertmany rows.What I'm seeing is an intermittent problem where the foreign key in thesecond table is not what it should be. I think the problem may be dueto the fact that the insert into the first table and the calling ofSCOPE_IDENTITY are done in 2 separate ASP statements with some ASP codein between.Is it possible that 2 users may be calling my ASP page at the same timeand causing a concurrency problem due to the INSERT and theSCOPE_IDENTITY being done in 2 different SQL statements? I read thatSCOPE_IDENTITY always returns the last identity value generated from"the current connection", so I thought that would mean that it wouldn'tget messed up by another ASP request. But now I'm thinking thatperhaps ASP uses connection pooling which could mean that 2 users couldbe sharing the same connection which would cause this concurrencyissue.Does anyone know if my theory of what's wrong is plausible?
I have an ASP front end on SQL 2000 database. I have a form that submits toan insert query. The entry field is an "identity" and the primary key. Ihave used scope_identity() to display the entry# of the record just enteredon the confirmation page. Now I need to insert the entry into anothertable. This is my query:SET NOCOUNT ONINSERT wo_main(site_id, customer, po_number)VALUES ('::site_id::', '::customer::', '::po_number::')SELECT scope_identity() AS entryINSERT INTO wo_combo_body(entry) VALUES ('::entry::')SET nocount offThis query displays the entry number of the record just entered, but insertsa 0 in to entry field of the 2nd table. Any help would be great.Thanks,Darren
have a detailsView control with an SqlDataSource whose insert statement looks like this: InsertCommand="INSERT INTO [tblCompaniesNewSetRaw] ([NAME], [CITY], [ST], [ZIPCODE], [NAICS], [NAICSDESCRIPTION]) VALUES (@NAME, @CITY, @ST, @ZIPCODE, @NAICS, @NAICSDESCRIPTION); SELECT RETURN_VALUE = SCOPE_IDENTITY()" also played with the same insert but used ...;Select SCOPE_IDENTITY() my question is how do i get the last record inserted into tblCompaniesNewSetRaw after the insert is run. ie I read that the Select Scope_identity() would return the value but how do i access the return value from within the code behind page, iusing VB. some things i tried in the detailsView_ItemInserted(... Dim row As DetailsViewRow For Each row In DetailsView3.Rows x = row.Cells.Item(0).Text Next in the VS debugger x is just "" and not the last record inserted in that table. probably way off base on this, clues appreciated, tc
I have a app that is inserting data into a SQL 2005 database and I would like to return the UniqueID of the inserted record. I am using Dim queryString As String = "INSERT INTO dbo.DATATABLE (FIELD) VALUES (@FIELD);SELECT Scope_Identity()" Dim sID As String = comSQL.ExecuteScalar() This isn't working - it says the value returned is DBNull... Any ideas on how to make this work?
I have a stored procedure that does three INSERTS each needing to use the primary key from the previous. There are three INSERTS in the procedure. Is this ok? my reason for asking is that it will get the first @IDPrimary but not the second @IDSecondary For example; INSERT (1) Set @IDPrimary = SCOPE_IDENTITY() INSERT(2) Set @IDSecondary = SCOPE_IDENTITY() INSERT(3)
Hi I am using Visual Web Developer Express 2008. The site is configured to ASP.Net 2.0. The database is a MS Access database and is accessed using a DAL. The Query - "InsertNewOrder()" is INSERT INTO Orders (order_date, order_type, order_pay) VALUES (NOW(), 'P', 'none'); SELECT SCOPE_IDENTITY()The Code-behide is : Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim NewOrder As New OrdersTableAdapter Dim CurrentOrderNumber As Integer = NewOrder.InsertNewOrder() End If End SubWhen debugging the following exemption occurs: "Characters found after end of SQL statement." Can anybody please help. If I take the ";" out then I get a "missing ; error" How do I fix this?ThanXRed
No matter how hard I look, I can't find a suitable answer to the many questions people tend to have about retriving an identity value after an SQL Insert command, particularly in C#. Everyone keeps going on about the advantages of Scope_Identity over @@IDENTITY but nobody seems to have actually explained properly how to use it ... hence my problems and frustrations.
I can use @@Identity fine since it returns a simple value (an int, I think). But the site I'm developing will apparently have heavy traffic (a similar site by the client uses 25Gb of bandwidth per month) so @@Identity is probably out of the question.
The problem I have is that Scope_Identity, along with the SqlCommand.ExecuteScalar() method returns an object, which is not what I want. I can't cast the object into an int.
I'm presently just using SqlConnection and SqlCommand classes to build and execute SQL queries (no data adapters in sight) so I need to know how to use the SCOPE_IDENTITY within that context.
Another question: I'm using the command builder to create the SQL commands. I've noticed some examples adding a SELECT @thisId = SCOPE_IDENTITY(). However, the command builder doesn't like this syntax (?)
this is my code, i really want a basic easy way without using procedures to do a simple task to response.write(last record added) using scope_identity...asp.net/VB===================================================================================Sub click_addnew(sender as object, e as system.eventargs) Dim oDR as System.Data.SQLClient.SQLDataReader Dim oCom As System.Data.SQLClient.SqlCommand Dim oConn as System.Data.SQLClient.SQLConnection Dim recnumber as integer try oConn = New System.Data.SQLClient.SQLConnection ("server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx") oConn.Open() oCom = New System.Data.SQLClient.SqlCommand() oCom.Connection = oConn oCom.CommandText = "INSERT INTO hooliganproducts (hooligantitle, hooliganprice, hooligandescription, hooliganpartno, hooligancata) VALUES ('test' , '100' , 'test' , 'test' , 'test') select scope_identity"oDR = oCom.ExecuteReader()response.Write(oDR) 'THIS IS WHERE I WANT TO DISPLAY THE NUMBER OF THE LAST RECORD catch Response.Write("Error:" & err.Description) Finally oDR = Nothing oCom = Nothing oConn.Close() oConn = Nothing end try End Sub=============================================================================Please can someone help, feel like i have been banging my head up a brick wall all day, have spent the whole day trying to find an answer.Thank you in advanceDarren
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
Hi,I am using following code to insert some record in to database and after that i want the id of new added record , so what kind of change i have to with my code plz anyone can do some change my code as it return id by using SCOPE_IDENTITY in my code-------------------------------------------------------------------Dim strconn As String = "server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx" 'Create a connection Dim MyConn_member As New SqlConnection(strconn) MyConn_member.Open() 'Start the transaction Dim myTrans As SqlTransaction = MyConn_member.BeginTransaction() Try 'Specify the first statement to run... Dim MySQL_member As String = "Insert Into article ([articleCategoryId],[articleTitle],[articleDescription],[articleContent],[articlePostBy],[articleStatus],[addDate],[lastUpdate]) Values (@category_id, @article_title, @article_description,@article_content,@article_postby,@article_status,@add_date, @last_update)" 'Create the SqlCommand object, specifying the transaction through Dim cmd_member As New SqlCommand(MySQL_member, MyConn_member, myTrans) cmd_member.Parameters.Add(New SqlParameter("@category_id", article_category.SelectedValue)) cmd_member.Parameters.Add(New SqlParameter("@article_title", article_title.Text)) cmd_member.Parameters.Add(New SqlParameter("@article_description", article_description.Text)) cmd_member.Parameters.Add(New SqlParameter("@article_content", article_content.Text)) cmd_member.Parameters.Add(New SqlParameter("@article_postby", post_by)) cmd_member.Parameters.Add(New SqlParameter("@article_status", article_status)) cmd_member.Parameters.Add(New SqlParameter("@add_date", last_update)) cmd_member.Parameters.Add(New SqlParameter("@last_update", last_update)) cmd_member.ExecuteNonQuery() myTrans.Commit() Catch ex As Exception 'Something went wrong, so rollback the transaction myTrans.Rollback() MyConn_member.Close() Throw 'Bubble up the exception Finally 'Finally, close the connection MyConn_member.Close() End Try-------------------------------------------------------------------PLease help meThanks in advance
I am currently writing an application that inserts data into a MS SQL Server 2000 DB. I am developing the application in Microsoft Visual Web Developer 2005 EE. So far everything has come together nicely. However, I have run into a situation that I can't seem to figure out.I wrote a stored procedure to insert data into the DB from a Web Form. The insert works nicely; however, one of the requirements I have is that I would like the DB to return the last row inserted into to my table. I am using the Scope_Identity() function at the end of my stored procedure to accomplish this task:...Last few lines of Stored Procedure...@ANNEXCITYCOUNCILACTION,@CRA)RETURN SCOPE_IDENTITY()GOI have conducted several tests on the SP, and received the following output from the DB when it is run:(1 row(s) affected)(0 row(s) returned)@GID = <NULL>@RETURN_VALUE = 116Finished running [dbo].[usp_InsertProject].I would like to take the result of the @RETURN_VALUE (116), and create a variable that I can use to embed into an e-mail. The code for the e-mail generation is working however, the value that comes through for the @RETURN_VALUE is always 0. I have tried several different things to get this to work, but so far no luck.Here is the application code I am using to create the e-mail: Sub SendEmail() Dim mySqlDataSource1 = SqlDataSource1.ConnectionString.ToString Dim myConnection As New Data.SqlClient.SqlConnection(mySqlDataSource1) Dim myCommand As New Data.SqlClient.SqlCommand("usp_InsertProject", myConnection) myCommand.CommandType = Data.CommandType.StoredProcedure Dim parameterGID As New Data.SqlClient.SqlParameter("@RETURN_VALUE", Data.SqlDbType.Int) parameterGID.Direction = Data.ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterGID) Dim reader As Data.SqlClient.SqlDataReader = myCommand.ExecuteReader() Dim GID As Integer = CInt(parameterGID.Value) GID.ToString() ...E-mail code is below this, but is working, so not included ... End SubI would like to insert the GID variable into the e-mail, but for some reason it won't work. The following error occurs when the InsertCommand is invoked:ExecuteReader requires an open and available Connection. The connection's current state is closed.
I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.
If I do the INSERT followed by a "SELECT @@IDENTITY" on the same connection, it works correctly. If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @@IDENTITY.
I guess what I have is satisfactory but this was frustrating and I want to know why.
This is on SQL Server 2000 Standard edition with version SP3a + hot fixes
With a query such as,SET NoCount ONINSERT INTO ....VALUES ...SELECT scope_identity()SET NoCount OFF, can I trust that the id I get back is for the row that I just inserted, orcan it be incorrect because of other "simultaneous" inserts? I need it foran ASP page.Thanks,Marcus
hi to all who is the best use among IDENT_CURRENT and @@IDENTITY and SCOPE_IDENTITY when i wnat to get last inserted id from a table. and also give the reason why because i am little bit confuse for useing these.. thanks in advance. arvind
I am following the tutorial from here : http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63Using Visual Studio Express Edition, it seems like so many SQL statements are not compatible with that tutorial so I have to figure the difference myself.For example for this : SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE CategoryID = @CategoryIDI have to use this : SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (CategoryID = ?)As you can see the difference is the ? character.Now I have problem with SELECT SCOPE_IDENTITY(), when I use it like tutorial: INSERT INTO [Products] ([SupplierID], [ProductName], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);SELECT SCOPE_IDENTITY() it gives me this error: Unable to parse query text. I really need to use SCOPE_IDENTITY() to return the inserted record ID ! Do you know what is wrong or what changes I have to make ?Thanks in advance.
Hi Folks,I've been Googling about for ages trying to get to the bottom of this, I've found plenty of stuff but nothing with the answer I'm looking for :(What I'm trying to do is use SqlDataSource programatically (i.e. not the <asp:SqlDataSource ... /> route) to call a stored procedure which inserts values into a table, the Sproc ends with RETURN SCOPE_IDENTITY() in order to return the autoincrement identity of the row that was just created. My specific problem is getting the returned value assigned to any sort of variable at the C# end! At the moment it seems to be returning 1 for some reason, it used to return -1 so I guess this is a step in the right direction... Here's my code: int importantReturnValue = 0; SqlDataSource sds = new SqlDataSource(); sds.ConnectionString = connString; sds.InsertCommandType = SqlDataSourceCommandType.StoredProcedure; sds.InsertCommand = "sp_insertValues"; sds.InsertParameters.Add("creator", creator); sds.InsertParameters.Add("title", title); // And so on.. Perhaps I need to add importantReturnValue to sds as some sort as parameter? - If so, how should I declare this at the Sproc end? ... importantReturnValue = sds.Insert(); Label1.Text = importantReturnValue.ToString(); Thanks,Ally
I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening. SqlConnection conn = new SqlConnection(@"Server=.SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open(); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
// delete all rows cmd.CommandText = "DELETE FROM Person"; cmd.ExecuteNonQuery();
// parameter insert cmd.CommandText = "SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@PersonID, @PersonName, @PersonAge)"; p = new SqlParameter("@PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonName", "Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p = new SqlParameter("@PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText = "SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();
Response.Write("<br>end.");
} finally { conn.Close(); } I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing... Thank you.
i am inserting new record in linked server and i need to get the id (which is of course autonumber) of newly added record. can't i get it using SCOPE_IDENTITY( ) ? SCOPE_IDENTITY( ) seems to be returning null. so SCOPE_IDENTITY( ) doesn't work in distributed transaction?
