Below is a bunch of information relating to an error I am receiving and I can not figure out why.
It is essentially a very basic table setup, a pretty simple trigger, and a really simple piece of
ASP code. For some reason the ASP code is not actually returning me the information I am looking
for and I don't understand why it won't work. Before I created this trigger that block of ASP code
worked perfectly well. This is why I am confused. Running the same exact query that is on the
first line of the ASP code in SQL Query Analyzer gives me the SCOPE_IDENTITY() value as it should. In ASP
though for some reason this is not the case. I can not figure out why.
In SQL Query Analyzer, I also know that using @@IDENTITY instead of SCOPE_IDENTITY() works as well, but it also does not work in ASP.
What's going on here? How do I fix this and get the id value?
Table ss_stn
------------------
stn_id int primarykey
stn_name varchar(50)
stn_slt int
cnl_id int
Trigger On ss_stn
------------------
CREATE TRIGGER UpdateStationSlots ON [dbo].[ss_stn]
FOR INSERT, UPDATE
AS
-- Create a temporary table for the slot information to be stored
CREATE TABLE #t ( stn_id int, stn_slt int )
-- Declare a variable to use for updating the slots sequentially
DECLARE @SLOT_NUM int
SET @SLOT_NUM = 1
-- Get the id and slot of the inserted/updated record
DECLARE @INS_ID int, @INS_SLT int, @INS_CNL int
SELECT @INS_ID = stn_id, @INS_SLT = stn_slt, @INS_CNL = cnl_id FROM inserted
-- Insert all the information into the temporary table
INSERT INTO #t
SELECT stn_id, stn_slt FROM ss_stn WHERE cnl_id = @INS_CNL
UNION
SELECT inserted.stn_id, inserted.stn_slt FROM inserted WHERE inserted.cnl_id = @INS_CNL
ORDER BY stn_slt ASC
-- Start iterating through temporary table data
DECLARE @tmp_id int, @tmp_slt int
DECLARE curTEMP CURSOR FOR SELECT stn_id, stn_slt from #T
OPEN curTEMP
FETCH NEXT FROM curTEMP INTO @tmp_id, @tmp_slt
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SLOT_NUM = @INS_SLT AND @tmp_id <> @INS_ID SET @SLOT_NUM = @SLOT_NUM + 1
IF @tmp_id <> @INS_ID
BEGIN
UPDATE ss_stn SET stn_slt = @SLOT_NUM WHERE stn_id = @tmp_id
SET @SLOT_NUM = @SLOT_NUM + 1
END
FETCH NEXT FROM curTEMP INTO @tmp_id, @tmp_slt
END
CLOSE curTEMP
DEALLOCATE curTEMP
ASP CODE Test 1
--------------------------
---
set rs = db.execute("insert into ss_stn (stn_name, stn_slt, cnl_id) values ('test', '1', 'test'); select SCOPE_IDENTITY() as id")
response.write rs(0) 'Note: Trying this as rs("id") also fails with the same error below.
ASP CODE Test 2
--------------------------
---
set rs = db.execute("insert into ss_stn (stn_name, stn_slt, cnl_id) values ('test', '1', 'test'); select SCOPE_IDENTITY() as id")
dim trs
set trs = rs.nextrecordset
response.write rs(0) 'Note: Trying this as rs("id") also fails with the same error below.
set trs = nothing
Error
--------------------------
---
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/betatest.asp, line (references the lines above with "response.write rs(0)" on it in either test)
Start Free Trial
