|
Subject:
|
Auto Increment Field Problem in SQL Server 2000
|
|
Posted By:
|
yes_no
|
Post Date:
|
7/25/2008 5:23:44 AM
|
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.
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
7/25/2008 1:17:29 PM
|
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.
|
|
Reply By:
|
yes_no
|
Reply Date:
|
8/5/2008 5:50:34 AM
|
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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
8/5/2008 1:10:25 PM
|
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/ShowPost.aspx?PostID=144651&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.
|
|
Reply By:
|
Nehru
|
Reply Date:
|
8/11/2008 7:10:27 AM
|
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
|
