Autonumber - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Autonumber

Author

Topic

jewly
Starting Member

4 Posts

Posted - 2004-07-30 : 06:47:50

Hi, I am inserting data values into a table which has an autonumber as its primary key using VB.NET and SQL server.

I was wondering if there is a query to get the number of the row just being created so it can be printed on screen to the user before they actually add the new data??

Thanks in advance for any help

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-30 : 07:12:19

before they add the data.....SELECT MAX(ID) FROM TABLE1
after they add the data.....SELECT @@IDENTITY

mibheenick
Starting Member

12 Posts

Posted - 2004-07-30 : 07:13:43

if u are using transact sql on sql server 2000 then this should do the job....

Declare @Rank as int
Select @Rank = max(NameOfThatPK) from tableName
SELECT @Rank = isnull(@NumLot, 1)

the above should do the job!

jewly
Starting Member

4 Posts

Posted - 2004-07-30 : 09:12:54

Thank you for solving my problem!!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-30 : 15:02:35

Shouldn't that be SELECT MAX(ID) + 1 FROM TABLE1 ?

And please realize, jewly, that this does not guarantee they will actually get that number. If you have multiple users and another user inserts their data between the time you retrieve the MAX(ID) and the time this user inserts their data, then this user will get the next value after that. The only way to guarantee a user will get the ID number you think is to do the number incrementing manually and avoid the use of IDENTITY.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-30 : 15:14:23

Here's another option...

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

BUT...I don't like it when you say...

quote:

so it can be printed on screen to the user before they actually add the new data??

Do you plan on holding on to it?

Don't you think this will cause problems?

Brett

8-)

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-01 : 19:18:23

Hi X002548,

Can you please explain this Trigger.

CREATE TRIGGER myTrigger99NextID ON myTable99NextID
FOR INSERT, DELETE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted)
BEGIN
-- Nope only want to keep 1 row
ROLLBACK
END
IF EXISTS (SELECT * FROM deleted)
BEGIN
-- Nope only want to keep 1 row
ROLLBACK
END
END
GO

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-02 : 03:50:57

All this trigger does is ensure that the table always has only one row, since the table will only need one row and one column (containing the next available id). Brett's solution is sort of a manual Identity column, because he doesn't like the automatic ones

You probably don't need it if you can go with the standard identity (autonumber) columns in SQL Server.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources