Back To Programming / Scripting / Coding Forum

ASP/MSSQL - getting auto-increment value of new record

  • stevegmag
  • Novice
  • Novice
  • User avatar
  • Joined: Jun 11, 2004
  • Posts: 29
  • Loc: Washington DC
  • Status: Offline

Post July 6th, 2004, 12:02 pm

I've seen this function used on 100s of tutorial sites, it works great on my DEV server but not my client's server.... is there a setting that would cause UPDATE not to preform as intended? The information is being added to the DB, but the ID is not being returned which is throwig off the rest of the script(s) that need this ID.

'strAutoFieldName = the field that auto-increments (ID is this case)


Code: [ Select ]
Function InsertRecord(tblName, strAutoFieldName, ArrFlds, ArrValues )
dim conn, rs, thisID
Set conn = Server.CreateObject ("ADODB.Connection")
Set rs = Server.CreateObject ("ADODB.Recordset")

conn.open dbConnectionString
conn.BeginTrans
rs.Open tblName, conn, adOpenKeyset, adLockOptimistic, adCmdTable
[b]
rs.AddNew ArrFlds, ArrValues
rs.Update
thisID = rs(strAutoFieldName)
response.write "thisID: " & thisID &"<br>"
' not getting the ID[/b]
rs.Close
Set rs = Nothing

conn.CommitTrans
conn.close
Set conn = Nothing

If Err.number = 0 Then
InsertRecord = thisID
End If
End Function
  1. Function InsertRecord(tblName, strAutoFieldName, ArrFlds, ArrValues )
  2. dim conn, rs, thisID
  3. Set conn = Server.CreateObject ("ADODB.Connection")
  4. Set rs = Server.CreateObject ("ADODB.Recordset")
  6. conn.open dbConnectionString
  7. conn.BeginTrans
  8. rs.Open tblName, conn, adOpenKeyset, adLockOptimistic, adCmdTable
  9. [b]
  10. rs.AddNew ArrFlds, ArrValues
  11. rs.Update
  12. thisID = rs(strAutoFieldName)
  13. response.write "thisID: " & thisID &"<br>"
  14. ' not getting the ID[/b]
  15. rs.Close
  16. Set rs = Nothing
  18. conn.CommitTrans
  19. conn.close
  20. Set conn = Nothing
  22. If Err.number = 0 Then
  23. InsertRecord = thisID
  24. End If
  25. End Function
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post July 6th, 2004, 12:02 pm

  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Joined: May 21, 2004
  • Posts: 3229
  • Loc: South Africa
  • Status: Offline

Post July 8th, 2004, 7:50 am

After you have execeuted the insert query try this SQL query

Code: [ Select ]
SELECT IDENT_CURRENT('yourTableName') AS C_ID


should return the newest value from your auto_inc field. (inside Microsoft SQL)
Watch me grow
  • stevegmag
  • Novice
  • Novice
  • User avatar
  • Joined: Jun 11, 2004
  • Posts: 29
  • Loc: Washington DC
  • Status: Offline

Post July 8th, 2004, 7:52 am

I added a select max(id) and it works... hopefully more than one entry at a time won't occur :D
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Joined: May 21, 2004
  • Posts: 3229
  • Loc: South Africa
  • Status: Offline

Post July 8th, 2004, 7:58 am

Yeah that is the way I would have done it with php or used the mysql_insert_id call
Watch me grow

Page 1 of 1

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: ScottG and 206 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.