how to increment pk entry in a table

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

Express Edition and Compact Edition (2005)

how to increment pk entry in a table

Author

Topic

mkkb917
Starting Member

14 Posts

Posted - 2013-08-04 : 05:12:17

hi
i am new on this forum and new bie in knowledge so i come here to solve my problems

i am using sql server express edition with VB.net
and facing a problem

i have a table in my db with spec

TABLE[ SrNo(PK with auto increment), Name, Sex, Address, PhoneNo]

and i am trying to insert a record into name, sex, address, phoneNo columns using the sql query in my vb program
my query is

INSERT INTO TABLE(Name, Sex, Address, PhoneNo) values(@Name, @Sex, @Address, @PhoneNo)

and i am getting the error that PK cannot be null and transaction is canceled

now plz tell me how can iwrite a query that increment the PK attribute each time when ever the button is pressed r when ever a new record will be inserted into DB ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-04 : 05:31:04

You can assign identity property to the SrNo column. To do this, right click on the table name in SSMS object explorer, select Design. Then, click the SrNo column name, and in the lower panel, change Identity property to 1 and save.

When you do this, internally SQL Server is dropping and recreating the table. So if you have lot of data already in the table, this can be resource intensive.

Read up on identity property to see if this fits your needs.

mkkb917
Starting Member

14 Posts

Posted - 2013-08-04 : 10:48:40

i think (sorry) i did not explain my porblem well
the given step that u tell me has already been implemented and my database is designed
i need a query that i will use in my program that able me to insert new record into these columns.
when i try to insert a new record in these columns using the given mentioined query i received the error of Primary Key

PLZ TELL ME A QUERY THAT ENABLE A NEW RECORD INSERTION AND AUTO INCREMENT the PRIMARY KEY EACH TIME WHEN IT IS INVOKED BY USER IN MY VB PROGRAM

mkkb917
Starting Member

14 Posts

Posted - 2013-08-04 : 10:50:46

my given INSERT query did not increment the PRIMARY KEY
i need a method or sql query that also increment the PK and also insert the data each time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-04 : 11:10:25

quote:
Originally posted by mkkb917

my given INSERT query did not increment the PRIMARY KEY
i need a method or sql query that also increment the PK and also insert the data each time

looking at your table definition you say SrNo is autoincrementing column? isnt it correct?
if yes, it will generate values automatically and you wont get the posted error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkkb917
Starting Member

14 Posts

Posted - 2013-08-04 : 13:07:40

yes it is autoincrementing column
but still i get the error
the error is displaying

"Cannot insert the value NULL into column SrNo. Column does not allow NULL.
Insert fail.
transaction canceled"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-04 : 20:26:35

If it is auto increment i.e., if the column has identity property set, you should not see that error. Can you do the following? In SSMS object explorer, right-click on the table name and script table as -> New Query editor window. That will script the table. Copy the results and post it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 02:23:57

quote:
Originally posted by mkkb917

yes it is autoincrementing column
but still i get the error
the error is displaying

"Cannot insert the value NULL into column SrNo. Column does not allow NULL.
Insert fail.
transaction canceled"

what does this return for you?


SELECT id_identity
from sys.columns 
where COL_NAME(object_id,column_id)= 'SrNo'
AND OBJECT_NAME(object_id) = 'TABLE'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkkb917
Starting Member

14 Posts

Posted - 2013-08-05 : 03:16:07

sir
your given query return me an error and the error is
Error Message:- Invalid column name id_identity

i did not understand what does it mean
but i try to insert the record manually into the table by using the server explore and displaying the table data
server explorer ->Database->table->show table data

and there i try to insert i receive the following error

Error Message:The insert statement conflict with forign key constraint 'Table1_Table2_key'. The conflict occur in database (database path)table Table1.dbo, column PhoneNo
the statement has been terminated

i am using PhoneNo column in first table as a primary key
and in this table it is forign key

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 04:20:51

quote:
Originally posted by mkkb917

sir
your given query return me an error and the error is
Error Message:- Invalid column name id_identity

i did not understand what does it mean
but i try to insert the record manually into the table by using the server explore and displaying the table data
server explorer ->Database->table->show table data

and there i try to insert i receive the following error

Error Message:The insert statement conflict with forign key constraint 'Table1_Table2_key'. The conflict occur in database (database path)table Table1.dbo, column PhoneNo
the statement has been terminated

i am using PhoneNo column in first table as a primary key
and in this table it is forign key

tht was a typo

it should be


SELECT is_identity
from sys.columns 
where COL_NAME(object_id,column_id)= 'SrNo'
AND OBJECT_NAME(object_id) = 'TABLE'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 05:14:02

If SrNo is autoincementing, you should NOT INSERT NULL value into it.

INSERT dbo.Table1 (Name, Sex, Address, PhoneNo) VALUES ('SwePeso', 'M', 'Sweden', 'Cell')
SrNo will be automatically assigned.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 05:19:53

quote:
Originally posted by SwePeso

If SrNo is autoincementing, you should NOT INSERT NULL value into it.

INSERT dbo.Table1 (Name, Sex, Address, PhoneNo) VALUES ('SwePeso', 'M', 'Sweden', 'Cell')
SrNo will be automatically assigned.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

But OPs initially posted insert didnt have SrNo column at all in the column list so I doubt whether an explicit NULL is being passed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkkb917
Starting Member

14 Posts

Posted - 2013-08-05 : 07:26:51

the same error arises
when i used your query
i come to know also that my database is not displaying the new data that is entered in the master table TABLE1
and only displaying the old data
i unfortunately edit the database tables and relation diagram using server explorer in vb.net
so i think these error are generated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 07:35:27

quote:
Originally posted by mkkb917

the same error arises
when i used your query
i come to know also that my database is not displaying the new data that is entered in the master table TABLE1
and only displaying the old data
i unfortunately edit the database tables and relation diagram using server explorer in vb.net
so i think these error are generated

First try the insert query using SQL Server management studio quyery editor and see if it succeeds.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkkb917
Starting Member

14 Posts

Posted - 2013-08-05 : 11:10:48

by using query editor the query works fine and record inserted successfully
but why it is not working in my program with same query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 11:22:54

Do you have some kind of object modeler? Some kind of framework?
Something that tries to insert into the identity column.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mkkb917
Starting Member

14 Posts

Posted - 2013-08-05 : 14:05:23

no i dont have any object model that use to inset into identity column
frameworks means?
i did not fully understand what exactly u want to ask
framwork version : i am using microsoft dot net framework 4.0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 14:12:28

I mean, are you using some framework such as Entity Framework to build your code for you to insert data?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mkkb917
Starting Member

14 Posts

Posted - 2013-08-05 : 15:47:00

friends
i resolve the problem by deleting the given table and recreate it with another name and use the same columns names and properties
now every thing is working fine and my data is being saved by user successfully and efficiently

i once edit the table definition and change the attributes values
so after that the problem arise and now by deleting and creating with another name it is fine

thanks alot for your help and instantly replying to me
thanks again .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources