SELECT @@IDENTITY

[nsikkandar]

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 generate new rows in child
tables.

When I was looking on the net to get some Idea, I found there is a
feature SELECT @@IDENTITY to get "Auto Generated Number". But, I could
not find clear details about this feature.

I did the following code and executed, I am getting the result. But, I
am not sure, what I am doing is right. I would be thankful, if any one,
just go through my below code and let me know it is correct or it may
cause some error at some circumstances such as when multiple user
trying to execute the application and fire this method.

<-----Code Start Here ------->

Dim NewID As Integer

Public Shared Function ExecuteNonQuery(ByVal query As String) As
Boolean

Dim ConnectionString As String =
System.Configuration.ConfigurationSettings.AppSett ings("DSN") '
Declared in App.Config File for Connection String

Dim myConnection As New OleDbConnection(ConnectionString)

Try

myConnection.Open()

' Argument for the query is ""insert into Candidate_Personal
(Name,FatherName,DateOfBirth,PlaceOfBirth,Address, City,Pincode,Phone,Mobile,Email)
values ('" & txtCandName.Text & "', '" & txtCandFName.Text & "', '" &
txtCandDoB.Text & "', '" & txtCandPoB.Text & "', '" &
txtCandAddress.Text & "', '" & txtCandCity.Text & "', '" &
txtCandPincode.Text & "', '" & txtCandPhone.Text & "', '" &
txtCandMobile.Text & "', '" & txtCandEmail.Text & "') "

'Note that the table Candidate_Personal contains Autogenerated Primary
Key called ID.

Dim myCommand As New OleDbCommand(query, myConnection)

Dim myCommand1 As New OleDbCommand("SELECT @@IDENTITY",
myConnection)
myCommand.ExecuteNonQuery()
myCommand1.ExecuteNonQuery()
NewID = myCommand1.ExecuteScalar
'MsgBox("The Candidate ID Generated by the system is: " &
NewID)
Return True
Catch ex As Exception
MsgBox("Experiencing Technical Problem! Error at
DataManager.ExecuteNonQuery" & vbNewLine & ex.Message)
Return False
Finally
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If
End Try
End Function
<---- Code End Here ---->

Many Thanks in advance

Sikkandar

[zacks]

I would make the insert command and the select @@identity command all
part of the same sql command, and run it as a datareader query. This
insures that you will get back the identity of the row YOU inserted.

[Herfried K. Wagner [MVP]]

<ns********@gmail.com> schrieb:

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 generate new rows in child
tables.

HOW TO: Retrieve the Identity Value While Inserting Records into Access
Database By Using Visual C# .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;816112>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

[CAnderson [MVP]]

The SELECT @@IDENTITY returns a scalar value.... you'll want to execute your
command for it using .ExecuteScalar and putting it into a variable:

Dim NewID As Int32

NewID = myCommand1.ExecuteScalar

... Oh wait, I see that you are, but just before that you execute a
..ExecuteNonQuery... drop that line, it's probably what is causing the
interference.

Other than that, it looks like it should work. One item of caution, it's
generaly a bad idea to concatenate SQL strings together like that. That is
how SQL Injection hacks develop. A Parameterized Query would work best.

-Chris

"ns********@gmail.com" wrote:

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 generate new rows in child
tables.

When I was looking on the net to get some Idea, I found there is a
feature SELECT @@IDENTITY to get "Auto Generated Number". But, I could
not find clear details about this feature.

I did the following code and executed, I am getting the result. But, I
am not sure, what I am doing is right. I would be thankful, if any one,
just go through my below code and let me know it is correct or it may
cause some error at some circumstances such as when multiple user
trying to execute the application and fire this method.

<-----Code Start Here ------->

Dim NewID As Integer

Public Shared Function ExecuteNonQuery(ByVal query As String) As
Boolean

Dim ConnectionString As String =
System.Configuration.ConfigurationSettings.AppSett ings("DSN") '
Declared in App.Config File for Connection String

Dim myConnection As New OleDbConnection(ConnectionString)

Try

myConnection.Open()

' Argument for the query is ""insert into Candidate_Personal
(Name,FatherName,DateOfBirth,PlaceOfBirth,Address, City,Pincode,Phone,Mobile,Email)
values ('" & txtCandName.Text & "', '" & txtCandFName.Text & "', '" &
txtCandDoB.Text & "', '" & txtCandPoB.Text & "', '" &
txtCandAddress.Text & "', '" & txtCandCity.Text & "', '" &
txtCandPincode.Text & "', '" & txtCandPhone.Text & "', '" &
txtCandMobile.Text & "', '" & txtCandEmail.Text & "') "

'Note that the table Candidate_Personal contains Autogenerated Primary
Key called ID.

Dim myCommand As New OleDbCommand(query, myConnection)

Dim myCommand1 As New OleDbCommand("SELECT @@IDENTITY",
myConnection)
myCommand.ExecuteNonQuery()
myCommand1.ExecuteNonQuery()
NewID = myCommand1.ExecuteScalar
'MsgBox("The Candidate ID Generated by the system is: " &
NewID)
Return True
Catch ex As Exception
MsgBox("Experiencing Technical Problem! Error at
DataManager.ExecuteNonQuery" & vbNewLine & ex.Message)
Return False
Finally
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If
End Try
End Function
<---- Code End Here ---->

Many Thanks in advance

Sikkandar

[nsikkandar]

Wagner:

In your given link, they are getting "AutoIncreamentID" in the event of
"RowUpdated" which needs more coding. It seems, will be in safe side.

Zaks:

As per the link, given by Mr. Wagner and other references, we should
use another OleDbCommand for SELECT @@IDENTITY.

Your concept is NOT clear for me. Could you please explain your logic
with little sample code. ?

Chris Anderson,

Good catch. I dropped the line "myCommand1.ExecuteNonQuery()". I agree
with your suggession. But, I just took it as a scenario, when we are
forced to do such coding.

Once again many thanks guys.

-Sikkandar.