473,320 Members | 1,820 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

TableAdapter Insert and SCOPE_IDENTITY

The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincremented ID.

Question. How do I get hold of this value ?
Jun 8 '06 #1
3 9825
I believe it populates the table with the ID after the insert :)

Cheers,

Greg
"Ian Semmel" <is***********@NOKUNKrocketcomp.com.au> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincremented ID.

Question. How do I get hold of this value ?

Jun 9 '06 #2
Problem is how to know which record in the table is the new one, as the only key
is the ID.

I looked around and found that there were some sql procedures that did it but
with my knowledge of sql somewhat limited I didn't fully understand them.

What I did was put a value in a field prior to the INSERT and then read the
table back in and found the record I needed.

Greg Young wrote:
I believe it populates the table with the ID after the insert :)

Cheers,

Greg
"Ian Semmel" <is***********@NOKUNKrocketcomp.com.au> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincremented ID.

Question. How do I get hold of this value ?


Jun 9 '06 #3


Ian Semmel wrote:
The generated INSERT statement in a typed dataset has an INSERT
statement followed by a SELECT statement which gets (I think) the value
of the autoincremented ID.

Question. How do I get hold of this value ?


In case anyone is interested, I finally got this to work

// Copy this code from Dataset.Designer
string sql = "INSERT INTO ...... "
// BUT replace the SELECT statement in Designer with
+ "SELECT CAST(scope_identity() AS int)";

using ( SqlConnection connection = new SqlConnection ( connectionString ) )
{
SqlCommand command = new SqlCommand ( sql, connection );

command.CommandType = System.Data.CommandType.Text;

// This stuff is in Dataset.Designer (sort of)
// Copy it changing the command identifier

command.Parameters.Add ( new System.Data.SqlClient.SqlParameter (
"@parmName", System.Data.SqlDbType.Int, 0,
System.Data.ParameterDirection.Input, 0, 0, "ColumnName",
System.Data.DataRowVersion.Current, false, null, "", "", "" ) );
.....

command.Parameters [ 0 ].Value = ( (int) ( parmValue ) );
.....

connection.Open ();

int ID = (int) command.ExecuteScalar ();
}
Jun 13 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Hardy Wang | last post by:
Hi all, I have the following codes, but SCOPE_IDENTITY() just returns NULL to me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works fine!! Since I have a trigger on the...
0
by: jhansen | last post by:
I have a datagridview where a user enters a row of data. Upon hitting the ENTER key, I capture that event and add additional rows based upon business logic, and delete their original row. As this...
1
by: gmccallum | last post by:
I would have expected to be able to find this easily, but I couldn't. I am using the TableAdapter.Insert command to insert a record. I would like to then get an ID field from that record after...
2
by: needin4mation | last post by:
Hi, I have generated my queries and selected the refresh the table option. This creates a select after the insert to get the scope_identity() so that I can use that key value in another table,...
8
by: Martin Z | last post by:
INSERT INTO dbo.Transmission (TransmissionDate, TransmissionDirection, Filename, TransmittedData) VALUES (@TransmissionDate,@TransmissionDirection,@Filename,@TransmittedData); SELECT @retVal =...
1
by: =?Utf-8?B?RGV2YW4=?= | last post by:
Hi, I have a table adapter for a database with an insert query. I want to be able to get the PRIMARY key of the inserted record for that insert statement. I know that SCOPE_IDENTITY is the...
3
by: Randy | last post by:
I think that this is a really simple question. I have a table adapter set up with the various sql queries. I'd like to allow the user to make changes to a given row on a form. Each of the...
0
by: Yin99 | last post by:
I'm using TableAdapter with .NET 2.0 and SQL 2005. In SQL Database, a column's type is "float". Default TableAdapter methods update, insert, get, etc. maps this automatically to c# type...
3
by: Miro | last post by:
I am trying to get my ID added from a people table in an access table "MyContacts.mdb" i just added. Its basically a contact file i created in access: PK Person_ID - Unique - autoincrement...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.