Wrox Programmer Forums
|
BOOK: Beginning VB.NET Databases
This is the forum to discuss the Wrox book Beginning VB.NET Databases by Thearon Willis; ISBN: 9780764568008
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning VB.NET Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 29th, 2007, 12:37 AM
Registered User
 
Join Date: Nov 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to iii_carlos Send a message via Yahoo to iii_carlos
Default AutoNumber in MS Access

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.

 
Old January 30th, 2007, 05:50 AM
Registered User
 
Join Date: Nov 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to iii_carlos Send a message via Yahoo to iii_carlos
Default

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

 
Old January 30th, 2007, 07:01 PM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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
 
Old January 31st, 2007, 05:45 AM
Registered User
 
Join Date: Nov 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to iii_carlos Send a message via Yahoo to iii_carlos
Default

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


 
Old January 31st, 2007, 06:11 AM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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
 
Old January 31st, 2007, 07:31 AM
Registered User
 
Join Date: Nov 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to iii_carlos Send a message via Yahoo to iii_carlos
Default

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?

 
Old January 31st, 2007, 08:23 PM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Acces Autonumber teddyk ASP.NET 2.0 Basics 7 October 22nd, 2008 09:07 PM
Accessing AutoNumber field of MS Access Using ASP MuthuAL Classic ASP Databases 1 January 22nd, 2005 11:30 AM
Access Autonumber chall90909 Access ASP 1 November 21st, 2004 10:26 AM
Autonumber on Access 2002 pdunning Access 1 August 23rd, 2004 10:33 AM
ASP and Access AutoNumber phungleon Access ASP 2 October 10th, 2003 03:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.