473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Auto Generate Value

92
Hi

Can anyone tell me, how can i set Identity specification as zero when it gets new entries in table after delete all the rows in d table.

now i m getting entries like, if i delete 2 rows in d table.
after i enter new values. it increment the value as 3,4..... like d.

but i need to enter 1,2,3...... after delete rows in d table

Thanks in Advance
Limno
Apr 18 '08 #1
4 1849
deepuv04
227 Expert 100+
Hi

Can anyone tell me, how can i set Identity specification as zero when it gets new entries in table after delete all the rows in d table.

now i m getting entries like, if i delete 2 rows in d table.
after i enter new values. it increment the value as 3,4..... like d.

but i need to enter 1,2,3...... after delete rows in d table

Thanks in Advance
Limno
Hi,
use the command

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value)

thanks
Apr 18 '08 #2
Limno
92
Hi,
use the command

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value)

thanks
Thank u 4 replying me,
i m new to sql, can u plz explain me more.
how can i do this while inserting records into d table.

i hv 3 fields.

1.code
2.short_acct
3.acct

(1. code) holds the identity specification field.

while inserting these fields. i hv to do the process. ie code starts the value as zero always. after deleted rows
Apr 18 '08 #3
deepuv04
227 Expert 100+
Thank u 4 replying me,
i m new to sql, can u plz explain me more.
how can i do this while inserting records into d table.

i hv 3 fields.

1.code
2.short_acct
3.acct

(1. code) holds the identity specification field.

while inserting these fields. i hv to do the process. ie code starts the value as zero always. after deleted rows
Hi,
After deleting the rows from the table execute the following statement

DBCC CHECKIDENT (Table_Name , RESEED, Seed_Value or Initial value )

Then execute the insert statement

example: assuming the columns in the table as given
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. CREATE TABLE Sample_Table(code INT IDENTITY( 1,1),
  4. short_acct VARCHAR(100),
  5. acct VARCHAR(100) )
  6.  
  7. INSERT INTO [Sample_Table] ([short_acct],[acct])
  8. VALUES ('value1','value1')
  9. INSERT INTO [Sample_Table] ([short_acct],[acct])
  10. VALUES ('value2','value2')
  11. INSERT INTO [Sample_Table] ([short_acct],[acct])
  12. VALUES ('value3','value3')
  13.  
  14. SELECT * FROM [Sample_Table] 
  15.  
  16. DELETE FROM [Sample_Table] 
  17.  
  18. -- Reset the identity value 
  19. DBCC CHECKIDENT ([Sample_Table] , RESEED, 0)
  20.  
  21. SELECT * FROM [Sample_Table] 
  22.  
  23. -- insert new record
  24. INSERT INTO [Sample_Table] ([short_acct],[acct])
  25. VALUES ('value4','value4')
  26.  
  27. -- select to know which value iserted
  28. SELECT * FROM [Sample_Table] 
  29.  
Apr 18 '08 #4
Limno
92
hi deepuv04

Thank u very much.

Itz working..........
Apr 18 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: csomberg | last post by:
SQL 2000 I thought I would throw this out there for some feedback from others. I'd like to know if you feel using MS auto-increment field is a good solution these days or should one grow their...
2
by: SalimShahzad | last post by:
Dear Gurus, i had written following codes to auto generate the next claim no Private Const strC = "GCT/02/J/" Private Sub Command1_Click() Dim stra, stre As String Dim intb, intd As Integer...
6
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I...
0
by: Viorel | last post by:
Working as a beginner with data objects in Visual Studio 2003 and C#, I use the "Generate Dataset" command in order to generate automatically the dataset objects based on data adapters. Generated...
8
by: Bill Rust | last post by:
I've created an "Add Item" wizard for VB.NET 2003 that allows a user to add a specialized class that works with my application framework. In the wizard, the user can select the interfaces they...
5
by: Navillus | last post by:
Hey gang, I have a login form that is empty by default, but can be filled with values from a previous form: <input type=text maxlength="40" size="40" name="user" value="`usr`"> <input...
2
by: x0054 | last post by:
Ok, this is a stupid problem, I admit. I have a scrip that adds records to a table. The records are for photos. So, after adding a record the scrip also uploads a picture from users computer and...
2
by: alexs | last post by:
Chaps, I'm starting to play with db2 V9.1 and am writing a stored procedure to manage accounting records from oiur RADIUS server. I've got an XML aware table with an auto increment primary...
2
by: G | last post by:
Hello Firends I want a sample code for developing an " auto genearte number " and want to store in database. Please if you find any code please send Thanks in Advance G
1
by: Maninder Karir | last post by:
Hi Im stuck AGAIN!!!!!! Sure its something simple but, Ive put together a code to generate an automatic number everytime i open the work book, However I now would rather only generate the...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.