Scope_identity + MS Access.

am struggling to get the scope identity in MS Access.
its returning an error 'Character found at the end of statement'.

Adapter : OLEDB

Code:

---

INSERT INTO Leads (firstname, lastname) VALUES ('Bill', 'gates'); SELECT SCOPE_IDENTITY()"

---

For Access (I think anyway) you can not run two statements in the one command.

You should open the connection.
Run the insert command.
Once it returns reset the command text to Select Scope_Identity
and run the command again.

As long as it is on the same connection there is no probblem with this approach.

Check this thread:

http://www.vbforums.com/showthread.p...78#post3760878

Quote:

---

Originally Posted by GaryMazzone

For Access (I think anyway) you can not run two statements in the one command.

You should open the connection.
Run the insert command.
Once it returns reset the command text to Select Scope_Identity
and run the command again.

As long as it is on the same connection there is no probblem with this approach.

---

Thanks Gary,

whether this solution works when multiusers inserting data,

I don't think SCOPE_IDENTITY() is even supported in Access, it is for SQL Server only.

@GaryMazzone and Dee-U:
SCOPE_IDENTITY is NOT supported in Access. However, @@IDENTITY IS.

However, As Gary points out, MS Access does not support multiple statements. You need to use Gary's solution, but using SELECT @@IDENTITY instead of the SCOPE_IDENTITY Function. Follows is a silly example:

Code:

---


    Public Function INSERT_TEST() As Integer

        'An integer variable to catch the new Autonumber ID:
        Dim ReturnValue As Integer = -1

        'THIS is the magic SQL Statement:
        Dim SQLID As String = "SELECT @@IDENTITY"

        'THIS is a Silly Insert Example, using one of my local databases, just to test this:
        Dim SQLText As String = "INSERT INTO tbl*****er(UserName, LastName) SELECT 'MyName' As UserName, 'MyLastName' AS LastName"

        'Set up a connection:
        Using cn As New OleDb.OleDbConnection(My.Settings.acConnection)

            'Set up a Command. We will use this to execute both SQL
            'Statements in sequence while it is in the active scope:
            Using cmd As New OleDb.OleDbCommand()
                cmd.Connection = cn

                cn.Open()


                'Set up the INSERT SQL Statement in the command:
                cmd.CommandText = SQLText

                'OK. INSERT a new record:
                cmd.ExecuteNonQuery()

                'Reset the comand text to the "SELECT @@IDENTITY" Statment:
                cmd.CommandText = SQLID

                'Use execute Scalar, and be sure to convert the result to the proper (integer) type:
                ReturnValue = CType(cmd.ExecuteScalar(), Integer)

                cn.Close()
            End Using
        End Using

        'Output to the console to see the last Autonumber used:
        Console.WriteLine("Identity Inserted = " & ReturnValue)

        Return ReturnValue

    End Function

---

This Link Has more info (Scroll down to find the MS Access part). However, true to form, they have given a valid, but excessivly cumbersome peice of example code.

Hope that helps!

-RWS

If anyone looked at the link I provided it shows the solutuion with comments using @@Identity and MS Access.

I'm not sure how I missed that Gary, or I wouldn't have been redundant. Apologies.