retrieving AutoID

Results 1 to 4 of 4

Thread: retrieving AutoID

  1. #1
    moomjean Guest

    Default retrieving AutoID

    Hi. I&#039ve tried a couple suggestions from people on the message board to no avail over the last couple days. When adding a record to a table I want to grab the new autoid before closing the recordset.<BR><BR>I&#039ve tried this:<BR>Dim newID ...<BR>&#039 connect, recordset, add record, blah blah blah...<BR>myRS.Update <BR>newID = myRS("autoID")<BR>myRS.Close<BR>&#039 displaying newID does not show anything<BR><BR>I&#039ve also tried variations of @@IDENTITY. Someone suggested @@IDENTITY would be recognized if I called it out. I can&#039t get that to work, either.<BR><BR>Anyone have any foolproof solutions?<BR><BR>Thanks!

  2. #2
    Join Date
    Dec 1969

    Default RE: retrieving AutoID

    here&#039s an exact copy of our stored procedure for our sql db that returns an @@IDENTITY. Disregard the extra info.<BR>Alter Procedure ap_InsertNewUser<BR> @pFirstName VARCHAR(40),<BR> @pLastName VARCHAR(40),<BR> @pUserName VARCHAR(30),<BR> @pPassword VARCHAR(30),<BR> @pDigID BIT<BR>As<BR>DECLARE @tError BIT<BR> <BR> <BR>IF EXISTS (SELECT idsUserID FROM tblUser WHERE chrUserName LIKE @pUserName)<BR> BEGIN<BR> SELECT @tError = 1<BR> SELECT @tError AS Error, @@IDENTITY AS UserID<BR> END<BR>ELSE<BR> BEGIN<BR> SELECT @tError = 0<BR> INSERT INTO tblUser (chrFirstName, chrLastName, chrUserName, chrPassword, blnDigitalImaging)<BR> VALUES (@pFirstName, @pLastName, @pUserName, @pPassword, @pDigID)<BR> SELECT @tError AS Error, @@IDENTITY AS UserID<BR> END<BR><BR>So you would get the @@IDENTITY by doing this<BR>newID = rs("UserID")

  3. #3
    moomjean Guest

    Default RE: retrieving AutoID

    Does anyone know how to do this without using a stored procedure in SQL Server?

  4. #4
    LeeAnnie Guest

    Default RE: retrieving AutoID

    Here you go. Hope this helps!<BR><BR>Set Cm = Server.CreateObject("ADODB.Recordset")<BR> cntStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:pathnamedb1.mdb;"<BR> Cm.Open "TableName", cntStr, adOpenKeySet, adLockPessimistic, adCmdTable<BR> Cm.AddNew<BR> LName = replace(LName,"&#039","&#039&#039")<BR> Cm.Fields("empFirstName")=request("FirstName")<BR> Cm.Fields("empLastName")=request("LastName")<BR> Cm.Fields("empEmailAddress")=request("Email")<BR> Cm.Fields("empWorkPhoneArea")=request("PhoneArea") <BR> Cm.Fields("empWorkPhoneExch")=request("PhoneExch") <BR> Cm.Fields("empWorkPhoneNum")=request("Phone")<BR> Cm.Update<BR> empID = Cm.Fields("empautoID")<BR> Cm.Close<BR> set Cm=Nothing <BR><BR>The third to the last line will return thew value of the autoID.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts