Wrox Programmer Forums
|
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
 
Old August 12th, 2004, 04:14 AM
Authorized User
 
Join Date: Jul 2004
Posts: 36
Thanks: 0
Thanked 0 Times in 0 Posts
Default auto gerate of primary key

I want that when a new student's recored entered into the database
the ID(primary key) generate automattically.
what's the sol.

abhinavjain
__________________
abhinavjain
 
Old August 12th, 2004, 04:21 AM
Friend of Wrox
 
Join Date: Jun 2004
Posts: 331
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to qazi_nomi
Default

For this first you select last ID from table and add 1 to that value and then insert into the table

Numan
--------------------------------------------------
Love is the most precious thing of this world. So find and grab it!
 
Old August 12th, 2004, 07:25 AM
Authorized User
 
Join Date: Jun 2003
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

gazi_nomi's solution isn't a good idea as if 2 people select the same value and add 1 the pk will be the same for 2 records or somebody will recieve an error. the best thing to do is to use a numeric field with the identity seed and identity increment set to 1, then when you need to retrieve the new identity use SELECT @@identity.

im sure there is some way of using an insert trigger to set this up automatically, but ive only been using SQLserver for 4 days and alot of that time has been setting it up :)

hope that help
Andy
 
Old August 12th, 2004, 10:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Andy's solution will work- as long as no one else inserts on the same connection.. then you will get the wrong identity back...

Personally, I always use GUID as my "un natural" key and then generate them before my insert.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee
 
Old August 12th, 2004, 11:01 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Hal,

Under what conditions could 2 users be making a database call on the same connection? I'm not clear on how this is possible.
 
Old August 12th, 2004, 07:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

When two people go to insert into a database, most databases are transactional, so via a locking scheme, only one person could add a record at a time. I've seen out on the web people using the select @@identity to return the ID. Simultaneous connections aren't a problem with transactional databases.

SQL Server (for example) has various isolation options which you can choose to lock users out, depending on your requirements.

You could also do DataAdapter.Update, which would return the database identity information to the dataset/datatable.

Brian
 
Old August 12th, 2004, 11:05 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Brian,
Thanks for the explanation, however it doesn't really answer my question. Hal speaks of the possibility of two users being on the same connection. How can this (ever?) be possible? When two users are browsing a web application "simultaneously" their requests are being processed separately and are thus using two connections (not their connection to the server which is of course 2 separate, but the DB connections). I'm just curious if I have missed some scenario that I should be on the lookout for.
 
Old August 13th, 2004, 01:55 AM
Friend of Wrox
 
Join Date: Jul 2004
Posts: 623
Thanks: 0
Thanked 1 Time in 1 Post
Default

we have here many instances(Connection objects,adapter objects)
its impossible exactly at the same time two users could update a sqltable
but maybe due to delays of users send their update query there could be errors could be handled with datatable and adapter instances of users...
I think it is not practical two users could change a sqltable exactly at the same time so using GUID as "un natural" key while inserting rows isn't essential.

--------------------------------------------
Mehdi.:)
 
Old August 13th, 2004, 09:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

If you are using a connection pool- which we are- you could end up executing a SQL statement on a connection and then the next statement *COULD* end up on a different connection. Someone else could have also used that connection in the time between uses.

Now, I freely admit that the newer method of MSFT giving you the last Identity column generated on any particular connection is a VAST improvement over the old method of Last Identity generated on ANY connection- I just don't trust it. Sure, you can turn on transactions, but I don't need them in this case. I could lock things to protect myself, but then I end up with Excessive locking- all of which I can avoid with a simple "myID = guid.new" - then I have no worries about any types of problems.

In addition, using a GUID, I can do more work BEFORE needing the database. If I need to have data span objects, or even relationships in data in the same object, I don't need to worry about hitting the DB, inserting a partial row to get an ID and then use the ID... I generate my own ID for the row that I need to create relationships with.

I like touching the database once for each row- since changing to a GUID for any tables without natural Identity fields I am able to do that and the code I have to write has simplified.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee
 
Old August 14th, 2004, 05:10 AM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anand Patel
Default

I have also use identity Seed for Getting Auto Incrementing Unique ID. Its good to allow Server to handle Deadlock Condition so use Identity Seed when ever u fill confussion.

In case of two users simultaniously trying to Inserting a data then u can figure a qurey in bellow format so that it will return u a Unique ID that is generated for that Records only

  RecordSet = Connection.Execute ("Insert into Table Name(Field Name) values(field Values...); Select @@identity as NewID")

Above Statement will return u a value of Unique Id as a field of Recordset.Field("NewID").Value which is generated for that row only. So in case if two users also send insert query at same time they get their Unique Value in their Record set.

I have tested this on VB & SqlServer and its working fine without generating any Error in case two users try to insert record at same time

Anand Patel:)


Anand Patel





Similar Threads
Thread Thread Starter Forum Replies Last Post
auto increment primary key jcuga SQL Language 2 June 3rd, 2007 09:04 AM
How to auto increment primary key method SQL Server 2000 5 May 24th, 2005 03:25 PM
How to Auto Generate ID (Primary Key) SQL Database havering SQL Server ASP 1 December 9th, 2004 05:33 AM
How to Auto Generate ID (Primary Key) SQL database havering SQL Server 2000 9 December 1st, 2004 10:38 AM
auto increment primary key-compile error junemo Oracle 2 June 16th, 2004 08:53 AM





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