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


Saturday, March 20, 2010

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


.

No comments :