|
| Retrieving autoincrement field after insert |
|
|
|
|
| Messages |
|
Related Types |
This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
| Jung Steutel |
I would like some help with inserting records with an autoincrement column
as the primary key. I have created a typed dataset. In the UI I add one
record to the dataset. Then I pass this dataset to a webservice and from
there to my business layer objects. These objects perform some validation
and the send the dataset to the data access layer. Here I do a
SQLdataAdapter.update. I want to return the dataset with the correct
identity columnvalue. I thought that this happend automatically. The insert
succeeds, but the returned dataset has the original value for the identity
column (i.e. 0). I set the option in the wizard for creating an XML dataset.
I also tried to set properties like UpdatedRowsSource (I use a
commandbuilder) and AcceptChangesDuringFill. Tried ByRef and ByVal for the
dataset argument. Nothing seems to work. I don't want to retrieve the
inserted record manually from the db. Am I doing something wrong or is it
just not possible?
Thanks in advance.
Jung Steutel
Utrecht, The Netherlands
|
|
|
| |
|
| |
| |
| Henry Matthews |
Hi
Before you fill the dataset you need to add the table schema:
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
After you have posted you changes to you database, you will have to requery
your database to get any change may.
This should return the data you are looking for. However I would suggest
that you use command objects instead of the CommandBuilder. The command
builder is slower than command objects. It makes an extra trip to the
server for Meta base.
Hank
Henry Matthews, MCSD
Microsoft Visual Basic Developer Support
Email : Click here to reveal e-mail address <Remove word online. from address>
Want to know more? Check out the MSDN at msdn.microsoft.com or the
Microsoft Knowledge Base at support.microsoft.com
This posting is provided “AS IS”, with no warranties, and confers no rights.
--------------------
From: "Jung Steutel" <Click here to reveal e-mail address>
Subject: Retrieving autoincrement field after insert
Date: Fri, 1 Mar 2002 13:09:22 +0100
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <ewIrXnRwBHA.872@tkmsftngp02>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: a213-84-59-59.adsl.xs4all.nl 213.84.59.59
Path: cpmsftngxa09!tkmsftngxs01!tkmsftngp01!tkmsftngp02
Xref: cpmsftngxa09 microsoft.public.dotnet.framework.adonet:12793
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
I would like some help with inserting records with an autoincrement column
as the primary key. I have created a typed dataset. In the UI I add one
record to the dataset. Then I pass this dataset to a webservice and from
there to my business layer objects. These objects perform some validation
and the send the dataset to the data access layer. Here I do a
SQLdataAdapter.update. I want to return the dataset with the correct
identity columnvalue. I thought that this happend automatically. The insert
succeeds, but the returned dataset has the original value for the identity
column (i.e. 0). I set the option in the wizard for creating an XML dataset.
I also tried to set properties like UpdatedRowsSource (I use a
commandbuilder) and AcceptChangesDuringFill. Tried ByRef and ByVal for the
dataset argument. Nothing seems to work. I don't want to retrieve the
inserted record manually from the db. Am I doing something wrong or is it
just not possible?
Thanks in advance.
Jung Steutel
Utrecht, The Netherlands
|
|
|
| |
|
| |
|
| |
| David Sceppa (VIP) |
Jung,
The CommandBuilder will not generate the logic required to
fetch your new auto-increment values. However, it's easy to add
this logic yourself. If the CommandBuilder generates an
InsertCommand with a CommandText of:
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)
VALUES (@CustomerID, @EmployeeID, @OrderDate)
change the text to:
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)
VALUES (@CustomerID, @EmployeeID, @OrderDate);
SELECT SCOPE_IDENTITY() AS OrderID
and make sure that the InsertCommand's UpdatedRowSource is set to
UpdateRowSource.FirstReturnedRecord. This property setting tells
the Command to look for a row returned by the query and to assign
the data from that row to your DataRow object.
There should be a topic in the MSDN documentation titled
"Retrieving Identity or Autonumber Values" that explains this
scenario in more detail. If you have further questions on the
topic, please let us know.
If you're using SQL Server 7.0 or earlier, use @@IDENTITY
instead of SCOPE_IDENTITY(). For more information on the
difference between these features, see SQL Server Books OnLine.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2002 Microsoft Corporation. All rights reserved.
|
|
|
| |
|
|
| |
| |
| Jung Steutel |
David,
Thanks for the reply. The thing is however that I can make a form with a
datagrid (and update button) that automatically shows my identity column
(even when run on multiple clients). The data adapters are created during
designtime. I could copy the insertstatement en property values. but. I
would like to know why this isn't standard behaviour for a runtime created
dataadapter/dataset?I always want to know what my primary key is is after an
insert.
Jung Steutel
"David Sceppa" <Click here to reveal e-mail address> wrote in message
news:$ng2WlVwBHA.1572@cpmsftngxa07...
[Original message clipped]
|
|
|
| |
|
| |
| |
| Henry Matthews |
Hi
Here is an article that you may find helpful.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q310366
This behavior is by design.
Hank
Henry Matthews, MCSD
Microsoft Visual Basic Developer Support
Email : Click here to reveal e-mail address <Remove word online. from address>
Want to know more? Check out the MSDN at msdn.microsoft.com or the
Microsoft Knowledge Base at support.microsoft.com
This posting is provided “AS IS”, with no warranties, and confers no rights.
--------------------
From: "Jung Steutel" <Click here to reveal e-mail address>
References: <ewIrXnRwBHA.872@tkmsftngp02> <$ng2WlVwBHA.1572@cpmsftngxa07>
Subject: Re: Retrieving autoincrement field after insert
Date: Mon, 4 Mar 2002 10:11:50 +0100
Lines: 55
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <eP065z1wBHA.1524@tkmsftngp02>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: 213-84-124-33.adsl.xs4all.nl 213.84.124.33
Path: cpmsftngxa09!tkmsftngxs01!tkmsftngp01!tkmsftngp02
Xref: cpmsftngxa09 microsoft.public.dotnet.framework.adonet:12935
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
David,
Thanks for the reply. The thing is however that I can make a form with a
datagrid (and update button) that automatically shows my identity column
(even when run on multiple clients). The data adapters are created during
designtime. I could copy the insertstatement en property values. but. I
would like to know why this isn't standard behaviour for a runtime created
dataadapter/dataset?I always want to know what my primary key is is after an
insert.
Jung Steutel
"David Sceppa" <Click here to reveal e-mail address> wrote in message
news:$ng2WlVwBHA.1572@cpmsftngxa07...
[Original message clipped]
|
|
|
| |
|
| |
|
| |
| David Sceppa (VIP) |
Jung,
The DataGrid is displaying the ADO.NET-generated "dummy"
auto-increment value. That's not necessarily the new
auto-increment value that the database will generate. You still
need to supply the required logic in your DataAdapter to fetch
the value that the database generated after you submitted your
new row.
The various ways to retrieve new auto-increment values
depend on the functionality available through that particular
database. When working with SQL Server databases, you can use a
batch query to retrieve the new auto-increment values. With
Access 2000 (or more recent) databases, the only way to
accomplish this is to trap for the DataAdapter's RowUpdated event
because Access does not support batch queries. Unfortunately,
there's no easy way to dynamically determine how or if you can
retrieve auto-increment values from your database.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2002 Microsoft Corporation. All rights reserved.
|
|
|
| |
|
|
| |
|
|
|
| |
| joanzxp joanzxp |
(Type your message here)
--------------------------------
From: joanzxp joanzxp
|
|
|
| |
|
| |
|
|
|
|
|
|
|
|
BootFX
Reliable and powerful .NET application framework. |
|
|
|
|
|
|
