Avatar of michaelpaschalmp

asked on 

How to get SELECT @@Identity to work, without generating the "String index out of bounds" error?

After using JDBC to insert a new record into an MS Access table ("Rebalancing"), I need to fetch the generated AutoNumber key to use a foreign-key link in the descendant table.  Why does the "SELECT @@IDENTITY" SQL statement fail, with the error message "String index is out of range"?
Microsoft AccessSQL

Avatar of undefined
Last Comment
Avatar of frankytee
Flag of Australia image

@@IDENTITY is not valid in Access. Is your db Access or SQL Server?  and where are you executing this statement from?
if you have an Access front end linked to SQL back end then you need to create a "pass through" query to SQL in order to use @@IDENTITY.
An Access query to linked tables in SQL will not work here.
Avatar of MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

Support for @@Identity was introduced to Access 2000 with Jet 4's ANSI SQL-92 extensions.
The attached snippet is a working example from Access 2003 using ADODB

I think that the problem you have might be a missing alias in your Select @@Autonumber, try this:

                // Fetch the new Rebalancing ID.
                SQLQuery = "SELECT @@IDENTITY AS RebalanceID"
                SQLrs = SQLStatement.executeQuery(SQLQuery) ;
                SQLrs.next() ;
                Long RebalancingID = SQLrs.getLong("RebalanceID") ;

Function InsertRecord(FirstName As String, LastName As String) As Long
Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdText
    .CommandText = "Insert INTO Employees(Lastname, FirstName) Values('" & LastName & "', '" & FirstName & "')"
    rs.Open "Select @@Identity as NewID", cn, Options:=adCmdText
    InsertRecord = rs("NewID")
End With
End Function

Open in new window

Avatar of frankytee
Flag of Australia image

>Support for @@Identity was introduced to Access 2000 with Jet 4's ANSI SQL-92 extensions.
oops, i had no idea. i take back what i posted earlier.
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

>>oops, i had no idea
That was something I learned here @ EE also.  I was quite surprised when I first read it -- and pleased!
Avatar of michaelpaschalmp

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

"Select @@Identity" doesn't include a valid column name, so Access will create one - Expr1000 in the following example (again ADO in Access 2003):
     rs.Open "Select @@Identity", cn, Options:=adCmdText
     debug.print rs.Fields(0).Name
There is no automatic retrieval of the actual column name.

Trying to refer to this via any other field name will throw a "member not found in collection" error in VB.
"String Index Out of Range -1" sounds very much like the Java equivalent, which is why I suspect that the column name is the problem.
The alternative way of retrieving a field from a recordset is via its index (at least in ADO and DAO) as I did in the snippet above. Try this:

                Long RebalancingID = SQLrs.getLong(0) ;

One other comment:
>>>SQLrs.next() ;
DAO and ADO automatically position on the first record of a recordset, does JDBC not do that?
Avatar of michaelpaschalmp


>>>Long RebalancingID = SQLrs.getLong(0) ;

The error condition "String index out of range -1" has already happened prior to the column-name reference.  It happens with the attempt to execute SQLrs = SQLStatement.executeQuery(SQLQuery) .  However, in my VB .NET 2007 code, I do have to execute the RebalancingID = CLng (drSQL.Item(0)) statement, where drSQL is declared as an OleDbDataReader.

Incidentally, the SQLrs.next() statement is definitely required to position at the first record of the result set in JDBC.
Avatar of MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

Ahh - I should have re-read your post after looking at the snippet, you did say that the execQuery statement had failed
Glad you found a work round :-)
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo