SELECT @@IDENTITY

2 Mar 2006 8:29 AM

nsikkandar@gmail.com

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.AppSettings("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

2 Mar 2006 2:40 PM

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.

2 Mar 2006 3:11 PM

Herfried K. Wagner [MVP]

<nsikkan***@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/>

2 Mar 2006 3:42 PM

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

Show quoteHide quote

"nsikkan***@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.AppSettings("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
>
>

2 Mar 2006 8:33 PM

nsikkandar@gmail.com

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.