This is my stored proc:
============== SP_INSERT_TBL_REQUEST ===================
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
SET NOCOUNT ON
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()
SET NOCOUNT OFF
GO
============== do_insert.asp begins ===================
Set CN = server.createobject("adodb.connection")
Set CMD = server.createobject("adodb.command")
CN.open strConn
With CMD
.ActiveConnection=strConn
.CommandText="sp_insert_tbl_request"
.CommandType=4
.Parameters.Append .CreateParameter("EventType_ID", 2, 1,
0,session("u_Event"))
.Parameters.Append .CreateParameter("Party_ID", 3, 1, 0,varParty_ID)
.Parameters.Append .CreateParameter("RequestDate", 135, 1, 0,
session("u_DateRequest"))
.Parameters.Append .CreateParameter("EmployeeNumber", 200, 1, 7,
session("u_EmployeeNumber") )
.Parameters.Append .CreateParameter("FullName", 200, 1, 100,
session("u_Name"))
.Parameters.Append .CreateParameter("Rank", 200, 1, 100,
session("u_Rank"))
.Parameters.Append .CreateParameter("Shift", 200, 1, 50,
session("u_Shift"))
.Parameters.Append .CreateParameter("Station", 200, 1,
50,session("u_Station"))
.Parameters.Append .CreateParameter("Battalion", 200, 1, 50,
session("u_Battalion"))
.Parameters.Append .CreateParameter("RequestDescription", 200, 1,
1000, varRequestDesc)
.Parameters.Append .CreateParameter("Wage", 6, 1, 0, varWage )
.Parameters.Append .CreateParameter("Invoiceable", 11, 1, 0,
varInvoiceable)
.Parameters.Append .CreateParameter("InvoiceDescription", 200, 1,
1000, varInvoiceDescription )
set varReturn_ID = .CreateParameter("Request_ID", 3, 2,1)
.Parameters.append varReturn_ID
.Execute
End With
varIDvalue= CInt(varReturn_ID)
'-- Take this varIDValue to insert the table tbl_review as the
following sp_insert_tbl_review
set CMD=nothing
Set CMD=Server.CreateObject("ADODB.Command")
With CMD
.ActiveConnection=strConn
.CommandText="sp_insert_tbl_review"
.CommandType=4
.Parameters.Append .CreateParameter("Request_ID", 3, 1,3, varIDvalue)
.Parameters.Append .CreateParameter("EmployeeNumber", 200, 1,7,
session("u_EmployeeNumber"))
.Parameters.Append .CreateParameter("FullName", 200, 1, 100,
session("u_Name"))
.Execute
End With
set CMD=Nothing
.....
....
...
============== do_insert.asp ends ===================
It inserted the data correctly, but there are two set of records for
each table. I don't know how it created. Each stored proc just ran
once, and two recordsets were created to each table.
In table tbl_request has 2 new records created,
Table tbl_review has 2 new records created as well. Any help would be
appreciated. TIA.