insert data into table with identity column

Question

0.00/5 (No votes)

See more:

hi all,
I have table with following columns :-
userid (int , pk, identity) , username (nvarchar(50))

the following code of procedure :

ALTER PROCEDURE dbo.ManageUsers
(

@username nvarchar(50)
)

AS
	
	

INSERT INTO users (username) Values (@username)

	END
	
	RETURN

I can't insert data to this table with the identity column
but if isidentity option set to false and try to insert userid manually , it worked well

what's problem ??

Posted 15-Jun-13 22:41pm

shms_rony

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

cigwork · Answer 1 · 2013-06-15T23:27:00

Ignoring any possible problems with the connection from the asp.net front-end; the RETURN following the END in the stored procedure will give you a syntax error. So won't run.

If you're using Query Analyser/SQL Server Management Studio then highlighting a block of SQL and hitting <ctrl-f5> will give you a syntax check on that block. Can save a lot of time.

Just tried this and it works fine on SQL2005....

SQL

create table users
(
  ID int identity
     constraint PK_users primary key (ID),
  [name] nvarchar(50) not null
     constraint DF_username default('')
)

insert into [users] ([name]) values('sheila')
select * from users

create procedure adduser( @name nvarchar(50) )
as
begin
insert into [users] ([name]) values(@name)
return
end

exec adduser 'jim'
select * from users

_Amy · Answer 2 · 2013-06-15T23:48:00

Solution 2

Use SET IDENTITY_INSERT[^] OFF/ON to insert values in identity column.
Try this:

SQL

ALTER PROCEDURE dbo.ManageUsers
(
    @username nvarchar(50),
    @userid int
)
AS
    SET IDENTITY_INSERT users ON 
    INSERT INTO users (userid, username) Values (@userid, @username)
    SET IDENTITY_INSERT users OFF
END
--RETURN

You can refer

--Amit

Posted 15-Jun-13 23:48pm

_Amy

v2

Comments

shms_rony 16-Jun-13 6:12am

I want automatic inserted values in the identity column

Chintan Desai1988 · Answer 3 · 2013-06-18T00:50:00

For that when you create user table just assign the primary key of id and set identity seed and increment as a "Yes" and after that create procedure as like:

Create procedure InsertUSerDetails
(
@username nvarchar(50)
)
as
begin
Insert into users values(@username);
end

---Its work fine if you want to try

shms_rony · Answer 4 · 2013-06-16T00:45:00

Solution 3

Thanks Dears, It works for me :)

Posted 16-Jun-13 0:45am