Results 1 to 8 of 8

Thread: Scope_identity + MS Access.

  1. #1

    Thread Starter
    Fanatic Member vijy's Avatar
    Join Date
    May 2007
    Location
    India
    Posts
    548

    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()"
    Visual Studio.net 2010
    If this post is useful, rate it


  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Scope_identity + MS Access.

    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.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Scope_identity + MS Access.

    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    Fanatic Member vijy's Avatar
    Join Date
    May 2007
    Location
    India
    Posts
    548

    Re: Scope_identity + MS Access.

    Quote Originally Posted by GaryMazzone View Post
    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,
    Visual Studio.net 2010
    If this post is useful, rate it


  5. #5
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Scope_identity + MS Access.

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    Fanatic Member
    Join Date
    Jun 2008
    Location
    Portland, OR, USA
    Posts
    659

    Re: Scope_identity + MS Access.

    @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

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Scope_identity + MS Access.

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

    Mazz1

  8. #8
    Fanatic Member
    Join Date
    Jun 2008
    Location
    Portland, OR, USA
    Posts
    659

    Re: Scope_identity + MS Access.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width