JET, ADO.NET, onRowUpdated, and @@IDENTITY anomaly?

  • Thread starter Thread starter Timo
  • Start date Start date
T

Timo

[Cross-posted to microsoft.public.access and
microsoft.public.dotnet.framework.adonet]

Since I am using JET4 SP6, I think the intermittent problem I'm
experiencing with duplicate autonumbers does not relate to the
corruption of the autonumber generator in Access after a Compact +
Repair of the MDB, because this problem was supposedly fixed by Jet4
SP4. Reference:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database (KB254708).

So I am wondering if it is possible that the SELECT @@IDENTITY command
shown below, **IF IT IS CALLED IN A TIGHT LOOP**, can have a timing
issue such that the same @@IDENTITY value is returned twice?

So, how does the SELECT @@IDENTITY command work in JET?

TIA
Timo

-----------------------

Private cmdGetIdentity As OleDb.OleDbCommand
cmdGetIdentity = New OleDb.OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = MyConnection

While new rows to insert
insert a row
'// fires the OnRowUpdated event (see below)
...

Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
' Get the Identity column value and store in PK col in row
Dim IDValue as Integer
IDValue = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString)
e.Row("MyID") = IDValue
e.Row.AcceptChanges()
End If
End Sub
 
Arvin,
I think you may be mistaken about this. From what I read, the feature
was introduced with Jet4.0.

http://support.microsoft.com/default.aspx?scid=kb;en-us;232144

Timo




@@Identity only works on a SQL-Server database (or MSDE). JET is not
involved at all.

Are you saying that you are getting duplicate values in a SQL-Server
Identity column?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Timo said:
[Cross-posted to microsoft.public.access and
microsoft.public.dotnet.framework.adonet]

Since I am using JET4 SP6, I think the intermittent problem I'm
experiencing with duplicate autonumbers does not relate to the
corruption of the autonumber generator in Access after a Compact +
Repair of the MDB, because this problem was supposedly fixed by Jet4
SP4. Reference:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database (KB254708).

So I am wondering if it is possible that the SELECT @@IDENTITY command
shown below, **IF IT IS CALLED IN A TIGHT LOOP**, can have a timing
issue such that the same @@IDENTITY value is returned twice?

So, how does the SELECT @@IDENTITY command work in JET?

TIA
Timo

-----------------------

Private cmdGetIdentity As OleDb.OleDbCommand
cmdGetIdentity = New OleDb.OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = MyConnection

While new rows to insert
insert a row
'// fires the OnRowUpdated event (see below)
...

Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
Click to expand...
Click to expand...
 
What was happening is this: via ADO.NET, I would insert a new row into
an Access 2000 table, whose PK was an autonumber column, and would get
an error that the PK value already existed. The problem is intermittent
and has occured only a few times over a period of several days.
Compacting of the database could be involved as a factor, though it was
my understanding from KB254708 that this compacting-related autonumber-
duplicates problem had been resolved.

There are many factors to consider, but I wanted to understand how the
select @@Identity special query worked, while troubleshooting this.

Regards
Timo

@@Identity only works on a SQL-Server database (or MSDE). JET is not
involved at all.

Are you saying that you are getting duplicate values in a SQL-Server
Identity column?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Timo said:
[Cross-posted to microsoft.public.access and
microsoft.public.dotnet.framework.adonet]

Since I am using JET4 SP6, I think the intermittent problem I'm
experiencing with duplicate autonumbers does not relate to the
corruption of the autonumber generator in Access after a Compact +
Repair of the MDB, because this problem was supposedly fixed by Jet4
SP4. Reference:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database (KB254708).

So I am wondering if it is possible that the SELECT @@IDENTITY command
shown below, **IF IT IS CALLED IN A TIGHT LOOP**, can have a timing
issue such that the same @@IDENTITY value is returned twice?

So, how does the SELECT @@IDENTITY command work in JET?

TIA
Timo

-----------------------

Private cmdGetIdentity As OleDb.OleDbCommand
cmdGetIdentity = New OleDb.OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = MyConnection

While new rows to insert
insert a row
'// fires the OnRowUpdated event (see below)
...

Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
Click to expand...
Click to expand...
 
SELECT @@IDENTITY
does work on Access/JET databases (at least it's worked on the last few
versions).


--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Arvin Meyer said:
@@Identity only works on a SQL-Server database (or MSDE). JET is not
involved at all.

Are you saying that you are getting duplicate values in a SQL-Server
Identity column?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Timo said:
[Cross-posted to microsoft.public.access and
microsoft.public.dotnet.framework.adonet]

Since I am using JET4 SP6, I think the intermittent problem I'm
experiencing with duplicate autonumbers does not relate to the
corruption of the autonumber generator in Access after a Compact +
Repair of the MDB, because this problem was supposedly fixed by Jet4
SP4. Reference:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database (KB254708).

So I am wondering if it is possible that the SELECT @@IDENTITY command
shown below, **IF IT IS CALLED IN A TIGHT LOOP**, can have a timing
issue such that the same @@IDENTITY value is returned twice?

So, how does the SELECT @@IDENTITY command work in JET?

TIA
Timo

-----------------------

Private cmdGetIdentity As OleDb.OleDbCommand
cmdGetIdentity = New OleDb.OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = MyConnection

While new rows to insert
insert a row
'// fires the OnRowUpdated event (see below)
...

Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
' Get the Identity column value and store in PK col in row
Dim IDValue as Integer
IDValue = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString)
e.Row("MyID") = IDValue
e.Row.AcceptChanges()
End If
End Sub
Click to expand...
Click to expand...
 
I see that now. However, I have not gotten it to return the correct answer I
just built a test table and set an autonumber field. I then ran a query:

Select @@identity

and got a return of 0 (should have been 4)

I then tried:

Select @@identity from Table1 and it returned 3 0's

Looks like I've got some experimenting to do this weekend.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

William (Bill) Vaughn said:
SELECT @@IDENTITY
does work on Access/JET databases (at least it's worked on the last few
versions).


--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Arvin Meyer said:
@@Identity only works on a SQL-Server database (or MSDE). JET is not
involved at all.

Are you saying that you are getting duplicate values in a SQL-Server
Identity column?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Timo said:
[Cross-posted to microsoft.public.access and
microsoft.public.dotnet.framework.adonet]

Since I am using JET4 SP6, I think the intermittent problem I'm
experiencing with duplicate autonumbers does not relate to the
corruption of the autonumber generator in Access after a Compact +
Repair of the MDB, because this problem was supposedly fixed by Jet4
SP4. Reference:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database (KB254708).

So I am wondering if it is possible that the SELECT @@IDENTITY command
shown below, **IF IT IS CALLED IN A TIGHT LOOP**, can have a timing
issue such that the same @@IDENTITY value is returned twice?

So, how does the SELECT @@IDENTITY command work in JET?

TIA
Timo

-----------------------

Private cmdGetIdentity As OleDb.OleDbCommand
cmdGetIdentity = New OleDb.OleDbCommand()
cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
cmdGetIdentity.Connection = MyConnection

While new rows to insert
insert a row
'// fires the OnRowUpdated event (see below)
...

Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
' Get the Identity column value and store in PK col in row
Dim IDValue as Integer
IDValue = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString)
e.Row("MyID") = IDValue
e.Row.AcceptChanges()
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top