That's pretty much it, yes. You could also create a stored procedure which'll do the insert and return the identity in one fell swoop... for more details take a look at :- http://www.4guysfromrolla.com/webtech/102598-1.shtml And this page describes in more detail the method you've just described :- http://www.jansfreeware.com/articles/adoasp.html One thing to be aware of using @@Identity is that it will return the identity for the last record added _on that connection_. So if you add a record, then add another record somewhere else using the same connection and then select @@identity, it'll bring back the identity of the latest record. Unlikely you'll come across that scenario, but useful to know for debugging purposes. Dan www.diado.com
-----Original Message----- From: Martin Brown [mailto:[EMAIL PROTECTED] Sent: 25 August 2004 11:45 To: [EMAIL PROTECTED] Subject: RE: [AspClassicAnyQuestionIsOk] Record insert error Hi Dan, I like the idea of the SELECT @@IDENTITY Would I: 1. Insert the record using the following code: strnewins = "INSERT INTO tblCrmeBayReferrer (ReferrerName, ReferrerEmail, ReferrereBayID) VALUES ('" & objRSreferrals("YourName") & "', '" & objRSreferrals("YourEmail") & "', '" & objRSreferrals("YoureBayID") & "')" Set MM_editCmd = Server.CreateObject("ADODB.Command") MM_editCmd.ActiveConnection = MM_crm_STRING MM_editCmd.CommandText = strnewins MM_editCmd.Execute 2. Then retrieve the @@IDENTITY by creating a new recordset object 3. Or is there a way of performing them both so @@IDENTITY does not return NULL Cheers Martin _____ From: Dan Powderhill [mailto:[EMAIL PROTECTED] Sent: 25 August 2004 11:19 To: [EMAIL PROTECTED] Subject: RE: [AspClassicAnyQuestionIsOk] Record insert error Do you not need to requery before getting the ID back? objRSreferadd.Update intr = objRSreferadd("ReferrerID") objRSreferadd.Requery I usually use "SELECT @@Identity" to return the primary of a new record added, but I guess that's just a personal way of working. Dan www.diado.com -----Original Message----- From: Martin Brown [mailto:[EMAIL PROTECTED] Sent: 25 August 2004 11:04 To: [EMAIL PROTECTED] Subject: [AspClassicAnyQuestionIsOk] Record insert error Hi I do this all the time and have never had a problem so I'm going insane looking for my error. I add a record to a SQL Server database and I need the record ID back. No errors are produced but when I response.write the record id's there is nothing output which is causing later code to fail I have checked the database - the field is an identity field and the records are being inserted with new id fields - so why isn't it returning a value???? Code: dim objRSreferadd set objRSreferadd=Server.CreateObject("ADODB.recordset") objRSreferadd.open "tblCrmeBayReferrer", objConn, adOpenKeyset, adLockPessimistic, adCmdTableDirect objRSreferadd.AddNew objRSreferadd.Fields("ReferrerName") = objRSreferrals("YourName") objRSreferadd.Fields("ReferrerEmail") = objRSreferrals("YourEmail") objRSreferadd.Fields("ReferrereBayID") = objRSreferrals("YoureBayID") objRSreferadd.Update intr = objRSreferadd("ReferrerID") response.write "Referrer ID = " & intr & "<br>" Cheers Martin ________________________________________________________________________ This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This communication may contain confidential material. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error, please notify the sender. The statements and opinions expressed in this message are those of the author and do not necessarily reflect those of the company. The company does not take any responsibility for the views of the author. As internet communications are not secure we do not accept legal responsibility for the contents of this message nor responsibility for any change made to this message after it was sent by the original sender. We advise you to carry out your own virus check before opening any attachment as we cannot accept liability for any damage sustained as a result of any software viruses. [Non-text portions of this message have been removed] Yahoo! Groups Sponsor ADVERTISEMENT Yahoo! Groups Links To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Yahoo! Groups Sponsor ADVERTISEMENT < http://us.ard.yahoo.com/SIG=12903m7hc/M=295196.4901138.6071305.3001176/D=gr oups/S=1705006764:HM/EXP=1093515738/A=2128215/R=0/SIG=10se96mf6/*http:/compa nion.yahoo.com> click here < http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S= :HM/A=2128215/rand=484800303> _____ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] cribe> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service < http://docs.yahoo.com/info/terms/> . [Non-text portions of this message have been removed] Yahoo! Groups Sponsor ADVERTISEMENT <http://us.ard.yahoo.com/SIG=129mug3e6/M=295196.4901138.6071305.3001176/D=groups/S=1705006764:HM/EXP=1093517110/A=2128215/R=0/SIG=10se96mf6/*http://companion.yahoo.com> click here <http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=:HM/A=2128215/rand=819933330> _____ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> $9.95 domain names from Yahoo!. Register anything. http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/saFolB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspClassicAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
