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

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!

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

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

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.

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

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.

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.:)

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

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