Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 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
  #1 (permalink)  
Old November 2nd, 2007, 05:54 PM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default IDENTITY Field

Hi guys,


I have an multiuser application that generates id numbers randomly but I would like to have SQL 2000 to generate those numbers using an Identity field and this is my question:

How do I read the identity value upon creation time...?

that way I don't have to generate numbers from the program and have the program check the database to see if that number already exists and if it does repeat the process again..to me that is a waste of time and too many trips to the database with multiple users.

Thanks.

=======================
Strange and crazy, but everything is possible
__________________
=======================
Strange and crazy, but everything is possible
  #2 (permalink)  
Old November 2nd, 2007, 06:14 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
quote:that way I don't have to generate numbers from the program and have the program check the database to see if that number already exists
And what if two clients create the same number and then try to insert a new record at the same time with that number?

You should rely on SQL to take care of this, or look at the uniqueidentifier data type which is also unique, but allows you to create an ID up front (a GUID) using code.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: My List by The Killers (Track 9 from the album: Sam's Town) What's This?
  #3 (permalink)  
Old November 2nd, 2007, 06:26 PM
Authorized User
Points: 245, Level: 4
Points: 245, Level: 4 Points: 245, Level: 4 Points: 245, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: Houston, TX, USA.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ricespn
Default

Thanks for the quick response. I have never used a GUID, my application is writen in VB6, so far the program check the table for that generated number if exists try to generate another one and checks the table again. I know it sounds redundant, how do I create a GUID using VB6. I will do a research but for the mean time can you point me to an example?

Thank you.

=======================
Strange and crazy, but everything is possible
  #4 (permalink)  
Old November 2nd, 2007, 06:41 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You need to do some old skool API calls....

http://www.devx.com/vb2themax/Tip/18261


Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Why Do I Keep Counting by The Killers (Track 11 from the album: Sam's Town) What's This?
  #5 (permalink)  
Old November 2nd, 2007, 11:40 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Don't use a GUID... world of pain...

Use an IDENTITY column and then use the SCOPE_IDENTITY function to get the ID it generated... Lookup SCOPE_IDENTITY in Books Online (comes free with SQL) for examples on how to use.

--Jeff Moden


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting a Value into an SQL Identity Field in VB vbmazza VB Databases Basics 1 April 27th, 2005 02:45 PM
getting identity field chanceeve Classic ASP Databases 2 June 5th, 2004 01:31 AM
SQL Server Identity field won't increment Ron Howerton VB.NET 2002/2003 Basics 8 April 22nd, 2004 12:14 PM
Identity field in the Data Row bmains ADO.NET 2 December 17th, 2003 04:34 PM





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