 |
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
|
|
|

July 25th, 2008, 05:23 AM
|
Authorized User
|
|
Join Date: Apr 2008
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

July 25th, 2008, 01:17 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
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.
|

August 5th, 2008, 05:50 AM
|
Authorized User
|
|
Join Date: Apr 2008
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
|

August 5th, 2008, 01:10 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
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:
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.
|

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