Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Professional
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 25th, 2008, 05:23 AM
Authorized User
Join Date: Apr 2008
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto Increment Field Problem in SQL Server 2000


I am using ASP.NET2.0 with SQL Server 2000. My problem is related to auto increment field. I inserted the data to SQL table from Excel datasheet through ASP.NET technology. I can insert the data exactly but when I delete some data from SQL table and again reload with some data, there arises the problem.
There is a gap in the auto increment field( using int IDENTITY(1,1)). The details of the problem goes here:
1. suppose I have 5 rows in the table. I deleted all the row. Next time when I load some data it starts from row numer 6 in autogenerated field.
2. If I delete any row from the beginning or middle (not last row), the autoincrement doesnot work. Though row shifted up but the number doesnot get incremented automatically.

Is there anyone, who could help me to solve this problem. I would be very grateful.

Thanks in advance.

  #2 (permalink)  
Old July 25th, 2008, 01:17 PM
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

That's exactly how autoincrement fields work in *ALL* databases.

Once a number has been used for a record, it is *NEVER* re-used. Period.

IDENTITY *means* "a unique IDENTITY for the record so long as this table exists".

[Actually, you *can* do a TRUNCATE tablename to remove *all* records and start all over, but that's the only exception.]

In other words, do *NOT* treat an IDENTITY field as a row number. It isn't one. And it never will be.
  #3 (permalink)  
Old August 5th, 2008, 05:50 AM
Authorized User
Join Date: Apr 2008
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts

Hello Everybody,

Is there any alternative to Autogeneration number,with int IDENTITY(1,1) in SQL, it gives the GAP. So any alternative solution for that.
Please do help with coding.. I am using ASP.NET2.0 and SQL Server 2000.

Thanks in advance

  #4 (permalink)  
Old August 5th, 2008, 01:10 PM
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

SQL Server 2005 and 2008 have a ROW_NUMBER() function which is probably what you are looking for. Note that ROW_NUMBER() applies *only* to the results of a SELECT; it doesn't store any permanent value in the DB. This is, again, in keeping with the principles of relational databaases. To wit, there is *NO* implict ordering of records in an RDBMS. Only by using ORDER BY can you enforce a particular ordering and then only when doing a SELECT.

Anyway, SQL Server 2000 doesn't have ROW_NUMBER(), but look here:

But *do* read all the responses in that thread, some of which show why your relying on something like a row number is not good practice.
  #5 (permalink)  
Old August 11th, 2008, 07:10 AM
Registered User
Join Date: Aug 2006
Location: hyderabad, Andhrapradesh, India.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts


Instead of binding data from the original table you can bind data from temp table.

In your stored procedure insert data into #tmp table, this should contain identity.

for example your actual table columns intId(identityColumn),Name,Address

create table like this

create table #tmp
(intId1 INT iDENTITY(1,1),
 intId int,
 Name varchar(50),
 adress varchar(100)

insert into #tmp (intId,Name,Address)
select * from table

select * from #tmp
bind this data to ur grid. IntId should be invisible, for deleting rows you can use this.

I think this will help you.



Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query to find key when auto-increment SandyFeder SQL Server ASP 1 November 21st, 2005 10:49 AM
SQL Server "Service Manager" Auto Start Problem. khalidumer SQL Server 2000 0 May 18th, 2004 09:21 AM
SQL Server Identity field won't increment Ron Howerton VB.NET 2002/2003 Basics 8 April 22nd, 2004 12:14 PM
I have problem connecting to SQL Server 2000 sp3 ctanchan SQL Server 2000 0 September 11th, 2003 07:35 PM

All times are GMT -4. The time now is 10:40 PM.

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