SCOPE_INDENTITY() Not Returning

[vassone]

Dear All,

My newly insert GUID is not return from a store procedure. I turned this
around and around and can't understand why. The records are inserted but no
GUID is returned.

I use (newid()) in the table to generate a GUID

**************Store Procedure ***********************
CREATE PROCEDURE heasvase.[usp_insert_address]
@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id [int] OUTPUT
AS
INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])
VALUES
(
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)

SELECT @addr_id = scope_identity()
GO

***************** ASP ***********************
'Set connection and command properties
set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")

objConn.Open "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist
Security Info=False;User ID=iusr_abc; Initial Catalog=Cat_name; Data
Source=SQLSER7"
objComm.ActiveConnection = objConn
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "heasvase.usp_insert_address"
'Set parameters
set value1 = objComm.CreateParameter("@val1", adChar, adParamInput, 20 ,
val1)
set value2 = objComm.CreateParameter("@val2", adChar, adParamInput, 20 ,
val2)
set value3 = objComm.CreateParameter("@val3", adChar, adParamInput, 20 ,
val3)
set value4 = objComm.CreateParameter("@val4", adChar, adParamInput, 20 ,
val4)
set value5 = objComm.CreateParameter("@val5", adChar, adParamInput, 20 ,
val5)
set value6 = objComm.CreateParameter("@val6", adChar, adParamInput, 20 ,
val6)
set value7 = objComm.CreateParameter("@val7", adChar, adParamInput, 20 ,
val7)
set value8 = objComm.CreateParameter("@addr_id", adInteger, adParamOutput )
objComm.Parameters.Append(value1)
objComm.Parameters.Append(value2)
objComm.Parameters.Append(value3)
objComm.Parameters.Append(value4)
objComm.Parameters.Append(value5)
objComm.Parameters.Append(value6)
objComm.Parameters.Append(value7)
objComm.Parameters.Append(value8)

'Run Command and tell ADO no records only potput params 'adExecuteNoRecords'
objComm.Execute , , adExecuteNoRecords

newId = objComm.Parameters.Item("@addr_id")

response.write("Here ->" & newId)

'Cleanup resources
Set objComm = Nothing
Any help would be greatly appreciated...

[Simon Hayes]

You seem to be confusing two things - uniqueidentifer and identity.
NEWID() generates a new uniqueidentifier value; SCOPE_IDENTITY()
returns the last value generated by an IDENTITY column, which is
usually an integer.

I'm guessing (since you haven't provided a CREATE TABLE statement) that
you're using NEWID() as a default on a column? If so, there is no
function to retrieve the new value - typically you would use NEWID() in
your proc to generate the value, then INSERT it; you can then return
the new value as an output parameter (of data type uniqueidentifier,
not integer).

If this isn't helpful, or my guess is wrong, I suggest you post a
CREATE TABLE script for your table, so that it's clear what data types,
constraints etc you have. You should also clarify what you expect to
get back from the procedure.

Simon

[vassone]

Hi Simon,

I've taken your advice and changed the insert SP as below but I'm still not
getting my GUID back?

Any thoughts..

CREATE PROCEDURE heasvase.[usp_insert_address]

@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id [char] OUTPUT

AS
INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])
VALUES
(
NEWID(),
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)

SET @addr_id = scope_identity()
GO


"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

You seem to be confusing two things - uniqueidentifer and identity.
NEWID() generates a new uniqueidentifier value; SCOPE_IDENTITY()
returns the last value generated by an IDENTITY column, which is
usually an integer.

I'm guessing (since you haven't provided a CREATE TABLE statement) that
you're using NEWID() as a default on a column? If so, there is no
function to retrieve the new value - typically you would use NEWID() in
your proc to generate the value, then INSERT it; you can then return
the new value as an output parameter (of data type uniqueidentifier,
not integer).

If this isn't helpful, or my guess is wrong, I suggest you post a
CREATE TABLE script for your table, so that it's clear what data types,
constraints etc you have. You should also clarify what you expect to
get back from the procedure.

Simon

[Simon Hayes]

Try this:

CREATE PROCEDURE heasvase.[usp_insert_address]
@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id uniqueidentifier OUTPUT
AS

set @addr_id = newid()

INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])

VALUES
(
@addr_id,
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)
GO

As per my previous post, SCOPE_IDENTITY() has nothing to do with GUIDs
and NEWID(). An IDENTITY column is an auto-incrementing numeric value,
which is usually implemented as an integer, and SCOPE_IDENTITY()
returns the last identity value generated in the current scope.

NEWID() on the other hand generates a binary GUID value of data type
uniqueidentifier. See "IDENTITY (Property)", SCOPE_IDENTITY(),
uniqueidentifier, NEWID() and CREATE TABLE in Books Online for more
information.

By the way, char with no length defaults to char(1), so your @addr_id
parameter wouldn't work correctly. You can use CAST() if you want to
return the new GUID as a character type.

Simon

[vassone]

Hi Simon,

Yes I get it now and this work fine. Thank you for sharing your knowledge
and for your patience..

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Try this:

CREATE PROCEDURE heasvase.[usp_insert_address]
@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id uniqueidentifier OUTPUT
AS

set @addr_id = newid()

INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])

VALUES
(
@addr_id,
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)
GO

As per my previous post, SCOPE_IDENTITY() has nothing to do with GUIDs
and NEWID(). An IDENTITY column is an auto-incrementing numeric value,
which is usually implemented as an integer, and SCOPE_IDENTITY()
returns the last identity value generated in the current scope.

NEWID() on the other hand generates a binary GUID value of data type
uniqueidentifier. See "IDENTITY (Property)", SCOPE_IDENTITY(),
uniqueidentifier, NEWID() and CREATE TABLE in Books Online for more
information.

By the way, char with no length defaults to char(1), so your @addr_id
parameter wouldn't work correctly. You can use CAST() if you want to
return the new GUID as a character type.

Simon