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

  • stevegmag
  • Novice
  • Novice
  • User avatar
  • Posts: 29
  • Loc: Washington DC

Post 3+ Months Ago

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")
  5. conn.open dbConnectionString
  6. conn.BeginTrans
  7. rs.Open tblName, conn, adOpenKeyset, adLockOptimistic, adCmdTable
  8. [b]
  9. rs.AddNew ArrFlds, ArrValues
  10. rs.Update
  11. thisID = rs(strAutoFieldName)
  12. response.write "thisID: " & thisID &"<br>"
  13. ' not getting the ID[/b]
  14. rs.Close
  15. Set rs = Nothing
  16. conn.CommitTrans
  17. conn.close
  18. Set conn = Nothing
  19. If Err.number = 0 Then
  20. InsertRecord = thisID
  21. End If
  22. End Function
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

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)
  • stevegmag
  • Novice
  • Novice
  • User avatar
  • Posts: 29
  • Loc: Washington DC

Post 3+ Months Ago

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
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

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

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 71 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
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.