Problem with IDENTITY

Discussion in 'ASP General' started by thoidi, Sep 29, 2004.

  1. thoidi

    thoidi Guest

    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
    thoidi, Sep 29, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Giovanni Bassi
    Replies:
    0
    Views:
    626
    Giovanni Bassi
    Oct 20, 2003
  2. nalbayo
    Replies:
    2
    Views:
    5,477
    Bruce Barker
    Nov 11, 2005
  3. JimLad
    Replies:
    0
    Views:
    434
    JimLad
    Jan 16, 2009
  4. Frederick D'hont
    Replies:
    0
    Views:
    298
    Frederick D'hont
    Jul 25, 2005
  5. Replies:
    6
    Views:
    437
Loading...

Share This Page