Best way to retrieve the identity key of the record just inserted?This question is for discussion purposes; the business process that spurred the question is currently working.Using SQL Server 2008 R2, a record is inserted from a stored procedure. Let's say the sp has something like this:
Code:
BEGIN
BEGIN TRANSACTION
INSERT INTO tblTools
([Desc],CreateDate,Model,CreatedBy,Notes)
[code]....
In Access, when you add a new record to the recordset, the identity field comes "pre-populated" making it east to get the actual, correct identity value assigned to the record you are inserting. In SQL Server, I know options include:
Code:
IDENT_CURRENT('tblX')
SCOPE_IDENTITY
@@IDENTITY
among other methods.
Each has pros and cons, such as user privileges (IDENT_CURRENT requires the user to have Select privileges on the table, and catches records created by other things, such as users and triggers), and the other two give you the last key inserted and don't allow specifying the object (which is a problem if the insert added records to multiple tables, or you have multiple inserts).
Ok I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated.
Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString()
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
Hi I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation. On the event handler I am usin select ID,Name from my [table]; this will return all record from database. I just want to get the record which is inserted or updated. Can u help me in that. Take care Bye
I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user.
I am using VS.net (2003) SQLcommand TEXT. with input params. SQL server 2000
Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.
My code so far that works... but Where do I put @@IDENTITY ? How do I call or assign the @@IDENTITY value to a value in my aspx.vb code page?
Question: how do I get the Identity value from the ID column. Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help... --------------------- INSERT INTO App (AppName, Acronym, Description,bla bla bla bla........) VALUES (@AppName, @Acronym, @Description, bla bla bla bla........)
-----------------------------
Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click With cmdAddApp '.Parameters("@app_id").Value = Session("App_ID") .Parameters("@AppName").Value = txtAppName.Text .Parameters( bla bla bla............. .Parameters( bla bla bla............. .Parameters( bla bla bla.............
End With Try cnAppKBdata.Open() cmdAddApp.ExecuteNonQuery() cnAppKBdata.Close() ''''''''''''''Session("App_ID") = whatever the @@IDENTITY is....'''''''''''''''''''''''''''?? Response.Redirect("AppUpdate.asp") Catch ex As Exception
Hi, I have 2 tables in my database PrescriptionHeader and PrescriptionDetails.My PrescriptionHeader table has the following fields:PrescriptionID -identity fieldPatientID PatientfNamePatientlname PrescriptionDetails table has the following fields:PrescriptionDetailID -identityPrescriptionID -from PrescriptionHeader table MedicineDosage The function InsertPrescription inserts values into the table PrescriptionHeader. I want the same function to then insert the value of MedicineDosage into PrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?ThanksFunction InsertPrescription(ByVal PatientID As String, _ByVal PatientFname As String, _ByVal Patientlname As String, ByVal MedicineDosage as String)Dim DBAdapter As SqlDataAdapterDim DBDataSet As DataSetDim SQLString As StringDim DBCommandBuilder As SqlCommandBuilderSQLString = "SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"DBAdapter = New SqlDataAdapter(SQLString, DBConnection)DBDataSet = New DataSetDBAdapter.Fill(DBDataSet)Dim AddedRow As DataRow = DBDataSet.Tables(0).NewRow()AddedRow("PatientID") = PatientIDAddedRow("PatientfName") = PatientFnameAddedRow("Patientlname") = PatientlnameDBDataSet.Tables(0).Rows.Add(AddedRow)DBCommandBuilder = New SqlCommandBuilder(DBAdapter)DBAdapter.Update(DBDataSet) End Function
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = @@IDENTITY SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
Using Visual Studio 2005 Prof and SQL Server everywhere.
How do get the identity column value after insert record.
With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity())
Dear all, i am using asp.net ,C# (VS 2005) and sql server 2005. i have written sp for inserting the the data which written last inserted idendity no. i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form. As executenonquery return only affected rows.
I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:
CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals] AFTER INSERT AS Declare @Portal int set @Portal = (select PortalId FROM inserted)
Declare @TabId Int set @Tabid = (select TabID from Tabs where Tabs.PortalID = @Portal and Tabs.TabName = 'MyTab')
Declare @ModuleId int set @ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @TabId and Modules.ModuleTitle = 'MyModule')
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent). At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results. Hints? Mahalos in advance ... :) KevInKauai
i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked.
Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value...
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation. I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work. How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#. As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
Hello,I would like do an insert into a table. The table has an autoincrimenting unique int id. After I do the insert how do i get theunique int id of the record that I just inserted? Is there a straightforward way of accomplishing this?Thanks,Billy
SET @new_identity = @@IDENTITY SET @new_link = @item_link + @new_identity
UPDATE items SET item_link = @new_link WHERE item_id = @new_identity
END GO
Basically, what I am trying to do is this...
IF the item type is article, insert normally... which works fine...
however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id>
however, when the sql runs the mediaItem code, it leaves the item_link field blank.
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
I have a table named invoice that contains the following columns-invoiceno - Primary key and is set to autonumber-customerno-incoicedateand on my VB code i did the following InsertCommandSqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "SqlDataSource1.Insert()My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me. Here is my VB Code Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company) Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End Function
Is it possible to retrieve the user name or host name of the user that inserted or updated a particular record? Is this information stored in the database's log file, in hidden fields, or anywhere else? I know I can easily add a user_name field and use triggers to add this information for new records, but I need this information for records already existing in the table. Thanks.
Hi All, i have a database, and every time when any row enter , it capture the date and time of insertation, then does any function or query available, which help to investigate me, today inserted record,yesterday inserted record,last 7 days inserted record,last 30 days inserted record and last year inserted record.. i am using asp.net and MS Access... Any idea thanx in advance... sajjad
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?
also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."
There are loads of postings on the net about this problem but none I have found explain the cause. Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method. Has anyone come across the solution for this issue? Thanks
Hiwhen inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 Dim sqlStr As String 3 Dim sqlStr2 As String 4 Dim myConnection As MySqlConnection = Nothing 5 Dim myCommand As MySqlCommand = Nothing 6 Dim myConnection2 As MySqlConnection = Nothing 7 Dim myCommand2 As MySqlCommand = Nothing 8 Dim myReader As MySqlDataReader = Nothing 9 Dim IC As String 10 11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text 12 13 14 Try 15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'" 16 17 ' Connection 18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 19 myConnection2.Open() 20 ' Command 21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2) 22 ' Reader 23 myReader = myCommand2.ExecuteReader() 24 Catch ex As Exception 25 ' Exception Error Here 26 Response.Write(Err.Number & " - " & Err.Description) 27 28 End Try 29 ' Checking 30 If myReader.Read() Then 31 32 33 Label2.Text = "Username already exist. Please choose another username" 34 Label3.Text = "*" 35 36 Else 37 38 Try 39 40 41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )" 42 43 44 45 ' Connection 46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 47 myConnection.Open() 48 'Command 49 myCommand = New MySqlCommand(sqlStr, myConnection) 50 51 myCommand.Parameters.AddWithValue("?uName", txtName.Text) 52 myCommand.Parameters.AddWithValue("?uIC", IC) 53 54 55 myCommand.ExecuteNonQuery() 56 myConnection.Close() 57 Response.Redirect("Register.aspx", False) 58 59 Catch ex As Exception 60 ' Exception Error Here 61 Response.Write(Err.Number & " - " & Err.Description) 62 Finally 63 ' Clean Up 64 If Not IsNothing(myCommand) Then 65 myCommand.Dispose() 66 End If 67 ' 68 If Not IsNothing(myConnection) Then 69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close() 70 myConnection.Dispose() 71 End If 72 End Try 73 74 75 End If 76 77 End Sub 78 79
above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur. when it run,it run 55, then 57,then back to 55, then 57 again means that my db hav duplicate record being insert anyone know how to solve this problem?
I have a problem described as follows: I have a table with one instead of insert trigger:
create table TMessage (ID int identity(1,1), dscp varchar(50)) GO Alter trigger tr_tmessage on tmessage instead of insert as --Set NoCount On insert into tmessage
[code]....
When I execute P1 it returns 0 for Id field of @T1.
How can I get the Identity in this case?
PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.
I am using ASP.NET 3.5 with SQLDataSource, when a record is inserted is there a way to retreive the newly created GUID value in a field? I am currently using SELECT @NewID = SCOPE_IDENTITY() to retreive the newly created record unique ID field, but I need the secondary field GUID field's value instead in this case. Thank you.
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String
'Get the users IP address strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con
