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!
| | | |
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
| | | |
Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
| | | |
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
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 Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
| | | |
IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 16 
