-
Jul 29th, 2010, 08:26 AM
#1
Thread Starter
Fanatic Member
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
-
Jul 29th, 2010, 08:28 AM
#2
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
-
Jul 29th, 2010, 09:34 AM
#3
Re: Scope_identity + MS Access.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jul 30th, 2010, 12:12 AM
#4
Thread Starter
Fanatic Member
Re: Scope_identity + MS Access.
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,
Visual Studio.net 2010
If this post is useful, rate it
-
Jul 30th, 2010, 01:37 AM
#5
Re: Scope_identity + MS Access.
I don't think SCOPE_IDENTITY() is even supported in Access, it is for SQL Server only.
-
Aug 1st, 2010, 12:45 AM
#6
Fanatic Member
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
-
Aug 1st, 2010, 11:32 AM
#7
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
-
Aug 1st, 2010, 11:46 AM
#8
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|