G
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
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