This is the CORRECT way to get the Auto Increment ID from a database after Insert using SQL. - Assumptions:
- SQL is a valid Insert statement.
- DSNString is a valid Connection String
- I am using ADO2.5 (I do not know if this works with other versions - if not then this is a good reason to get it)
- I tested this on SQL7 as well as ACCESS 2000 using Jet4
I know that this has driven most people crazy. Here is the code function Public Function ExecuteID(SQL As String) As Long
'Tested with ACCESS 2000 and SQL7.0 using ADO2.5
On Error GoTo LocalError
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim AutoID As Long With rs
'Prepare the RecordSet
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = "SELECT @@IDENTITY"
End With With cn
.ConnectionString = DSNString
.CursorLocation = adUseServer
.Open
.BeginTrans
.Execute SQL, , adCmdText + adExecuteNoRecords
With rs
.ActiveConnection = cn
.Open , , , , adCmdText
AutoID = rs(0).Value
.Close
End With
.CommitTrans
.Close
End With
Set rs = Nothing
Set cn = Nothing
'If we get here ALL was Okay
ExecuteID = AutoID
Exit Function
LocalError:
LastError = Err.Number & " - " & Err.Description
If cn.State = adStateOpen Then
cn.RollbackTrans
cn.Close
End If
Set rs = Nothing
Set cn = Nothing
ExecuteID = False
End Function Brian Gillham
MailTo:Brian@FailSafe.co.za
FailSafe Systems
http://www.failsafe.co.za |
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Code Articles
More By Brian GillHam  developerWorks - FREE Tools!
|
<a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
FREE! Go There Now!
| | | |
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
| | | |
IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects.
FREE! Go There Now!
| | | |
As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br />
FREE! Go There Now!
| | | |
Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
FREE! Go There Now!
| | | |
Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Tester for SOA Quality V7.0.1, a functional and regression testing tool that enables the creation, comprehension, modification and execution of testing GUI-less Web services.
FREE! Go There Now!
| | | |
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 16 
