Problem with IDENTITY

[thoidi]

Hi Guru,

This is the work that inserting a new request into the tables
tbl_request and tbl_worktime.
The problem is it produced two records to each table. After this asp
executed, it generated two records for table tbl_request and 4 records
for table tbl_worktime. It didn't happen when I ran the SQL Query
Analyzer. My environment is Win 2K, SQL Server2000, IIS 5.1. The
following text is the code and the stored procedure scripts.

<%@ Language="VBScript"%>
<!-- #INCLUDE VIRTUAL="Connection.asp" -->
<%
on Error resume Next
Dim strSQL, CN, RS
strSQL="Declare @Request_ID int Exec sp_insert_tbl_request "
strSQL=strSQL & "12, 1 ,'09/27/2004','222222','John Doe',"
strSQL=strSQL & "'Firefighter','40 Mon
Rotate','STA3','B3','Overtime',15.25,'1',"
strSQL=strSQL & "'Overtime', @Request_ID OUTPUT"
strSQL=strSQL & " SELECT id FROM tbl_request WHERE id = @Request_ID"

set CN = server.createobject("adodb.connection")
CN.open strConn

set RS = server.createobject("adodb.recordset")
RS = CN.execute(strSQL)

'-- Save your ID Value, then insert request_ID to table tbl_worktime
session("u_IDvalue")= RS("id")

if err.Number = 0 then
strSQL="Exec sp_insert_tbl_worktime "
strSQL=strSQL & "'55',6 ,'09/12/2004 23:00','09/13/2004 04:00'"
RS = CN.execute(strSQL)

strSQL="Exec sp_insert_tbl_worktime "
strSQL=strSQL & "'61',6 ,'09/16/2004 18:00','09/16/2004 20:00'"
RS = CN.execute(strSQL)

RS.close
CN.close
Set RS = nothing
set CN = nothing
response.redirect "request_report.asp"
else
RS.close
CN.close
Set RS = nothing
set CN = nothing
response.redirect "error.asp"
end if
%>

CREATE PROCEDURE sp_insert_tbl_request
(
@EventType_ID smallint,
@Party_ID int,
@RequestDate smalldatetime,
@EmployeeNumber varchar(7),
@FullName varchar(100),
@Rank varchar(100),
@Shift varchar(50),
@Station varchar(50),
@Battalion varchar(50),
@RequestDescription varchar(1000),
@Wage smallmoney,
@Invoiceable bit,
@InvoiceDescription varchar(1000),
@Request_ID int OUTPUT
)
AS
INSERT INTO tbl_Request
(
EventType_ID,
Party_ID,
RequestDate,
EmployeeNumber,
FullName,
Rank,
Shift,
Station,
Battalion,
RequestDescription,
Wage,
Invoiceable,
InvoiceDescription
)
VALUES
(
@EventType_ID,
@Party_ID,
@RequestDate,
@EmployeeNumber,
@FullName,
@Rank,
@Shift,
@Station,
@Battalion,
@RequestDescription,
@Wage,
@Invoiceable,
@InvoiceDescription
)
SELECT @Request_ID = SCOPE_IDENTITY()
GO
CREATE PROCEDURE sp_insert_tbl_worktime
(
@Request_ID int,
@WorkTimeType_ID tinyint,
@StartDateTime smalldatetime,
@EndDateTime smalldatetime
)
AS
INSERT INTO tbl_Worktime
(
Request_ID,
WorkTimeType_ID,
StartDateTime,
EndDateTime
)
VALUES
(
@Request_ID,
@WorkTimeType_ID,
@StartDateTime,
@EndDateTime
)
GO
CREATE TABLE [dbo].[tbl_Request] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EventType_ID] [smallint] NOT NULL ,
[Party_ID] [int] NULL ,
[RequestDate] [smalldatetime] NOT NULL ,
[EmployeeNumber] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Rank] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Shift] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Station] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Battalion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[RequestDescription] [varchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Wage] [smallmoney] NULL ,
[Invoiceable] [bit] NOT NULL ,
[InvoiceDescription] [varchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Finalized] [bit] NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_WorkTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Request_ID] [int] NOT NULL ,
[WorkTimeType_ID] [tinyint] NULL ,
[StartDateTime] [smalldatetime] NULL ,
[EndDateTime] [smalldatetime] NULL ,
[Active] [bit] NULL
) ON [PRIMARY]
GO