Select @@Identity returns 0

[Brian]

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

[Taiwo]

I don't believe "SELECT @@IDENTITY" works for Access, but it works for SQL
Server.

---
Taiwo

"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

[Ron Allen]

Brian,
You have to have the same connection and be using an AutoNumber field as
the PK for this to work in Access. The call needs to be be done immediately
after the insert occurs. Also, call e.Row.AcceptCha nges() afterwards to
avoid having the row marked as having been edited. You also need to keep
the connection open between calls.
This type of call works just fine for me for both Access 2K and 2002.
Is this in a OleDbRowUpdated EventHandler routine? Or just directly after
a manual insert? microsoft.publi c.dotnet.framew ork.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

[Don B]

br*********@yah oo.com (Brian) wrote in message news:<be******* *************** ****@posting.go ogle.com>...

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

Look in the SQL Help for SCOPE_IDENTITY. It may be what you are
looking for, it may not. Just thought i would stick my oar in and row
a bit.

:D

Don

[Brian]

Thanks Ron. I initially thought it might be an issue with the DB
connection so, thanks to your advice, I *think* I am now using the
same connection. But I am still getting 0 returned. Here's a bit
more of my code. Any additional help is greatly appreciated as I am
stumped on this one!

****
Private Sub mnuSave_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirect Conn() ' opens connection to DB
bmcPersonMain.E ndCurrentEdit()
daPersonMain.Up date(dstPersonM ain, "Person")
dstPersonMain.A cceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKO nly)

Catch ex As Exception
MsgBox("Excepti on: " & (ex.ToString))
Finally
fnCloseDPKDirec tConn(cnConnect ion)
End Try
End Sub
****
Private Sub daPersonMain_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles
daPersonMain.Ro wUpdated

Dim newID As Integer
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If

End Sub
*****
"Ron Allen" <ra****@src-us.com> wrote in message news:<uS******* *******@TK2MSFT NGP11.phx.gbl>. ..

Brian,
You have to have the same connection and be using an AutoNumber field as
the PK for this to work in Access. The call needs to be be done immediately
after the insert occurs. Also, call e.Row.AcceptCha nges() afterwards to
avoid having the row marked as having been edited. You also need to keep
the connection open between calls.
This type of call works just fine for me for both Access 2K and 2002.
Is this in a OleDbRowUpdated EventHandler routine? Or just directly after
a manual insert? microsoft.publi c.dotnet.framew ork.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

[Ron Allen]

Brian,
How about using e.Command.Conne ction for your identity select as this
will be the actual connection used for this row. Also are you sure that the
Access field is an AutoNumber field? When you trap in the debugger you are
getting 0 for newID after it is assigned, correct?

I'd also suggest getting a copy of ADO.NET Core Reference by David
Sceppa as it has some very clear explanations and samples in both C# and
VB.NET.

Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Thanks Ron. I initially thought it might be an issue with the DB
connection so, thanks to your advice, I *think* I am now using the
same connection. But I am still getting 0 returned. Here's a bit
more of my code. Any additional help is greatly appreciated as I am
stumped on this one!

****
Private Sub mnuSave_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirect Conn() ' opens connection to DB
bmcPersonMain.E ndCurrentEdit()
daPersonMain.Up date(dstPersonM ain, "Person")
dstPersonMain.A cceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKO nly)

Catch ex As Exception
MsgBox("Excepti on: " & (ex.ToString))
Finally
fnCloseDPKDirec tConn(cnConnect ion)
End Try
End Sub
****
Private Sub daPersonMain_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles
daPersonMain.Ro wUpdated

Dim newID As Integer
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If

End Sub
*****
"Ron Allen" <ra****@src-us.com> wrote in message

news:<uS******* *******@TK2MSFT NGP11.phx.gbl>. ..

Brian,
You have to have the same connection and be using an AutoNumber field as the PK for this to work in Access. The call needs to be be done immediately after the insert occurs. Also, call e.Row.AcceptCha nges() afterwards to
avoid having the row marked as having been edited. You also need to keep the connection open between calls.
This type of call works just fine for me for both Access 2K and 2002. Is this in a OleDbRowUpdated EventHandler routine? Or just directly after a manual insert? microsoft.publi c.dotnet.framew ork.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Hello all -

I am trying to Insert a new record to an Access 2002 database.
Following the insert, I need to obtain the primary key of the inserted
row (person_ID) which is an Autonumber field. I am using the
following code (obtained from MSDN) to do this, but for some reason a
value of 0 is returned. Why is 0 returned????
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

[Brian]

That did the trick. Very interesting. I thank you for your help on
this. I just picked up a copy of ADO.NET Core Reference.
"Ron Allen" <ra****@src-us.com> wrote in message news:<eB******* *******@TK2MSFT NGP10.phx.gbl>. ..

Brian,
How about using e.Command.Conne ction for your identity select as this
will be the actual connection used for this row. Also are you sure that the
Access field is an AutoNumber field? When you trap in the debugger you are
getting 0 for newID after it is assigned, correct?

I'd also suggest getting a copy of ADO.NET Core Reference by David
Sceppa as it has some very clear explanations and samples in both C# and
VB.NET.

Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

Thanks Ron. I initially thought it might be an issue with the DB
connection so, thanks to your advice, I *think* I am now using the
same connection. But I am still getting 0 returned. Here's a bit
more of my code. Any additional help is greatly appreciated as I am
stumped on this one!

****
Private Sub mnuSave_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirect Conn() ' opens connection to DB
bmcPersonMain.E ndCurrentEdit()
daPersonMain.Up date(dstPersonM ain, "Person")
dstPersonMain.A cceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKO nly)

Catch ex As Exception
MsgBox("Excepti on: " & (ex.ToString))
Finally
fnCloseDPKDirec tConn(cnConnect ion)
End Try
End Sub
****
Private Sub daPersonMain_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles
daPersonMain.Ro wUpdated

Dim newID As Integer
Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.I nsert Then
newID = CInt(idCMD.Exec uteScalar())
e.Row("person_I D") = newID
End If

End Sub
*****
"Ron Allen" <ra****@src-us.com> wrote in message

news:<uS******* *******@TK2MSFT NGP11.phx.gbl>. ..

Brian,
You have to have the same connection and be using an AutoNumber field as the PK for this to work in Access. The call needs to be be done immediately after the insert occurs. Also, call e.Row.AcceptCha nges() afterwards to
avoid having the row marked as having been edited. You also need to keep the connection open between calls.
This type of call works just fine for me for both Access 2K and 2002. Is this in a OleDbRowUpdated EventHandler routine? Or just directly after a manual insert? microsoft.publi c.dotnet.framew ork.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...
> Hello all -
>
> I am trying to Insert a new record to an Access 2002 database.
> Following the insert, I need to obtain the primary key of the inserted
> row (person_ID) which is an Autonumber field. I am using the
> following code (obtained from MSDN) to do this, but for some reason a
> value of 0 is returned. Why is 0 returned????
>
>
> Dim newID As Integer = 0
> Dim idCMD As OleDbCommand = New OleDbCommand("S ELECT @@IDENTITY",
> cnConnection)
>
> If e.StatementType = StatementType.I nsert Then
> newID = CInt(idCMD.Exec uteScalar())
> e.Row("person_I D") = newID
> End If
>
>
> Also, the insert command is successful and does not cause any errors.
>
> Thanks!
> Brian

[Ron Allen]

Brian,
I'm glad I could be of some help.
Ron Allen
"Brian" <br*********@ya hoo.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

That did the trick. Very interesting. I thank you for your help on
this. I just picked up a copy of ADO.NET Core Reference.
"Ron Allen" <ra****@src-us.com> wrote in message

news:<eB******* *******@TK2MSFT NGP10.phx.gbl>. ..

Brian,
How about using e.Command.Conne ction for your identity select as this will be the actual connection used for this row. Also are you sure that the Access field is an AutoNumber field? When you trap in the debugger you are getting 0 for newID after it is assigned, correct?

I'd also suggest getting a copy of ADO.NET Core Reference by David
Sceppa as it has some very clear explanations and samples in both C# and
VB.NET.