Stored Proc Partially Runs, no error

[firstdivision]

Hi,

I have a stored procedure that's inserting a new record, getting the new ID, and then updating a second table with the new ID. When called from the ASP.NET page, the procedure runs, but only the first statement (as far as I can tell). So I end up with the record created, but no child records associated with it.

However, if I run the same EXEC statement from query analyzer the entire thing runs fine, both under my NT user account and the ASP.NET login account.

I also ran a trace with SQL Profiler, I can see the statement go by, and if I copy the statement from Profiler and paste it into Query Analyzer, it will perform fine.

The code on the ASP.NET page is wrapped in a Try/Catch which never causes an excpetion.

I have tried running the page both with and without the transaction code enabled. No effect.

Since I can run the stored procedure under the ASP.NET account from query analyzer, and it runs fine there, can I assume that the problem is within my .NET code and not the Stored procedure? I am new at writing stored procedures so it is possible that I have an error there I suppose. Here it is:

CREATE PROCEDURE spCreatePayment
( 
    @AdjID 		INT,
    @DateEntered 	DATETIME,
    @Comment 		varCHAR(50),
    @UserID 		INT
) 
AS 

DECLARE  @new_id INT

BEGIN
	INSERT INTO P_PaymentRecord(DateEntered,Comment,UserID)  VALUES(@DateEntered,@Comment,@UserID)
	SELECT @new_id = @@IDENTITY
	UPDATE P_PaymentItem SET paymentID=@new_ID WHERE adjID=@AdjID

END
GO

I am at a complete loss to explain what's going on.

 

[firstdivision]

Never mind, i'm an idiot.

It's hard to update the records before they're created isn't it???


Arrrgghh.

Sorry for wasting everyone's time. lol

Sometimes it's right in front of you and you don't see it...

Thanks