Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problem with IDENTITY

Reply
Thread Tools

Problem with IDENTITY

 
 
thoidi
Guest
Posts: n/a
 
      09-29-2004
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP.NET 2.0 Impersonation of fixed identity - truncation of identity JimLad ASP .Net 0 01-16-2009 10:42 AM
HttpContext.Current.User.Identity.Name AND Context.User.Identity.Name; nalbayo ASP .Net 2 11-11-2005 11:12 PM
Difference between System.Web.HttpContext.Current.User.Identity.Name and System.Threading.Thread.CurrentPrincipal.Identity.Name jeremy.rice@alkermes.com ASP .Net Security 5 11-08-2005 05:25 PM
Issue with Identity Impersonation and user identity used passed for trusted SQL connection. Frederick D'hont ASP .Net Security 0 07-25-2005 02:41 PM
Difference between HttpContext.Current.User.Identity and identity Impersonation Giovanni Bassi ASP .Net 0 10-20-2003 02:25 PM



Advertisments