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 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
Dear all,
I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @@IDENTITY.
The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),
Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection) Try NewTagID = CInt(command2.ExecuteScalar().ToString)
However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @@IDENTITY", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)
Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @@IDENTITY would be the better option.
Thankful in advance for advice.
Hi,
I need to pass the ID of a newly inserted product in my products db to a wizard step so that I can achieve the following:
Wizard Step 1: Enter product details
Wizard Step 2: Choose product image and upload to file system, then write to DB.
All help is appreciated!
Thanks,
Chris
Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.
It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...
I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.
Thank
Felix Pageau
fpageau@str.ca
You can test the functionnality with the following code:
create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @count as int
set @count = (select count(IDnumber) from identCurrentBugExeample)
--Print the number of record in the table
print @count
--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeample
I found a strange problem with IDENT_CURRENT returning NULL value in all my SP's in which it is used.Following are the general reasons why it returns NULL :1. Its executed against the Empty table.2. Table does not Identity column.But i could check that above two point were not a prolblem for me. The problem was the database user has only db_executor permissions. If it is set to db_owner all my insert SP's work's as it should. But it fails otherwise.Can anybody throw some lights here ?
View 1 Replies View RelatedI have Server1(MSSQL7) & Server2(MSSQL 2000) are linked. For trigger purpose I need to access latest identity value from Server2.mydatabase.mytable in Server1 so I have statement as follows
select @id = IDENT_CURRENT(‘server2.mydatabase.mytable’)
I get following error
'IDENT_CURRENT' is not a recognized function name.
Any idea why I am getting this error? Any help is appreciate
Hi thereI have small problem with IDENT_CURRENT...We have a table where PK is generated automatically.What I want is to get that value and insert it to another,corresponding table.So here is what I am doing...BEGIN TRANInsert Into table() --> PK is created.SELECT IDENT_CURRENT('Table_Name') AS lastValueCOMMIT TRanIT works fine, but there is a possibility to insert another record byanother user before I get my IDENT_CURRENT.For instance:BEGIN TRANInsert Into table() --> PK is created.---->somebody else inserts another record into same table.SELECT IDENT_CURRENT('Table_Name') AS lastValue---->this is not the value that I generated....COMMIT TRan
View 6 Replies View RelatedI have a table that uses an identity column. I have a second column that will contain the current identity value or, when the original row is replaced, the identity value of the replacing row.
USE [test]
GO
/****** Object: Table [dbo].[test1] Script Date: 04/10/2008 14:04:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test1](
[col1] [int] IDENTITY(1,1) NOT NULL,
[col2] [int] NOT NULL,
[col3] [varchar](max) NOT NULL,
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
My question is on the insert is it safe to use the following statement to insert the new identity value to the second column?
INSERT INTO test1
(col2, col3)
VALUES (IDENT_CURRENT('test1'), 'test')
BOL says (or has had added ) on the subject of IDENT_CURRENT
Exceptions
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.
However am finding that SCOPE_IDENTITY does continue to function for a user who has not been granted meta data permisisons or directly (select) on the underlying table.
can somebody explain why they are different behaviours. I need to be able to capture the ID of a row just inserted (under the insert rights of my application role) I'm glad SCOPE_IDENTITY works but am nervous am working in an area of undefined behaviour ! after all its also a metadata-emitting, built-in function
My application role is the owner of the schema and the storedprocedure which trys to use IDENT_CURRENT but it consistently returns me a null when logged in as a user without admin priveleges
any info much appreciated
Iain
Hello,
The following statement behave different between SQL2000 and SQL2005.
On our database we have some insert statements like that, and as we test our DB for SQL2005, we have found this difference. Can anyone tell me why?
create table #tmp (a int identity(1,1), b int)
insert into #tmp
values(ident_current('#tmp'))
select * from #tmp
insert into #tmp
values(ident_current('#tmp'))
select * from #tmp
drop table #tmp
SQL2000
a b
----------- -----------
1 1
a b
----------- -----------
1 1
2 1
SQL2005
a b
----------- -----------
1 1
a b
----------- -----------
1 1
2 2
I'm using visual basic and SQL
Here's my string:
SELECT intD1 = IDENT_CURRENT(ServerLogID)FROM ServerLog
I'm trying to select the primary key of the last record from a table and place it in the variable 'intD1'
I think I'm missing something, but I'm not quite sure what!
Hi,can somebody explain me, why the IDENT_CURRENT from an empty table is 1?After insert of the first record it is still 1 (assuming that start valueis 1) which is okay. But if i check the IDENT_CURRENT from a newly createdtable the result should be NULL, or not?bye,Helmut
View 10 Replies View RelatedHi
I am trying to get the last ID value of a table.
The following code worked when a SQLConnection object was used, how do I achieve the same result using an SQLDataSource?
this.sqlSelectCmd2 = new System.Data.SqlClient.SqlCommand("SELECT IDENT_CURRENT('Assets')",sqlConnection1);
sqlConnection1.Open();
int nID = Convert.ToInt32( this.sqlSelectCmd2.ExecuteScalar() ) ;
nID++;
dc.FocusedRow["ID"] = nID.ToString();
Cheers
Pen
Hi
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.
Code Snippet
DECLARE @ImportedStagingData TABLE
(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50),
[Chronic Heart Failure] INT,
[Colon Cancer] INT
)
INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0)
INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1)
INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0)
DECLARE @Conditions TABLE
(
ID INT IDENTITY(1,1),
Condition VARCHAR(50)
)
INSERT INTO @Conditions VALUES ('Chronic Heart Failure')
INSERT INTO @Conditions VALUES ('Colon Cancer')
DECLARE @Members TABLE
(
MemberID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50)
)
DECLARE @memConditions TABLE
(
MemberID INT,
ConditionID INT
)
SELECT * FROM @ImportedStagingData
SELECT * FROM @Conditions
SELECT * FROM @Members
SELECT * FROM @memConditions
/* --- This is the part that i am battling with ---
DECLARE @CurrentValue INT
DECLARE @numValues INT
SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData)
WHILE @numValues <> 0
BEGIN
INSERT INTO @Members
SELECT Name, surname, email
FROM @ImportedStagingData
GO
SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData'))
INSERT INTO @memConditions (MemberID), (ConditionID)
VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)
@numValues = @numValues - 1
END
END
*/
All help will be greatly appreciated.
Kind Regards
Carel Greaves
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
View 1 Replies View RelatedHi 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?
Thanks!
i have such a stored procedure.
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)
I was wondering if its possible to use this function if your using a
SqlDataReader where I just run a stored procedure that just inserts a
row?
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
THANKS
Hi All,
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.
can any one give me the syntax in Oracle ?
Here's what I have (OrderID and CustID are PK's and autoincrement):
tblOrders__________tblCustomers
---------__________------------
OrderID____________CustID
CustID_____________Name
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
conn.Open();
cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
conn.Close();
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?
View 5 Replies View RelatedI 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
View 4 Replies View Relatedis there an sql mobile equivalent of SCOPE_IDENTITY()?
View 4 Replies View Related
hi
what is difference between thos two's
SCOPE_IDENTITY()
and
@@IDENTITY
thanx
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
Hi folks;I'm having trouble retrieving the SCOPE_IDENTITY() with an SqlDataSource, it returns a number that has nothing to do with the real identity.It was always returning 102 or 137 when the real identities were something in between 5 to 10
Here is my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [POD] WHERE [PODID] = @PODID" InsertCommand="INSERT INTO [POD] ([CustomerID], [Airbill], [Shipper], [Consignee], [POD], [Date], [Time], [Pieces], [Weight], [Comments]) VALUES (@CustomerID, @Airbill, @Shipper, @Consignee, @POD, @Date, @Time, @Pieces, @Weight, @Comments); SELECT SCOPE_IDENTITY() AS @newID"
SelectCommand="SELECT * FROM [POD] ORDER BY [POD]" UpdateCommand="UPDATE [POD] SET [CustomerID] = @CustomerID, [Airbill] = @Airbill, [Shipper] = @Shipper, [Consignee] = @Consignee, [POD] = @POD, [Date] = @Date, [Time] = @Time, [Pieces] = @Pieces, [Weight] = @Weight, [Comments] = @Comments WHERE [PODID] = @PODID">
<DeleteParameters>
<asp:Parameter Name="PODID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" />
<asp:Parameter Name="Shipper" Type="String" />
<asp:Parameter Name="Consignee" Type="String" />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" />
<asp:Parameter Name="Weight" Type="Decimal" />
<asp:Parameter Name="Comments" Type="String" />
<asp:Parameter Name="PODID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="newID" Type="Int32" Direction="ReturnValue" />
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="Airbill" Type="String" DefaultValue=" " />
<asp:Parameter Name="Shipper" Type="String" DefaultValue=" " />
<asp:Parameter Name="Consignee" Type="String" DefaultValue=" " />
<asp:Parameter Name="POD" Type="String" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Time" Type="DateTime" />
<asp:Parameter Name="Pieces" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="Weight" Type="Decimal" DefaultValue="0" />
<asp:Parameter Name="Comments" Type="String" DefaultValue=" " />
</InsertParameters>
</asp:SqlDataSource>
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
' ** get the id and redirect to details page
Dim id As Integer = e.Command.Parameters("@newID").Value
Response.Redirect("pod_details.aspx?id=" & ID)
End Sub
Does anybody know what am i doing wrong in here?
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)