473,581 Members | 3,234 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with IDENTITY

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="VBScr ipt"%>
<!-- #INCLUDE VIRTUAL="Connec tion.asp" -->
<%
on Error resume Next
Dim strSQL, CN, RS
strSQL="Declare @Request_ID int Exec sp_insert_tbl_r equest "
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.createob ject("adodb.con nection")
CN.open strConn

set RS = server.createob ject("adodb.rec ordset")
RS = CN.execute(strS QL)

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

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

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

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

CREATE PROCEDURE sp_insert_tbl_r equest
(
@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),
@RequestDescrip tion varchar(1000),
@Wage smallmoney,
@Invoiceable bit,
@InvoiceDescrip tion varchar(1000),
@Request_ID int OUTPUT
)
AS
INSERT INTO tbl_Request
(
EventType_ID,
Party_ID,
RequestDate,
EmployeeNumber,
FullName,
Rank,
Shift,
Station,
Battalion,
RequestDescript ion,
Wage,
Invoiceable,
InvoiceDescript ion
)
VALUES
(
@EventType_ID,
@Party_ID,
@RequestDate,
@EmployeeNumber ,
@FullName,
@Rank,
@Shift,
@Station,
@Battalion,
@RequestDescrip tion,
@Wage,
@Invoiceable,
@InvoiceDescrip tion
)
SELECT @Request_ID = SCOPE_IDENTITY( )
GO
CREATE PROCEDURE sp_insert_tbl_w orktime
(
@Request_ID int,
@WorkTimeType_I D tinyint,
@StartDateTime smalldatetime,
@EndDateTime smalldatetime
)
AS
INSERT INTO tbl_Worktime
(
Request_ID,
WorkTimeType_ID ,
StartDateTime,
EndDateTime
)
VALUES
(
@Request_ID,
@WorkTimeType_I D,
@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_Gene ral_CP1_CI_AS
NOT NULL ,
[FullName] [varchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Rank] [varchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[Shift] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL
,
[Station] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Battalion] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[RequestDescript ion] [varchar] (1000) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Wage] [smallmoney] NULL ,
[Invoiceable] [bit] NOT NULL ,
[InvoiceDescript ion] [varchar] (1000) COLLATE
SQL_Latin1_Gene ral_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
Jul 19 '05 #1
0 1236

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3897
by: Jason | last post by:
The following stored procedure is taking too long (in my opinion). The problem seems to be the SUM line. When commented out the query takes a second or two. When included the response time climbs to minute and a half. Is my code that inefficient or is SUM and ABS calls just that slow? Any suggestions to spead this up? Thanks, - Jason
2
908
by: Keith | last post by:
I am having a problem creating a many-to-many-to-many type relationship. It works fine, but when I create a view to query it and test it, it does not generate the results I expected. Below if the DDL for the tables and the SQL for the view.
5
9849
by: grzes | last post by:
MS SQL Server 2000. My case is: I have the table T with primary key calling __recid int without identity property. This table includes a lot of records (about 1000000). I need to convert __recid's data type to IDENTITY. As you know sql sentence: "alter table T alter column __recid int IDENTITY not null" does not work with not-empty tables....
8
9224
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record is entered, either from a form or from the table view of the table, when the record gets saved it immediately displays #DELETED# in all of the...
2
4770
by: Martin Maat | last post by:
Hi. I want to use the same mutex in different classes (web pages in an ASP.NET application). In global.asax.cs, the class that starts up first, I create a Mutex like this: static private Mutex mtxBezoeken = new Mutex(false, "bezoeken"); which compiles and executes just fine. Then in another page I want to write to a file that may also...
1
3667
by: Arda | last post by:
Hi all, I tried to connect a database(SQL) by both VS 2003 interface tools and normal typing. When I make the connection in subdirectory project it doesn't work and gives an error like this; Server Error in '/WebApplication1' Application. ----------------------------------------------------
4
2181
by: Chris Gatto | last post by:
Hi, I'm having what should be a minor problem but has turned into a 2 day slug fest with ASP.Net. I am simply attempting to authenticate my asp.net application users against users in an AD group set up on our domain. It seems to me I am missing something very simple and obvious, but none of the MSDN articles I have read are indicating...
3
4495
by: Dan | last post by:
I'm writing a record from an asp.net page to SQL Server. After the insert I'm selecting @@identity to return the ID of the record that I just wrote. It worked fine until I typed a semicolon into one of the string fields to be inserted. The string fields are inside single quotes in the INSERT command. With the semicolon in the string, the...
3
2838
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long time. Now the identity value is over 700.000 and I get errors whiles retrieving the inserted identitiy value. If I delete rows and reset the identity...
8
1062
by: Tom P. | last post by:
I have a webpage that takes user data and sends it to a stored procedure that inserts a row in a table then returns the ID of that row for display. The stored procedure does an insert then it uses IDENT_CURRENT to get the identity of the row that was just inserted and selects the row back out to display. The dataset is not getting...
0
7861
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8141
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8300
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7892
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6548
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5669
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3802
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2299
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1130
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.