Access Database OleDb Insert Record Getting Identity

[Ghafran Abbas]

Use this function to insert a record and return the identity using an
OleDb Provider. For example, Access database, mdb file. It does not
allow mulple queries to run within one execute statement. So you have
to run the SELECT @@IDENTITY query separately, within the same
connection.


Public Function RunSQLReturnIdentity(ByVal SQL As String, ByVal
ParamArray CommandParameters() As OleDbParameter) As Integer

Dim Conn As OleDbConnection = GetConnection()
Dim retVal As Integer
Dim Cmd As New OleDbCommand(SQL, Conn)
Cmd.CommandType = CommandType.Text
Dim P As OleDbParameter
For Each P In CommandParameters
P = Cmd.Parameters.Add(P)
P.Direction = ParameterDirection.Input
Next
Cmd.ExecuteNonQuery()
Cmd.CommandText = "SELECT @@IDENTITY"
retVal = Cmd.ExecuteScalar()
Cmd.Dispose()
CloseConnection(Conn)
Return retVal

End Function

Example:

UserID = RunSQLReturnIdentity("INSERT INTO tbl_Security_Users
(UserName, [Password], IsSuperAdmin, FirstName, MiddleName, LastName,
Email, Phone, [Note]) " & _
"VALUES (@UserName, @Password, @IsSuperAdmin, @FirstName,
@MiddleName, @LastName, @Email, @Phone, @Note)", _
New OleDbParameter("@UserName", UserName.Text), _
New OleDbParameter("@Password", Password.Text), _
New OleDbParameter("@IsSuperAdmin", SuperAdmin.Checked), _
New OleDbParameter("@FirstName", FirstName.Text), _
New OleDbParameter("@MiddleName", MiddleName.Text), _
New OleDbParameter("@LastName", LastName.Text), _
New OleDbParameter("@Email", Email.Text), _
New OleDbParameter("@Phone", Phone.Text), _
New OleDbParameter("@Note", Note.Text))

- Ghafran Abbas

 

[Guest]

I'm a little confused about how to use the function. I've got a Sub that
fires fine and Inserts a record, but I'm struggling with retrieving the
AutoNumber ID just inserted. In your example, what is "UserID"? Is it the sub
fired when you submit the form?

 

[Ghafran Abbas]

The UserID is a variable of type integer. The RunSQLReturnIdentity
function returns the Identity column value after the insert. I modified
the code to make it more clear.

Public Function RunSQLReturnIdentity(ByVal SQL As String, ByVal
ParamArray CommandParameters() As OleDbParameter) As Integer

Dim Conn As OleDbConnection = GetConnection()
Dim retVal As Integer
Dim Cmd As New OleDbCommand(SQL, Conn)
Cmd.CommandType = CommandType.Text
Dim P As OleDbParameter
For Each P In CommandParameters
P = Cmd.Parameters.Add(P)
P.Direction = ParameterDirection.Input
Next
Cmd.ExecuteNonQuery()
Cmd.CommandText = "SELECT @@IDENTITY"
retVal = Cmd.ExecuteScalar()
Cmd.Dispose()
CloseConnection(Conn)
Return retVal

End Function

Private Sub btnAddUser_Click(source As Object, e As EventArgs)

Dim UserID As Integer = RunSQLReturnIdentity("INSERT
INTO tbl_Security_Users
(UserName, [Password], IsSuperAdmin, FirstName, MiddleName, LastName,
Email, Phone, [Note]) " & _
"VALUES (@username, @Password, @IsSuperAdmin,
@Firstname,
@MiddleName, @LastName, @Email, @Phone, @Note)", _
New OleDbParameter("@username", UserName.Text), _
New OleDbParameter("@Password", Password.Text), _
New OleDbParameter("@IsSuperAdmin", SuperAdmin.Checked),
_
New OleDbParameter("@Firstname", FirstName.Text), _
New OleDbParameter("@MiddleName", MiddleName.Text), _
New OleDbParameter("@LastName", LastName.Text), _
New OleDbParameter("@Email", Email.Text), _
New OleDbParameter("@Phone", Phone.Text), _
New OleDbParameter("@Note", Note.Text))

Response.Redirect("UserView.aspx?UserID=" & UserID)
End Sub

 

[Guest]

Thanks, it works great although the line feeds are a little tricky to me yet,
and I needed to open the connection in the function. Got it though, although
all I've got it doing is response.write (userId). On to the next step.