SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 SQL Serve 2008 Does Not Return Identity Value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjivus
Starting Member

16 Posts

Posted - 07/25/2013 :  19:50:05  Show Profile  Reply with Quote
We just upgraded from Windows 2003/SQL2000 to Windows 2008R2/SQL200R2 and we are using a website in classic ASP.
We are running into a problem where SQL server stored procedure does not return identity value after doing an insert.




My Stored Procedure
#########################################

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
	DECLARE @CustId AS INT
	INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
	SET @CustId = @@IDENTITY

	SELECT @CustId

##########################################



My Table
#########################################

CREATE TABLE [dbo].[myCustomers](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
 CONSTRAINT [PK_myCustomers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

#########################################


My ASP Code
#########################################

<%
strConn = "Provider=SQLOLEDB;SERVER=mySERVER;Uid=myUserName;Pwd=myPassWord;Database=myDatabase"

set conn = server.createobject("ADODB.Connection")
conn.open strConn 
If Err.Number <> 0 then
	Response.Write Err.Number & "<br>"
	Response.Write Err.Description & "<br>"
Else
	Response.Write "Conned established<br>"	
End If	

strSQL = "exec myStoredProcedure 'myFirstName', 'MyLastName'"

SET RS  = conn.execute(strSQL)
CustID = RS("CustID")  & "<br>"
Response.Write "CustID = " & CustID 

RS.Close
SET RS = Nothing
conn.close
SET conn = nothing
Response.End

%>

#########################################


My Error Message
#########################################

Conned established
ADODB.Recordset error '800a0cc1' 
Item cannot be found in the collection corresponding to the requested name or ordinal. 
myTest.asp, line 12 

#########################################


Here is what I have tried I have so far:


  • 1. If I comment the INSERT statement in stored procedure and hard code some value to @CustID such as @CustID=1000, it works.

  • 2. If I DO NOT comment INSERT statement in stored procedure and hard code some value to @CustID such as @CustID=1000, it gives the same error as above.

  • 3. Same code with same stored procedure works with Windows 2003/SQL2000.



Please help.


Edited by - sanjivus on 07/25/2013 19:50:28

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/25/2013 :  20:03:52  Show Profile  Reply with Quote
I don't see anything obvious that could break. Can you try the change shown in red?

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
        SET NOCOUNT ON;
	DECLARE @CustId AS INT
	INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
	SET @CustId = @@IDENTITY

	SELECT @CustId
Go to Top of Page

sanjivus
Starting Member

16 Posts

Posted - 07/25/2013 :  20:22:37  Show Profile  Reply with Quote
Appreciate your response. I tried your suggestion but still same error. Here is the final version of my stored procedure:



USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CustId AS INT
	INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
	SET @CustId = @@IDENTITY 
	SELECT @CustId
END
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/25/2013 :  21:42:03  Show Profile  Reply with Quote
is your collation case sensitive ?
try

CustID = RS("CustId")  & "<br>"



KH
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/26/2013 :  02:46:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also.don't use @@IDENTITY because that will return any created identity value in the database, regardless of user.
Use SCOPE_IDENTITY() instead


SET @CustId = SCOPE_IDENTITY()




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 08/16/2013 :  19:11:18  Show Profile  Reply with Quote
Give this a try:
ALTER PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CustId AS INT
	INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
	SET @CustId = SCOPE_IDENTITY()
	SELECT @CustId as CustID
END



=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Edited by - Bustaz Kool on 08/16/2013 19:24:20
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/17/2013 :  02:14:23  Show Profile  Reply with Quote
Have you checked to make sure the insert is actually working, and not getting an error?

I like to use the output clause to capture the identity column value.
ALTER PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CustId table ( CustID int )

	INSERT INTO myCustomers(FirstName,LastName)
	output inserted.CustID 
	into @CustId
	VALUES(@FName,@LName)

	SELECT CustID from @CustId 
END


CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/17/2013 :  05:01:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Either

SELECT @CustId AS CustID

or

CustID = RS(0).Value & "<br>"



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000