Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @.@.IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
Best regards,
George
Hi,
this is not the best solution but try:
me.RecordSource="SELECT * FROM table1"
instead of
me.RecordSource="SELECT * FROM query"
It works for me.
Best regards, Matjaz
|||Well, of course this would work, but unfortunatly I relied on the SQL Server 2000 behavior a lot, so changing this would be a very much work.
From my point of view this clearly is a bug in SQL Server 2005 or the new OLE-DB provider. Or is there a new property in ADO so that the behavior is the same?
Anyone any ideas?
Best regards, George
|||It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.
Thanks
|||Sorry, because i have exactly the same problem, i didn't read your message completely ( I have read a hundreds of questions but no answers). We both came to the same conclusion.
George, I'd like to stay in touch with you. This is my e-mail: info@.finesa.si
Best regards, Matjaz
|||Here is a small sample to reproduce the behavior:
It turns out, that the false behavior only occurs if a foreign key column is set. So I've included this in the snippet. I've also noticed, that the CursorLocation Property of the Connection has to be set to adUseServer for the SQL-Server 2005 to make this work, for SQL-Server 2000 it has to be adUseClient.
Private Sub InsertRecord(addlink As Boolean)
On Error GoTo fail
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
con.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestIt;Data Source=.")
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM TestView WHERE ID = 0", con, adOpenKeyset, adLockOptimistic, adCmdText
rs.AddNew
rs("Nr") = "1"
rs("Description") = "Hello Nr. 1"
If addlink = True Then rs("Test2_ID") = 1
rs.Update
MsgBox "ID of new record is " & CStr(rs("ID"))
rs.Close
con.Close
GoTo quit
fail:
MsgBox Err.Description
quit:
End Sub
Here comes the T-SQL code for creating the sample database:
use master
go
create database TestIt
go
USE [TestIt]
GO
CREATE TABLE [dbo].[Test2]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
)
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Test2_ID] [int] NULL,
)
GO
ALTER TABLE [dbo].[Test] WITH CHECK ADD CONSTRAINT [FK_Test_Test2] FOREIGN KEY([Test2_ID])
REFERENCES [dbo].[Test2] ([ID])
GO
ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_Test2]
go
CREATE VIEW [dbo].[TestView]
AS
SELECT dbo.Test.ID, dbo.Test.Nr, dbo.Test.Description, dbo.Test.Test2_ID, dbo.Test2.Nr AS Nr2, dbo.Test2.Description AS Desc2
FROM dbo.Test LEFT OUTER JOIN
dbo.Test2 ON dbo.Test.Test2_ID = dbo.Test2.ID
GO
insert into Test2(Nr, Description) VALUES('1','The Number 1')
go
Johnny
|||I have encountered this same problem too. Has anyone found a solution?It's used in a complex bound form with an n-m relationship (3 records). Needless to say changing this code to reference the tables directly is a big change and lots of work.
|||
I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
||||||ADO 2.7, SQL Server 2005OdilonS wrote:
I′d like to tell you that I′m having almost the same problem. The main difference is that when I run the application in a Windows 98 se it runs ok and when I run it in a XP SP2 it doesn′t return the identity key. Another very important information is that I′m using only SQL 2000. So, I believe that the problem resides on the MDAC version being used. I′ll continuing looking for the solution.
Thank you for any help
I found the source of the error.
Be in mind that in my case the routine was working very well until
the MDAC 2.5.
I'm not sure about the versions 2.6 and 2.7, however,
on Windows 98 SE and XP with MDAC 2.8 it stops to run (ADO change his behavior ).The solution was just re-stablish the ActiveConnection before Open the
recordset again, like this .....
oRs.ActiveConnection = oCnwhere oRs and oCn are respectivelly
ADODB.Recordset and ADODB.Connection, valids objects.
Im having the EXACT same problem. We have not changed our code. When migrating from SQL Server 2000 to 2005 we immediately noticed the following database update pattern start to fail in a number of areas.
1. A new record is added to a recordset (The record contains an Identity
column)
2. The recordset is posted back to the database (RS.UpdateBatch)
3. A value is changed at the UI
4. The recordset is again posted back to the database
5. > Boom < The BatchUpdate fails - Source="Microsoft Cursor Engine"
Description="Row cannot be located for updating. Some values may have been
changed since it was last read.">
We have traced this down to a difference in the way identity column
information is returned to a recordset after new records are inserted into
the database. I examined this issue from 2 perspectives. 1, I ran SQL
profiler on both databases for the same transaction, and 2, I persisted my
recordset to XML before and after the first batch update (the one in which
the record is INSERTED) to look for differences between SQL Server 2000 and
2005. Here's what I found:
In the traces below, you'd notice a SELECT @.@.IDENTITY which occurs
immediately after the initial INSERT statement for SQL Server 2000 but not
for 2005. Why should this be missing? Presumably ADO itself issues this
SELECT statement. So why doesn't it do so with 2005?
SQL Server 2000 Profile Trace - This works!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (8.803915900000000e+007, 47, 2000, NULL, NULL, 0, 1, 1, 'Aug 8 2006
3:10PM', 'I')
SELECT @.@.IDENTITY
UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1
WHERE "PatientProcedureID"=54 <<-- Good! ADO has gotten this value
into its recordset.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1
SQL Server 2005 Profile Trace - This FAILS!
-
INSERT INTO "FreedomDemo".."PROCEDURES"
("PatientID",
"ProcedureTypeID",
"DateOfProcedureY",
"DateOfProcedureM",
"DateOfProcedureD",
"ExternalOrigin",
"OverrideExternalSource",
"RecAuthor",
"RecStamp",
"RecModType")
VALUES (4.51692e+008, 47, 2000, NULL, NULL, 0, 1, 1, '2006-08-08
15:21:24:000','I')
<< Hmm, no SELECT @.@.IDENTITY occurs after the preceding INSERT >>
UPDATE "FreedomDemo".."PROCEDURES"
SET "DateOfProcedureY"=2006,
"DateOfProcedureM"=8,
"DateOfProcedureD"=8,
"OverrideExternalSource"=1
WHERE "PatientProcedureID"=0 <<-- Wrong! This value is not 0; As an
Identity column this is set to some value.
AND "DateOfProcedureY"=2000
AND "DateOfProcedureM" IS NULL
AND "DateOfProcedureD" IS NULL
AND "OverrideExternalSource"=1
Here are the relevant abstracts from the recordsets serialized as XML
(complete XML attached, if you are interested). In the following snippets
you'll notice the following. Before the INSERT statement, the identity
column is not represented in the <z:row>. After the INSERT it appears.
However, with SQL Server 2000 this column is populated with the correct
value, but with SQL Server 2005, this column shows a value of 0 - incorrect.
This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted.
Can anyone help with this? This was originally posted on June 12, almost 2 months ago, but I don't see a
solution posted. This is a significant deviation of functionality between SQL Server 2000 and SQL Server 2005. Anyone using RS.BatchUpdate and identity columns will be affected. This should account for a significant number of Microsoft's customers. Has this been fixed?
Thanks very much for your help!
- Joseph Geretz -
SQL Server 2000: Before and after RS.Update - This works!
Before update: PatientProcedureID is null, not represented in the <z:row>:
<z:row PatientID="451692044" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:55:12" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>
After update: PatientProcedureID shows as 55 in the <z:row> GOOD!:
<z:row PatientProcedureID="55" PatientID="451692044" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:55:12"
RecModType="I"/>
SQL Server 2005: Before and after RS.Update - This FAILS!
-
Before update: PatientProcedureID is null, not represented in the <z:row>:
<z:row PatientID="759886920" ProcedureTypeID="47" DateOfProcedureY="2000"
ExternalOrigin="False" OverrideExternalSource="True" RecAuthor="1"
RecStamp="2006-08-08T15:59:57" RecModType="I" rs:forcenull="DateOfProcedureM
DateOfProcedureD"/>
After update: PatientProcedureID shows as 0 in the <z:row> This is WRONG!!!:
-
<z:row PatientProcedureID="0" PatientID="759886920" ProcedureTypeID="47"
DateOfProcedureY="2000" ExternalOrigin="False" OverrideExternalSource="True"
RecDeleted="False" RecAuthor="1" RecStamp="2006-08-08T15:59:57"
RecModType="I"/>|||
OK, I've found the solution. Basically, with SQL Server 2005, you need to
explicitly set the Resync behavior, although with SQL Server 2000,
developers who didn't set this explicitly have been getting away with the
default behavior in many (most?) cases. Here's a sample which is working for
me on SQL Server 2005, and I've checked as well that this is backward
compatible with SQL Server 2000.
RS.Properties("Update Resync") = adResyncInserts + adResyncAutoIncrement
RS.Properties("Resync Command") = "SELECT * FROM VALLPATIENTSPROCEDURES
WHERE PATIENTPROCEDUREID IN
(SELECT @.@.IDENTITY FROM PROCEDURES)"
http://windowssdk.msdn.microsoft.com/en-us/library/ms676738.aspx
The *default* behavior for a recordset in which these properties are
unspecified has definitely changed from SQL Server 2000 to SQL Server 2005.
<grrr>Thanks Microsoft. It's always nice when the newer technology works
differently than the older technolgy did, and of course, if you can make it
more difficult for us as well, that's great too! :-\ </grrr>
Hope this helps someone else avoid the pain I just went through with this.
But I still think that MS should release a service pack to put this back to the way it used to be!
--
Nov 21, 2006:
FYI: There is now a Microsoft Hotfix for this issue: http://support.microsoft.com/kb/920974/en-us
I haven't tested this, but the lierature suggests that this will resolve the issue.
Thanks IgorB for bringing this to our attention (post on page 2 of this thread).
|||Sorry if I resume this old post.
I have the same exact problema, but I wonder if this is a documented change in behaviour or a bug of SQL 2005 that should work as SQL 2000 but doesn't....
I ask this because if there is a bug (as Zoya Bashirova - MSFT says) I will try to escalate to point some attention on it (hoping to get some attention)
If this is a documented "feature" I will make my programmers work on it following Joseph Geretz suggestions.
This is a very impacting thing on my environment...
Thanks to all!
|||
From my point of view this is a bug, although one could argue it is a "feature":
The same ADO code works on a table and fails on a view. For me this clearly is a bug, but we decided to change our code, because this was a relativly easy job.
If you decide to escalate this issue, please keep us informed.
Thanks,
George
|||As my developers says (according to Joseph Geretz findings), this has to do with the default behaviourof an ADO recordset keyset, where the default behaviour has changed from "autoresync" to something else...I wonder if there is a way to specify on the server that this behaviour should change back to "autoresync": a server side parameter, a registry key or whatever...
The thing that seem really strange to me is that there is not a lot of documentation on this "issue".|||
Your developer is right, the default behavior changed. But the strange thing is, the behavior changed for views only. From my point of view this is not consistent. A view should behave exatly the same way as a table, which is not the case, so I call this a bug.
The easiest way to change this setting is via connection string, there is no way to change it on the server as far as I know.
It seems everyone is using ADO.NET these days, so they do not pay too much attention to guys like us developing with ADO 2.8
George
No comments:
Post a Comment