How do I get the AutoNumber (or Identity) for a newly inserted record with

  • Thread starter Thread starter Chris Lane
  • Start date Start date
Check out my article on www.beta.com\articles (Managing an Identity Crisis)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I was thinking about using identity columns as primary keys for all my
tables, but after reading your article, it seems i would have to
perform some tricks to implement them. So does anyone have a better
approach to generating id's for primary keys?

Would using GUIDS be a good way? Would they perform poorly?
 
Yes, you don't need a dataset at all, however, read William's article again.
You can use executescalar and a few other methods, but MSDE is free and
Output params can do a lot for you..
 
I've done the following for SQL Server Identity fields:
1) Build SQL Insert statement
2) Append "Select Scope_Identity();" (i.e "insert into table values
(...);select scope_identity();")
3) Execute this as Scalar to insert the record and then get a single result
back - your new ID.
 
Thanks Bill Vauhgn and the others for references to
articles they were extremely helpfull.
This is what I ended up doing:
Function WriteRecord(ByVal sql As String) As Int32

Dim ConnString As String
Try
ConnString = GetConnStr
("../_database/Test.mdb")
m_oOleDbConn = New OleDbConnection(ConnString)
m_oOleDbConn.Open()
m_oOleDbCmd = New OleDbCommand(sql,
m_oOleDbConn)
m_oOleDbCmd.ExecuteNonQuery()
m_oCmdIdentity = New OleDb.OleDbCommand
("SELECT @@IDENTITY", m_oOleDbConn)
WriteRecord = CInt
(m_oCmdIdentity.ExecuteScalar)
Catch exc As Exception
MailError(exc)
End Try
If the INT32 is overkill let me know.
Note: CleanUp of objects is in another Method.

Works great. One of the articles did strongly suggest
using a differrent command object for the Identity query.

Thanks Every one. I will go back and read the articles
again to
make sure I remember and understand them better. BTW-Great
article Bill.
 
Back
Top