If I want to change the GUID to Autonumber which is automatically generated in MSAccess. Can you help make a function in DALBase Class that will retrieve and return the newly added autonumber in the table. I have read an article in MSDN: "http://msdn2.microsoft.com/ru-ru/library/aa720536(VS.71).aspx"

It uses the SQL statement "SELECT @@IDENTITY" in Access, but how can I use it in as function in DALBase class that will return the newly added autonumber.

Anyone can help me? I really need it badly. Thanks

Your query if using Access, or stored procedure if using SQL Server must return an output parameter when the number that was inserted when the record is inserted. I hope this helps.

Thearon

Yes, thank you for that, but what I'm trying to accomplish it to be in the DALBase Class. I want to have a public function that returns the value of the newly added autonumber using MS Access as the back end database.

This is my sample code that I want it to be part of the DALBase Class that was discussed and used in the book (author by: Theoron Willis, is it you who replied to me above?)

     Dim conn As New OledbConnection("Provider=Microsoft.Jet.OLEDB.4.0; ....")

     Dim sql As String = "INSERT INTO tablename( FieldName ) " & _
                      "VALUES ('StringValue')"
     conn.Open()
     Dim cmd As New OleDbCommand(sql, conn)

     Dim i As Integer = cmd.ExecuteNonQuery()
     cmd.CommandText = "SELECT @@IDENTITY"

     Dim id As Integer = cmd.ExecuteScalar()

     MessageBox.Show("Newly added ID: " & id.ToString())

     cmd.Dispose()
     conn.Close()


If It will be encapsulated as a public function in the DALBase Class, it would be great. But how can I do that? Is it possible??? Can you help me?..... Thanks

Yes, I am the author of this book. The problem with having a function to return the last inserted value is that you have to worry about concurrency in your application. That is, if two people are inserting records at the same time you cannot guarantee that the number the function returns is the number of the record that a particular user inserted.

However, if this is not a concern then you should create a function to select the maximum number from the identify field and have the function return that value. For example: Select Max(fieldname) From tablename.

Thearon

Thank you veyr much Mr. Theoron, such a honor for me to have a conversation with the author of the book I bought last month. :-D
I have thought that one also. That's why I asked, "if its possible". However, still using MS Access, can we have any solution to that?

Instead of using
cmd.CommandText = "SELECT @@IDENTITY"
try
cmd.CommandText = "SELECT MAX(fieldname) FROM tablename"
The fieldname in the code above would be the AutoNumber field.

Thearon