Return an unique identifier to an ASP.NET page to send it as a parameter into another stor

Hi !

I have a problem with the unique identifier and don't know how to solve it.

I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.

I tried withSCOPE_IDENTITYbut i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.

Dim

cmdAsNew SqlCommand

cmd.CommandText ="Insertar_Contacto"

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = connect

Thanks!!

Create a parameter of type OUTPUT in your stored proc, assign the value of SCOPE_IDENTITY() to it after your insert, create the same parameter on your application layer, set its direction to OUTPU and retrieve the value. Search my posts here for some sample code as I posted some code for someone, in the last few days.|||

Have a look at

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.usp_Test_Insert
(
@.IsAdmin bit,
@.JobTitle nvarchar (50),
@.Name nvarchar (50),
@.DateCreated datetime,
@.RETURN INT OUTPUT,
@.IDENTITY INT OUTPUT
) AS
-- Purpose:
-- Insert record into Test table
-- Parameters:
-- IsAdmin -
-- JobTitle -
-- Name -
-- DateCreated -
-- RETURN - Zero or Error Code
-- IDENTITY - Identity of inserted row
-- History:
-- 11Jan2006 ACERXP\Administrator Original coding
SET NOCOUNT ON
INSERT INTO Test( IsAdmin, JobTitle, Name, DateCreated)
VALUES (
@.IsAdmin,
@.JobTitle,
@.Name,
@.DateCreated)
SELECT @.RETURN = @.@.error, @.IDENTITY = SCOPE_IDENTITY()
RETURN
----- this is the end ------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Retrieving Identity after insert

Hey,

I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @.MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems.

Thanks,
Lang

hi,

can you try using @.@.Identity please.

morever please put some code what exactly you've done.

regards,

satish.

|||

Scope Identity is safer than @.@.Identity. @.@.Identity could possibly give you the wrong ID back if your table has triggers that also insert records.

Is @.MY_ID being returned as an output parameter or is this something you are simply doing in a stored procedure with no object/class interaction ?

Retrieving constraint information

Hello

I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.


SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'



It returns a row for each constraint which can be easilly stored in an associative array.


Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)


Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.

Pseudo-bit-mask indicating the status. Possible values include:

1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.

Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?

Thanks

-except10nUse INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.|||Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.
Unfortunately I can't use INFORMATION_SCHEMA for reasons that would take too long to explain at the moment. In your past experiences, were you able to achieve what I am trying to do using the standard system tables?