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!
|
Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success.
FREE! Go There Now!
| | | |
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
FREE! Go There Now!
| | | |
Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
FREE! Go There Now!
| | | |
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
| | | |
Learn from the best! Find out how developers use Rational ClearCase to be more flexible, innovative and deliver higher quality code in the Rational ClearCase Power Users eKit. This complimentary eKit provides a collection of materials, like articles, whitepapers, and demos that can help you become a power user of Rational ClearCase.
FREE! Go There Now!
| | | |
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
FREE! Go There Now!
| | | |
Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions.
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 14 
