So Scope_Identity() returns an ?-Byte Numeric Type in SQL Server. That is not awesome. Is there a safe way to cast it to an int in a select query so we don't have to manage every whim of SQL Server in our ODBC Wrapper? ...
Dear all, I'm unable to retrieve the latest inserted id from my SQL Server 2000 db using a typed dataset in asp.NET I have created a tableadapter and I ticked the "Refresh datatable" and "Generate Insert, Update and Delete statements". This auto-generates the Fill and GetData methods, and the Insert, Update, Select and Delete statement...
Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs as our primary keys. Any idea on what the best way to retrieve the last inserted GUID primary key. Thanks in advance! ...
Hi guys. Started getting this error it seems since we upgraded to SQL Server 2008. When inserting into the db and then returning the identity i get a 'Item cannot be found in the collection corresponding to the requested name or ordinal' error. Here is the code: SQL = "INSERT INTO PageFeatures(nPageFeatureFlagId,nPageFeatureFeatureId...
How do I get the last id created in the policy table and store it into a variable so that I can use it for another table called backupspec table. System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection(); dataConnection.ConnectionString = @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=Sumoo...
This is a follow up to my previous question, in t-sql SELECT SCOPE_IDENTITY() returns a BIGINT, I did the following to get it to return an INT: DECLARE @X INT INSERT ... SELECT @X = SCOPE_IDENTITY() -- if i don't include the line below, it will return a BIGINT SELECT @X Why does it return a BIGINT unless I do SELECT @X at the...
My SQL Server table has, between other, these columns. AutoID which has IdentitySpecification set to True and GuidKey which has the default value of (newid()) AutoID GuidKey 1 f4rc-erdd 2 gedd-rrds Is there any way of getting the GuidKey from the row inserted? I need something like the Scope_Identity(), with the di...
Hi I have a form that allows users to insert items in the database. I call Scope_Identity() to get last identity value inserted. This works ok. When inserting the item a passowrd is randomly generated which is also saved in my database. I need to retrieve this password as well, immediately after the item is inserted. How do I do this? ...
Hello everyone, I have a stored procedure that does, among other stuff, some inserts in different table inside a loop. See the example below for clearer understanding: INSERT INTO T1 VALUES ('something') SET @MyID = Scope_Identity() ... some stuff go here INSERT INTO T2 VALUES (@MyID, 'something else') ... The rest of the procedure...
So I have a table with an identity column as the primary key, so it is an integer. So, why does SCOPE_IDENTITY() always return a decimal value instead of an int to my C# application? This is really annoying since decimal values will not implicitly convert to integers in C#, which means I now have to rewrite a bunch of stuff and have a lo...
I'll give a pseudocode example of my current method and if anyone knows of a method that doesn't work one row at a time, I'd be quite appreciative. I'm using MS SQLServer 2008 define cursor for the data to be inserted (about 3 million records) loop ( insert record into table 1 use scope_identity() to get key insert record into tab...
Hey All, How can i get the scope identity parameter in my vb code. I have this so far.... InsertCommand="INSERT INTO [table_name] ([title], [subtitle], [description], [image1], [image1_caption], [image2], [pdf], [meta_description], [meta_keywords]) VALUES (@title, @subtitle, @description, @image1, @image1_caption, @image2, @pdf, @m...
In SQL Server, ID is a not null integer, and an identity. When I run the following code, I get an InvalidCastException on the last line: SqlCommand cmd = new SqlCommand(); cmd.Connection = _conn; cmd.CommandText = @"INSERT INTO [Users] (Name, Email, Password) VALUES (@name, @email, @pass); SELECT SCOPE_IDENTITY()"; cmd.Parameters.Add...
The way I want my stored procedure to work is this, the user passes all of the customer data, name, phone, and all of address info into the procedure, then I want to perform an insert into the address table, get the id generated from that insert, use that id in another insert for the customer table then return the customer id to the user...