OK, here's what I'm doing. I have a SQL Server 2005 table with an auto-number Identity column that I will be inserting to. I used the DataSet designer to add a table adapter to my DataSet to which I want to add an insert command.
Now, I want to know if there is some way to retrieve the number that was inserted into the Identity field. I know that if I were writing the update command manually I could set a parameter to SCOPE_IDENTITY( ), but is there anyway to do this in the TableAdapter's insert query that I will be adding?
Thanks.
6 5574
I personally have avoided this situation precisely because it is so unintuitive, but the situation is obviously legitimate..
Just to help get you started...this is outdated (2003), but the keywords should lead you in the right direction: Bill Vaughn - IdentityValues
Here is the MSDN blurb. Again, not intutive. Seems the assumption is your insert will be done via a stored procedure. Is that an option for you? That is, can your Adapter's Insert query call a stored procedure? Retrieving Identity Or AutoNumber Values
Thanks for the repy.
To answer your question, yes, an SP is an option. Actually, it was the plan.
I'll take a look at both of these tomorrow and give them a shot.
I'll be sure to post the results.
Thanks!
Plater 7,872
Recognized Expert Expert
Wouldn't the number of rows change? Could you just look at the last row in the index?
Thanks a lot, mldisibio.
Turns out that the output params were a real hassle when you are using the dataset to do the hard work for you. I figured out that I can make an SP with the insert statement followed by a select statement, and get exactly the result I want. - CREATE PROCEDURE name
-
--paramlist here
-
AS
-
BEGIN
-
--insert statement here
-
SELECT SCOPE_IDENTITY() AS IdOfNewRow
-
END
And then you add a new query to your table adapter, specifying to use an existing SP. VS will poll the DB and find your SP, find it's input parameters, and it's output values, and let you specify it as a single return value or a rowset return value. I chose single value.
It works perfectly.
Again, thanks for your help.
Plater 7,872
Recognized Expert Expert
Hehe, when you said you couldn't use scope_indentity () above, I just wrote that off.
I use just:
return SCOPE_IDENTITY( )
But I guess if you use SELECT it would come out like a "table"
Hehe, when you said you couldn't use scope_indentity () above, I just wrote that off.
I use just:
return SCOPE_IDENTITY( )
But I guess if you use SELECT it would come out like a "table"
Well, if I would have thought of RETURN instead of SELECT I would have used that. Oh well. all's well that ends well.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: (Pete Cresswell) |
last post by:
This doesn't work because the first INSERT is creating multiple
records for multiple projects. @@IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.
Consequently, all the hours records are then associated with
that last value.
The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets...
|
by: Jason L James |
last post by:
Hi all,
I recently wrote a vb.net app using oledb to
an access database. When I inserted new
rows in my datatable the identity column
was automatically created. This app used
an un-typed dataset.
My current app is using sqlClient and a typed
dataset that I created by exporting an xsd
|
by: pb648174 |
last post by:
In a multi-user environment, I would like to get a list of Ids
generated, similar to:
declare @LastId int
select @LastId = Max(Id) From TableMania
INSERT INTO TableMania (ColumnA, ColumnB)
SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
--get entries just added
|
by: Rico |
last post by:
Hello,
I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I can retrieve the autonum value for the
new record. This doesn't occur with SQL Server, which of course causes an
error (or at least in this code it does since there's an unhandled NULL
value). Is there any way to retrieve this value when I add a...
|
by: Brad Eck |
last post by:
In Access, newID returns a unique for the table. In SQL Server, newid()
returns a GUID - unique in the world. I do not need or desire that
complexity. Is there a way to get a simple unique int on the table in
SQL Server?
Brad Eck
http://www.sitesdynamic.com
http://www.basketsetcetera.com
*** Sent via Developersdex http://www.developersdex.com ***
| |
by: Susanne Klemm |
last post by:
Hello!
I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.
My Procedure:
|
by: Veeru71 |
last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?
I can't use identity_val_local() as the INSERTS are happening in a
different session.
Eg, We have the following table....
|
by: gunnar.sigurjonsson |
last post by:
I´m having some problem retrieving identity value from my newly
inserted row into a view.
I have two tables T1 and T2 which I define as following
CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
|
by: sajithamol |
last post by:
I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |