I need some help with this Stored Procedure that I'm using with .NET. What I need it to do first, is select the last record entered, then add 1 to the "ArrestID" field and hold that value for the next section of the SP. Then, I want it to insert the data submitted from the web page form and insert that, along with the new Arrest ID (That now has the 1 added to it) into the "ArrestID" field for the new record.

We were having the ArrestID has an autoincrement and has the PK, but have since decided that we should remodel the table design, and just add 1 to the last Arrest ID each time a new record is added.

Here's the SP. I've stared at this for 3 days and have changed this, change that, in an attempt to get it to work, to no avail. It may be way off by now and I may have unncessary commands in it.



When I run it in Query Analyzer, it returns the value of the "NewArrestID", but it doesn't insert it into the table in the Insert Command.

Any suggestions?

Thanks,

MB

DECLARE @LastArrestID int

SELECT TOP 1 @LastArrestID = [pkid], [ArrestID] +1
FROM [dbo].[tbl_ArrestIDNumbers]
ORDER BY [pkid] DESC;

SET @NewArrestID = @LastArrestID;

INSERT INTO [dbo].[tbl_ArrestIDNumbersTesting] ([Name],[CaseNo],[OfficerID],[ArrestDate],[Charge],[AssignedBy], [ArrestID],[AssignedDateTime])
VALUES (@Name,@CaseNo,@OfficerID,@ArrestDate,@Charge,@AssignedBy,@NewArrestID,GetDate());


SELECT @New_pkid = pkid FROM dbo.tbl_ArrestIDNumbersTesting WHERE (pkid = SCOPE_IDENTITY());
GO