473,379 Members | 1,167 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,379 software developers and data experts.

Select @@Identity returns 0

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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian
Nov 20 '05 #1
7 4085

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

---
Taiwo

"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

Nov 20 '05 #2
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.AcceptChanges() 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 OleDbRowUpdatedEventHandler routine? Or just directly after
a manual insert? microsoft.public.dotnet.framework.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

Nov 20 '05 #3
br*********@yahoo.com (Brian) wrote in message news:<be**************************@posting.google. 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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = 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
Nov 20 '05 #4
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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirectConn() ' opens connection to DB
bmcPersonMain.EndCurrentEdit()
daPersonMain.Update(dstPersonMain, "Person")
dstPersonMain.AcceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKOnly)

Catch ex As Exception
MsgBox("Exception: " & (ex.ToString))
Finally
fnCloseDPKDirectConn(cnConnection)
End Try
End Sub
****
Private Sub daPersonMain_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles
daPersonMain.RowUpdated

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

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If

End Sub
*****
"Ron Allen" <ra****@src-us.com> wrote in message news:<uS**************@TK2MSFTNGP11.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.AcceptChanges() 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 OleDbRowUpdatedEventHandler routine? Or just directly after
a manual insert? microsoft.public.dotnet.framework.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

Nov 20 '05 #5
Brian,
How about using e.Command.Connection 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*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirectConn() ' opens connection to DB
bmcPersonMain.EndCurrentEdit()
daPersonMain.Update(dstPersonMain, "Person")
dstPersonMain.AcceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKOnly)

Catch ex As Exception
MsgBox("Exception: " & (ex.ToString))
Finally
fnCloseDPKDirectConn(cnConnection)
End Try
End Sub
****
Private Sub daPersonMain_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles
daPersonMain.RowUpdated

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

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If

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

news:<uS**************@TK2MSFTNGP11.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.AcceptChanges() 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 OleDbRowUpdatedEventHandler routine? Or just directly after a manual insert? microsoft.public.dotnet.framework.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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("SELECT @@IDENTITY",
cnConnection)

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If
Also, the insert command is successful and does not cause any errors.

Thanks!
Brian

Nov 20 '05 #6
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**************@TK2MSFTNGP10.phx.gbl>...
Brian,
How about using e.Command.Connection 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*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles mnuSave.Click

Try
cnConnection = fnOpenDPKDirectConn() ' opens connection to DB
bmcPersonMain.EndCurrentEdit()
daPersonMain.Update(dstPersonMain, "Person")
dstPersonMain.AcceptChanges()

MsgBox("Record saved successfully.", MsgBoxStyle.OKOnly)

Catch ex As Exception
MsgBox("Exception: " & (ex.ToString))
Finally
fnCloseDPKDirectConn(cnConnection)
End Try
End Sub
****
Private Sub daPersonMain_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles
daPersonMain.RowUpdated

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

If e.StatementType = StatementType.Insert Then
newID = CInt(idCMD.ExecuteScalar())
e.Row("person_ID") = newID
End If

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

news:<uS**************@TK2MSFTNGP11.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.AcceptChanges() 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 OleDbRowUpdatedEventHandler routine? Or just directly after a manual insert? microsoft.public.dotnet.framework.adonet may be more
appropriate for this type of question.
Ron Allen
"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
> 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("SELECT @@IDENTITY",
> cnConnection)
>
> If e.StatementType = StatementType.Insert Then
> newID = CInt(idCMD.ExecuteScalar())
> e.Row("person_ID") = newID
> End If
>
>
> Also, the insert command is successful and does not cause any errors.
>
> Thanks!
> Brian

Nov 20 '05 #7
Brian,
I'm glad I could be of some help.
Ron Allen
"Brian" <br*********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
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**************@TK2MSFTNGP10.phx.gbl>...
Brian,
How about using e.Command.Connection 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.

Nov 20 '05 #8

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

Similar topics

2
by: Karthik.S | last post by:
Does DB2 have a select @@ identity equivalent. Karthik
1
by: Liming | last post by:
Hello all, I have a need to use "select @@identity" to get the autoid on the row that I just inserted. The insert store procedure executed correctly as if I took out the transaction, i can see...
2
by: Banski | last post by:
Hi, Im trying to use SELECT @@IDENTITY in a transaction. But it always returns 0. Im using an ms access database. And using the following code. What am i doing wrong? Best regards banski ...
2
by: Beowulf | last post by:
If I run this statement in Query Analyzer, it properly returns 1 for my testing table. But if I put the statement into a stored procedure, the stored procedure returns NULL. What am I doing...
4
by: nsikkandar | last post by:
Greetings, I am in need of getting "Auto Generated Number" of Primary Key from master table (I am, using Access) when I insert a row through ADO.NET from VB.NET. Based on this ID, I want to...
13
by: PinkBishop | last post by:
I am using VS 2005 with a formview control trying to insert a record to my access db. The data is submitted to the main table no problem, but I need to carry the catID to the bridge table...
4
by: Konrad Hammerer | last post by:
Hi! I have the following problem: I have a query (a) using another query (b) to get the amount of records of this other query (b), means: select count(MNR) as Number from...
2
by: alok | last post by:
Hi I have opened a serial port and then blocked on a select system call . but select returns even if no data is comming from other end. So when I read the buffer after select returns, I only...
3
by: cmrhema | last post by:
Hi, I have a table emp1 where i have two fields empno and empname. I have not assigned primary keys to any of these. After inserting values when I put on select @@identity or select @@identity...
1
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.