Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Professional
|
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 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 July 25th, 2008, 05:23 AM
Authorized User
 
Join Date: Apr 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto Increment Field Problem in SQL Server 2000

Hi,

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.


 
Old July 25th, 2008, 01:17 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old August 5th, 2008, 05:50 AM
Authorized User
 
Join Date: Apr 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old August 5th, 2008, 01:10 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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:
http://forums.microsoft.com/MSDN/Sho...44651&SiteID=1

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.
 
Old August 11th, 2008, 07:10 AM
Registered User
 
Join Date: Aug 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

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.

Regards,
Nehru






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





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