Insert Problem

[schafjoe]

This Stored Procedure works when I run it through the SQL Query Analyzer but does not when I use it from an ASP Page.

It will put the data in the dbo.Src table but I cannot get it to put it into the dbo.LockSrc Table.

Can somebody please tell me why.

ALTER Procedure SrcData
@Users Int = 0,
@Period Int = 0,
@Region Int = 0,
@Section Int = 0,
@Element Int = 0,
@Supplier Int = 0,
@Commodity Int = 0,
@Measure Int = 0,
@MaxScore Int = 0,
@Score Int = 0,
@SRCNotes Text = null
AS
BEGIN
DECLARE @ContactMeasure Int
SET @ContactMeasure = 1

INSERT INTO dbo.Src (Users, Period, Region, Section, Element, Supplier, Commodity, Measure, MaxScore, Score, SRCNotes)
VALUES(@Users, @Period, @Region, @Section, @Element, @Supplier, @Commodity, @Measure, @MaxScore, @Score, @SrcNotes)


-- Inserts a new value into the LockSrc Table so that we can
-- Lock out any additional changes to the data
IF @Measure = @ContactMeasure
BEGIN

SELECT dbo.LockSrc.Primekey, dbo.LockSrc.Locked
FROM dbo.LockSrc
WHERE dbo.LockSrc.Period = @Period AND
dbo.LockSrc.Region = @Region AND
dbo.LockSrc.Supplier = @Supplier AND
dbo.LockSrc.Commodity = @Commodity

-- Record does not exist, enter in record
IF @@ROWCOUNT = 0
BEGIN
DECLARE @Locked Bit
SET @Locked = 0
INSERT INTO dbo.LockSrc (Period, Region, Supplier, Commodity, Locked)
VALUES(@Period, @Region, @Supplier, @Commodity, @Locked)
END
END
END

 

[swampBoogie]

Add

set nocount on

at the beginning of your procedure.

 

[SQLSister]

have you checked the permissions of the stored procedure. Does the login used for the ASP page connection have Exec rights to the stored procedure?

 

[schafjoe]

Thanks Everybody,
I did not have the correct permissions for this stored procedure.