Bloody Access Database

[carlblanchard]

Hey people stupid one here
I dont think access datbase supports @@Indentity
so there fore i need toknow whats the best way to reteive the recordID that ive just added to the db, i am useing access querys

cheers peps

Carl

[Lunatic3]

I am afriad you have to requery the database to know what was the newest record added. I use AutoNumber fields when I just want to have a primarykey, otherwise when it comes to caring for the number of record or such, I use a number field whose value is determined by the code and I know beforehand what I am going to put into it.

[Edneeis]

You can use MSDE instead, its like SQL Jr.

[carlblanchard]

dont really want to use SQL
i guess theres no fancy way to do this i saw the other day some like select max(recordId) or something like that????

[nswan]

this might help

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

[Desbaratizador]

Since the 2000 version Access supports @@Indentity.

[Lunatic3]

Originally posted by Desbaratizador
Since the 2000 version Access supports @@Indentity.

Thanks
In fact 'Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity'
http://support.microsoft.com/default...NoWebContent=1

[carlblanchard]

Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity'
http://support.microsoft.com/defaul...;NoWebContent=1

Can this be used in VB.Net Application ??? that code looks like normall ASP to me ?

[Pirate]

I never know this before so I've tried it myself and created a demo that you may have a look at it but it has little bug which is : the result I get all zeros I don't know why . Maybe the syntax is incorrect for the sqlstring . But at least you keep testing on this little buggy progy . . It's VB.NET 2003 with XP Database mdb .

[carlblanchard]

Priate, ive found this in the MSDN Lib
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconretrievingidentityorautonumbervalues.htm

Expains everyting to get your code working correct
I think you need to add or update the table before recovering the identity

If u get your code working can you post it up

Cheers Carl

[Pirate]

I tried that and still getting 0 when quering autoincrement field . I don't know what I'm missing here . Here's the updated version of the demo .

[carlblanchard]

Prirate your code works mate just fine

Only thing your fogetting to do is insert or update a record first,
just copy

Dim cmdStr As String = "SELECT @@Identity"
Dim cmdid As New OleDbCommand(cmdStr, MyConnection)
Dim ident As Integer = Int(cmdid.ExecuteScalar())
MsgBox("New Ident is: " & ident)

into any project where your inserting or update just after the commit

it will return the correct value

cheers for the help buddy

[Pirate]

Thanks for testing . . I was too lazy to add in the database ...