problem in defining a variable as scope_identity in sql server 2008

Question

0.00/5 (No votes)

See more:

i m facing problem in declaring my scalar variable as scope identity. what is wrong with this Stored procedure.

ALTER procedure [dbo].[spuserdetails]
@Name varchar(50),
@DOB datetime,
@Age int=null,
@Address1 varchar(70),
@phone int,
@Country varchar(50) = null,
@States Varchar(50) = null,
@City varchar(50) = null,
@E_ID int Out
  set @E_ID =  SCOPE_IDENTITY()
 As 
Begin
insert into userdetails(Name,DOB,Addr,phn,Country,States,City )
values (@Name,@DOB , @Address1 , @phone , @Country, @States, @City )
select E_ID from userdetails
end

what i want to do is when user enters any information into the database from my asp.net web app.
the autogeneted identity column here E_ID popup on my web application screen that "Congrats your Empid is <that identity="" col.="" value="">

Posted 27-Jun-13 23:41pm

Rambo_Raja

Add a Solution

Comments

RedDk 28-Jun-13 12:47pm

RedDK again,

wrt => http://www.codeproject.com/Questions/612588/problem-in-automatically-generating-id-when-insert

Ahem ... this is technically a repost, yes? In the future, use the "Improve question" to modify any Original (question). Or if there's something a commenter or solution proposer needs to know that might be new/fresh/relevant.

I know RS'll get this message because I'm addressing him.

2 solutions

Solution 2

Hi,
I have corrected the Sp.. I hope it works.

A

SQL

ALTER procedure [dbo].[spuserdetails]
@Name varchar(50),
@DOB datetime,
@Age int=null,
@Address1 varchar(70),
@phone int,
@Country varchar(50) = null,
@States Varchar(50) = null,
@City varchar(50) = null

 As 
Begin
insert into userdetails(Name,DOB,Addr,phn,Country,States,City )
values (@Name,@DOB , @Address1 , @phone , @Country, @States, @City )

--set @E_ID =  SCOPE_IDENTITY()-- set @E_ID =  @@IDENTITY
select SCOPE_IDENTITY()

end

Posted 27-Jun-13 23:45pm

shek124

Updated 28-Jun-13 1:12am

v5

Comments

Rambo_Raja 28-Jun-13 5:53am

thnx...

Rambo_Raja 28-Jun-13 5:59am

hi...it is exec properly but now nothing is inserting in my database..whats wrong with it?

Nirav Prabtani 28-Jun-13 6:02am

Just check all the SP input variable fields it must be same as they had passed from codebehind...:)

Rambo_Raja 28-Jun-13 6:05am

i have not changed anything just maked alteration according to u. it executed succesfull but after several insert trails from my web app database is still empty...before this alteration it was working fine,,what went wrong?

Nirav Prabtani 28-Jun-13 6:07am

have you checked by break point where is the problame????

Rambo_Raja 28-Jun-13 6:12am

my update function which is meant for (insert,update,del.) throws exception that:"Procedure or function 'spuserdetails' expects parameter '@E_ID', which was not supplied."

Rambo_Raja 28-Jun-13 6:26am

hey i think i have not told u that E_ID is Identity column ..it generates automatically..i cant pass it .

shek124 28-Jun-13 7:13am

solution is updated.. Pls check.

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)

**Nirav Prabtani** · Accepted Answer · 2013-06-27T23:43:00

You can declare it by this way....:)

SQL

INSERT INTO [dbo].[shopw_users]
            ([generalids], [username], [email], [password], [allowedips], [realname], [initials], [salesperson], [ccaccess], [timemade], [defaultshop], [dailystat])
            VALUES
            (@generalids, @username, @email, @password, @allowedips, @realname, @initials, @salesperson, @ccaccess, @timemade, @defaultshop, @dailystat)

        SET @Output = CAST(@@IDENTITY AS [nvarchar] (max))