SQL server creates 2 records for a single insert SQL query

Hi,

The problem is SQL Server produced two records each time a signle insert statement fired up. After the user clicked the submit button in the asp Request form, it will call the do_insert.asp and I have the source code below. For an example, I assigned the data to all variables for testing purposes. The user submitted his personal info with 2 days that he worked overtime. General speaking, one record goes to the table tbl_request and 2 records to tbl_workTime with carrying the request_ID from tbl_request. When do_insert.asp executed it generated 2 records for table tbl_request and 4 records for table tbl_worktime. It didn't happen when I ran the queries from 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. TIA
============== do_insert.asp begins ===================
<%@ 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
%>
============== do_insert.asp ends here ===================
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

hi

according to the code here it will insert two rows coz u have
given the same names both times

regards


ashish
mcdba,mct,mcse,mcsd

Hi ashish,

Thanks for reply. The second table tbl_worktime has a PK and it's autoNum. In my example, I put the same data for testing, and that's OK.
The problem that I've seen is I can run a real data in the SQL Query analyzer. The output was fine. It's a simple execution line, i.e:
exec sp_insert_tbl_request 12, 1 ,'09/27/2004','222222','John Doe',.. and so on
exec sp_insert_tbl_worktime 111,....on and on
exec sp_insert_tbl_worktime 111,.... something...
and I can run all three commands at the same time. And The output is OK. It shows one record for tbl_request and the table tbl_worktime has two. But, if I run in asp, then I got 2 records in tbl_request and 4 records in tbl_worktime. It seems to run it twice some how? I set AutoNumber for both tables and they're PK.
Do you see my point here. Well, thanks for your time. I don't know what 's wrong with the code? For ASP, I even tried to write :
Set ObjCmd=Server.CreateObject("adodb.command")
with ObjCmd
.ActiveConnection=strConn
.CommandText="sp_insert_tbl_request"
.CommandType=adCmdStoredProc
.Parameters.Append .CreateParameter("ColumnName",DataType,Length,myVariableValue) ....
......
.Execute
end with
Still get the same result, I still get 2 records in table tbl_request



Thank you very much?