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

[Chris Lane]

Hi,

The following links explain how to get the value of the
Access AutoNumber Field of a newly created record using
classic ADO.
http://www.able-consulting.com/ADO_Faq.htm#Q9

http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q2
44/1/36.ASP&NoWebContent=1

Does anybody know how this can be done with ADO.NET?

Thanks

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Chris Lane]

Ok I read it. My next question is then can I do this
without using a dataset.

 

[Val Mazur]

Hi Chris,

Check next example

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

 

[Mike Margerum]

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?

 

[William Ryan]

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..

 

[Christopher Walls]

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.

 

[Chris Lane]

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.