Mistake for making a not void field empty provokes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mistake for making a not void field empty provokes

Author

Topic

jpiaya
Starting Member

3 Posts

Posted - 2008-07-10 : 03:09:56

Hello,

I have a table with a field that is primary key and identity autoincrementable and has also another field that cannot be void,
On having inserted a new record (from the own(proper) Sql Server) and to make empty the field that cannot be void shows me a mistake, then I introduce the value in the above mentioned field and it turns out that the number of the field identity has increased one more number, therefore, a number has skipped.

How can I solve this problem?

It can only very badly that the client who uses the computer application sees hollows between the numbers.

Thank you in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-10 : 03:22:11

see http://msdn.microsoft.com/en-us/library/ms176057.aspx?n=0

KH
[spoiler]Time is always against us[/spoiler]

ssnaik84
Starting Member

15 Posts

Posted - 2008-07-10 : 04:24:14

yes.. either use..

DBCC CHECKIDENT ([tablename], RESEED, [newID]) -- newval is int

or

set identity_insert [tablename] on;
insert...
set identity_insert [tablename] off;

jpiaya
Starting Member

3 Posts

Posted - 2008-07-10 : 11:00:36

Thank you for the help but it me is not sufficient,

My problem is that if I insert a record of a table that contains a field that cannot be void and for mistake I go on to the following record gives me a mistake that warns me that such a field cannot be void, but later on having inserted the value to the above mentioned field it has put a value in the field GO that it does not correspond.

Example
Identifying - description - date
1 football 01/01/2008
empty basketball forgot me to put the date

ON HAVING GONE ON TO THE FOLLOWING RECORD IT GIVES ME A MISTAKE, THEN I GO OUT TO THE COUNTRY AND INTRODUCE THE DATE

3 basketball 12/12/2008

It is to say, me the value has put 3 instead of the value 2.

Has this solution??

Thank you.

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 11:32:40

in your sp
begin

set identity_insert Table1 on

insert into Table1 (ident, value1, value2, etc etc...)
select (select max(ident) + 1 from Table1), @value1, @value2 etc etc)

set identity_insert Table1 off
end

No matter if it fails, the next success will always insert the next value and no gaps!

>>>>> THE Whammy Bar String Trasher <<<<<

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 11:33:25

... sopry, omit last bracket.

>>>>> THE Whammy Bar String Trasher <<<<<

jpiaya
Starting Member

3 Posts

Posted - 2008-07-10 : 15:17:34

I am working with sql server 2005 express

This happens to me when I insert a record from the Management Studio, when I select the table and say To open.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources