How to get @@Identity after insert operation in Access Database

Solution :

I am going to explain this in Vb.Net.

Create one vb.net project and add one from frmAddContact.vb in your project.
and take one button and txtbox in that form.

Create Dataaccesslayer class file with name, DAL.vb

and add below code to respective files, please find the code below.


frmAddContact.vb

Dim ObjDataAccess As New DAL()
Public Class frmAddContact
    Dim ObjDataAccess As New DAL()

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

Dim iRelationID As New Integer
            iRelationID = ObjDataAccess.ExecuteNonQueryGetIdentity("INSERT INTO PAM_Relationship ( tRelationshipName,dCreatedDate,dModifiedDate) VALUES ('" & _
                                          cmbrelationship.Text.Trim() & "',#" & _
                                          Date.Now & "#,#" & _
                                          Date.Now & "# )")

End Sub
End Class

DAL.vb

Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb

Public Class DAL
    Dim ConnStr As String = Configuration.ConfigurationSettings.AppSettings("DBConn").ToString()

Public Function ExecuteNonQueryGetIdentity(ByVal Query As String) As Integer

        Dim iResult As Integer
        Try
            Dim Conn As New OleDb.OleDbConnection(ConnStr)
            Conn.Open()
            Dim cmd As New OleDb.OleDbCommand(Query, Conn)

            cmd.ExecuteNonQuery()
            cmd.CommandText = "Select @@Identity"
            iResult = cmd.ExecuteScalar()
            Conn.Close()
            cmd.Dispose()
        Catch ex As Exception
            ex = Nothing
        End Try

        Return iResult
    End Function

End Class

After insert operation, when you will check the value of return value of iRelationID, you will find the inserted row id.


Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com


.