As per your coding you are checking InvstAc column value. so if same Investmant A/C no is exist it will return -1 else it will insert details and return Investmant No.
First you need to change your store procedure so it will work correct in both cases. You are returning the -1 value if record is exist but not returning the Investmant A/c no if it get inserted so you first need to change in your stored procedure as below.
CREATE PROCEDURE [dbo].[RDPrintedPassbook]
@col1 NCHAR(10),
@Branch NVARCHAR(50),
@InvstAc NVARCHAR(50),
@InvstName NVARCHAR(120),
@NameTitle NCHAR(10),
@PassBookStatus NCHAR(10),
@col5 NCHAR(10),
@col6 NCHAR(10),
@UserIP NCHAR(10),
@RecordDateTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT InvstAc FROM GPNL_InvstPassbookStatus WHERE InvstAc = @InvstAc)
BEGIN
SELECT -1 -- Username exists.
END
ELSE
BEGIN
INSERT INTO dbo.GPNL_InvstPassbookStatus
(col1, Branch, InvstAc, InvstName, NameTitle, PassBookStatus, col5, col6, UserIP, RecordDateTime)
VALUES(@col1, @Branch, @InvstAc, @InvstName, @NameTitle, @PassBookStatus, @col5, @col6, @UserIP, @RecordDateTime)
-- return Investmant A/c no
SELECT @InvstAc
END
END
GO
Above store procedure will check if there is any record exist with same Investmant A/c no then it will return -1 else it will insert record and return the Investmant no. if you have any modification in your where codition to check more condition for same Investmant A/C then you can modify the where condition as per your requirement.
