-
Nov 3rd, 2015, 08:16 AM
#1
Thread Starter
New Member
How to reset ID of Database table
Dear Friend,
I want to reset ID of Table database after deleted all rows. I want to make a button at Main form to reset Database table ID. Please help me
I see many video and guide but they only make sql query and click execute.
Thank you
-
Nov 3rd, 2015, 10:22 AM
#2
Re: How to reset ID of Database table
Why? there's not real reason to do so. Plus the only way I've found to reset it is to drop the table and re-create it.
Even Identity Insert doesn't allow for a reset
Code:
create table MyTest (
ID integer identity(1,1),
Name nvarchar(50)
)
insert into MyTest (Name) values ('TechGnome')
insert into MyTest (Name) values ('J_Group')
insert into MyTest (Name) values ('Sitten')
insert into MyTest (Name) values ('Shaggy')
insert into MyTest (Name) values ('dday9')
go
print 'Striaght fresh insert'
select * from MyTest
delete from MyTest
go
print 'Turn on Identity_Insert on the table'
set identity_insert MyTest on
insert into MyTest (ID, Name) values (1, 'TechGnome')
set identity_insert MyTest off
print 'Identity Insert is off... resuming inserts'
insert into MyTest (Name) values ('J_Group')
insert into MyTest (Name) values ('Sitten')
insert into MyTest (Name) values ('Shaggy')
insert into MyTest (Name) values ('dday9')
go
select * from MyTest
print 'Drop the table'
drop table MyTest
go
print 'Re-create the table'
create table MyTest (
ID integer identity(1,1),
Name nvarchar(50)
)
print 'New inserts'
insert into MyTest (Name) values ('TechGnome')
insert into MyTest (Name) values ('J_Group')
insert into MyTest (Name) values ('Sitten')
insert into MyTest (Name) values ('Shaggy')
insert into MyTest (Name) values ('dday9')
go
select * from MyTest
drop table MyTest
go
Produces the following output:
Code:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Striaght fresh insert
ID Name
----------- --------------------------------------------------
1 TechGnome
2 J_Group
3 Sitten
4 Shaggy
5 dday9
(5 row(s) affected)
(5 row(s) affected)
Turn on Identity_Insert on the table
(1 row(s) affected)
Identity Insert is off... resuming inserts
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
ID Name
----------- --------------------------------------------------
1 TechGnome
6 J_Group
7 Sitten
8 Shaggy
9 dday9
(5 row(s) affected)
Drop the table
Re-create the table
New inserts
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
ID Name
----------- --------------------------------------------------
1 TechGnome
2 J_Group
3 Sitten
4 Shaggy
5 dday9
(5 row(s) affected)
You can see that the only way I got it to re-seed the id field was to drop it and re-create it.
It looks like there is a DBCC CHECKIDENT https://msdn.microsoft.com/en-us/library/ms176057.aspx that will reset it... but it's an administrative call that could have side effects and shouldn't be called by a client system.
-tg
-
Nov 3rd, 2015, 10:33 AM
#3
Thread Starter
New Member
Re: How to reset ID of Database table
Thank for reply,
I understand you made SQL Query. But I dont know how to call SQL query to design code (sorry I am a beginer)
Ex: I need make a button name "RESET ID" at form design and reset ID of table in database whenever click "RESET ID"
How to do that?
Thank you
-
Nov 3rd, 2015, 11:06 AM
#4
Re: How to reset ID of Database table
Originally Posted by J_Group
...(sorry I am a beginer)
... and reset ID of table in database whenever click "RESET ID"
...
Since you are a beginner, it is probably more important that you do not do this.
Do you understand what it means to drop a table?
"Ok, my response to that is pending a Google search" - Bucky Katt.
"There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
"Before you can 'think outside the box' you need to understand where the box is."
-
Nov 3rd, 2015, 11:58 AM
#5
Re: How to reset ID of Database table
I'd like to know what you expect to accomplish by resetting the ID. After all, I see no good reason to do this and plenty of reasons not to. Therefore, since you claim to be a beginner, I suspect that there is a better way to achieve your goal than by resetting the ID. If you explain what the goal is, we could likely suggest an alternative.
My usual boring signature: Nothing
-
Nov 3rd, 2015, 12:35 PM
#6
Thread Starter
New Member
Re: How to reset ID of Database table
Attached is my program main design.
I want to do :
click scan : delete all information of table database
Reset ID of Table database
-
Nov 3rd, 2015, 01:09 PM
#7
Re: How to reset ID of Database table
So you want to TRUNCATE the table and reset the IDENTITY value back to start at 1 with the next insert - is that correct?
Is this MS SQL server?
-
Nov 3rd, 2015, 01:09 PM
#8
Thread Starter
New Member
Re: How to reset ID of Database table
I did it, Thank for your time
-
Nov 3rd, 2015, 01:09 PM
#9
Re: How to reset ID of Database table
People have been telling you this is probably a bad idea and asked why you think you should take this approach. You have not answered their question. It's going to be hard to help you, if your not willing to answer questions.
-
Nov 3rd, 2015, 01:15 PM
#10
Re: How to reset ID of Database table
Originally Posted by techgnome
It does not require admin - just that you be the owner of the schema
Caller must own the schema that contains the table, or...
and if the goal is to have a RESET button on a form that TRUNCATE's and reset's the identity value it's fine for the client app to use.
It's actually suggested in the description of what it is used for
You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.
I didn't see much about warnings about it's use.
I can see issues in a large multi-user shared environment with a casual button like this - issues that just call for some level of control.
-
Nov 3rd, 2015, 01:18 PM
#11
Thread Starter
New Member
Re: How to reset ID of Database table
I was Use Trigger Event when delete. So I done! Thank for your help
-
Nov 3rd, 2015, 01:20 PM
#12
Re: How to reset ID of Database table
Originally Posted by J_Group
I was Use Trigger Event when delete. So I done! Thank for your help
Must be a bit of a language barrier here as what you just said makes little sense and certainly does not seem related to a RESET of an ID.
Did you mean UPDATE of an ID - as in another record?
If that was the case we certainly went off in a very different direction in regard to answers!
-
Nov 3rd, 2015, 01:27 PM
#13
Thread Starter
New Member
Re: How to reset ID of Database table
below is TRIGGER I was made on table database
Code:
CREATE TRIGGER [Trigger]
ON [dbo].[info]
FOR DELETE
AS
BEGIN
DBCC CHECKIDENT ('dbo.info', RESEED, 0)
END
-
Nov 3rd, 2015, 01:38 PM
#14
Re: How to reset ID of Database table
Why would deleting one record cause you to reset the whole tables id?
-
Nov 3rd, 2015, 01:42 PM
#15
Thread Starter
New Member
Re: How to reset ID of Database table
Sir,
My software need empty table before read systems information and insert to database.
-
Nov 3rd, 2015, 01:58 PM
#16
Re: How to reset ID of Database table
Let's take a step back, and take a deep breath.
You have answered, many times, what you want to do. What you aren't answering is why you would want to do it. The reason so many people are asking is not because they are picky, but because this is a very strange thing to do, and you may think you need to do it but not actually need to do it.
Usually you design a table to have at least one column where the value for every row is unique. We call this a "primary key", and it's important to how the database optimizes itself. Sometimes, the primary key is a column that you want to provide, like a customer ID number. Other times, you don't have a value in your data that makes sense for a primary key. In those cases, an auto-incrementing number makes sense.
Because that column isn't really part of your data, and only exists to help you distinguish between certain rows, it's very rare that you care what the specific value of the autoincrementing number is. Even if the table is empty, most applications don't care if the number starts at 0, 100, 10,000, or any other number. So without any other context, trying to reset the auto-incrementing number is a waste of time. Nothing in most programs cares about what the next number will be. And often, when code does care about the number, it has many mistakes.
So we want to hear why you think you need to reset the ID. What are you doing with the ID that cares about its value? It might be a bad idea, and we could help you get around it. Or, it could turn out that it doesn't matter, and if we decide that we can help you not waste your time.
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Nov 3rd, 2015, 02:05 PM
#17
Re: How to reset ID of Database table
From your screenshot, I don't think you should delete the table ID at all. You showed a single record with some hardware information in it. There are two possibilities here. The first is that there is ONLY a single record, in which case you REALLY don't want the ID field to be auto-increment, because that causes the trouble that caused you to write this post. When I want a certain record to have a certain ID (which is often the case for tables that have only one record), I just remove the auto-increment so that I can set the ID to whatever I want it to be.
The other alternative is that there are multiple records and you want the primary keys to have some meaning. Sometimes it is necessary or useful for the primary key field to have meaning, but there's a school of thought that holds that the primary key should NEVER hold any meaning and should be just a key. In such a case, resetting has no value whatsoever. You appear to be using some kind of hybrid, where you want the primary key to have meaning AND to have specific values. How about just creating a primary key field that is an autoincrement, and which you don't care about, and adding the ID field as just an integer field. You could put in whatever data you wanted it to have and wouldn't have to worry about it being incremented or resetting it.
My usual boring signature: Nothing
-
Nov 3rd, 2015, 02:14 PM
#18
Re: How to reset ID of Database table
Originally Posted by szlamany
...
I didn't see much about warnings about it's use.
I can see issues in a large multi-user shared environment with a casual button like this - issues that just call for some level of control.
I'm not sure documentation on a command to do something like this would provide a 'warning' per se: the warning is inherently stated 'it resets the ID'. Seems like its the same as resetting your car odometer. It's how it's used which determines the level of 'warning' that might be required.
I think most people are questioning why you'd want to do it, because we consider all the systems we have worked on and doing such a thing would be a big Bad Idea (TM).
I'll also add that you are one of the better experts on databases, so are in a better position to recommend or question what can be done vs. what should be done.
Last edited by SJWhiteley; Nov 3rd, 2015 at 02:25 PM.
"Ok, my response to that is pending a Google search" - Bucky Katt.
"There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk.
"Before you can 'think outside the box' you need to understand where the box is."
-
Nov 3rd, 2015, 02:27 PM
#19
Thread Starter
New Member
Re: How to reset ID of Database table
Sir,
I just start learn VB by myself around 1 week. So I really dont understand much.
I need ID to update exactly device at other window.
like this
-
Nov 3rd, 2015, 05:24 PM
#20
Re: How to reset ID of Database table
In that case, you don't need to reset the ID at all. All you need is for the second form to know what the ID is. If that second form is intended to update something in the table, the ID simply identifies the item that needs to be updated. Therefore, the ID shouldn't ever change, but it doesn't matter what the ID actually is, so resetting the ID isn't necessary.
My usual boring signature: Nothing
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|