Error Checking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error Checking

Author

Topic

pcmech
Starting Member

7 Posts

Posted - 2002-05-30 : 18:57:56

I have a page where the user runs a Dynamic SP to pull up the first row in a table. My ASP code will then take that record and update two tables (the first table is the same table it came from to prevent other users from opening that record) - the second table simply takes the record ID and a Session Variable and inserts in a new record. The SP that conducts this action checks the table for a duplicate record (to avoid two users having the same record).

CREATE PROCEDURE usp_insert_user_trail
(
@UserID int,
@ContactID int

)
AS
SET NOCOUNT ON
IF EXISTS(SELECT 'True' FROM tblUser_Trail WHERE Contact_ID = @ContactID)
BEGIN
--This means it exists, return it to ASP and tell us
SELECT 'This record already exists!'
END
ELSE
BEGIN
--This means the record isn't in there already, let's go ahead and add it
SELECT 'Record Added'
insert into tblUser_Trail (
User_ID,
Contact_ID,
create_date
)
values(
@UserID,
@ContactID,
GETDATE()
)
END
SET NOCOUNT OFF
GO

This seems to work fine when it only has one record to go through - for example it tries to insert 'id#6' and fails because it exists - it then resubmits the page and gets id#21 (because I marked #6 as taken with the first update) and fails (which it should). The problem I am getting is that it will continue to submit id#21 every other time I submit the page. It should go to the next record and if it doesn't exist, populate the 2nd table.

If table 2 only has one existing record it works great, however it craps out if it goes through this procedure more than once.

Does this make any sense, and does anyone have a better idea on how to do this.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-31 : 13:59:00

Not sure what you are trying to do here but
IF EXISTS(SELECT 'True' FROM

it is normal to write
IF EXISTS(SELECT * FROM

How are you blocking other users by the update?
If you are not holding a transaction then the update will not block anything.
If you are updating with a user ID then it may work - depends how it is coded and you will need to allow for lost connections.

It sounds like the problem is more with the ASP code which supplies the contact_id.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 14:37:13

quote:

...
This seems to work fine when it only has one record to go through - for example it tries to insert 'id#6' and fails because it exists - it then resubmits the page and gets id#21 (because I marked #6 as taken with the first update) and fails (which it should). The problem I am getting is that it will continue to submit id#21 every other time I submit the page. it should go to the next record and if it doesn't exist, populate the 2nd table.....and does anyone have a better idea on how to do this.

Sounds to me like your problem is clearly with it. Why are you submitting the proc as suspect? The proc seems to have nothing to do with failing control-of-flow code....

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources