insert data into sql table and get recent inserted iD and insert data in same table from different table

Question

Here is the situation that I have to insert profile photos in the SQL table. But here are 2 scenarios the condition

1. if user is inserting photo and data from front end. Its working perfectly fine.

2. if user is skip the photo and just inserting his biography then in that case the default image should be inserted by default. I tried to do in front end Just adding dummy image in if else condition, but in DMZ server for some reason this is creating problem, on local server its working good. Here is the Query...

      ALTER PROCEDURE [dbo].[SavePhysicianBiodata]
     -- Add the parameters for the stored procedure here
     @ID int,
      @Physician_Bio nvarchar(MAX),
     @Physician_Mnemonic nvarchar(MAX),
     @Physician_Image image,
     @Physician_ImageType nvarchar(MAX),
     @Physician_ImageFileName nvarchar(MAX)
    AS
    BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
   if( @ID is null OR @ID='')
    begin
   
                --if not image then deafult image will be applied
            if((@Physician_ImageType is null or @Physician_ImageType='') and 
         (@Physician_ImageFileName is null or @Physician_ImageFileName='') )
                begin
                        insert into Physician_Biodata(Physician_Bio, Physician_Mnemonic)
                        values(@Physician_Bio, @Physician_Mnemonic)
   
        set @ID = SCOPE_IDENTITY()
                                update [dbo].[Physician_Biodata]
                                set Physician_Image=@Physician_Image,
                                Physician_ImageType=@Physician_ImageType,
                                Physician_ImageFileName=@Physician_ImageFileName
                                where ID=@ID
                        end
            else
                    begin
                        -- Insert statements for procedure here when user adds photo as well
                        insert into Physician_Biodata(Physician_Bio, Physician_Mnemonic, 
                       Physician_Image, Physician_ImageType, Physician_ImageFileName)
                        values(@Physician_Bio, @Physician_Mnemonic, 
                    @Physician_Image,@Physician_ImageType,@Physician_ImageFileName)
                    end
   
               end
            else
                begin
                        update [dbo].[Physician_Biodata]
                        set Physician_Bio=@Physician_Bio,
                        Physician_Mnemonic=@Physician_Mnemonic,
                        Physician_Image=@Physician_Image,
                        Physician_ImageType=@Physician_ImageType,
                        Physician_ImageFileName=@Physician_ImageFileName
                where ID=@ID
                end
   
               END
   

In this query I also tried insert query which is given below

                                    insert into Physician_Biodata(ID, Physician_Image, Physician_ImageType, Physician_ImageFileName)
                                select @ID, dd.Physician_Image,dd.Physician_ImageType,dd.Physician_ImageFileName from DefaultImage as dd
                                join Physician_Biodata
                                on Physician_Biodata.Physician_ImageFileName = dd.Physician_ImageFileName
                                where Physician_Biodata.ID = @ID

but getting error during execute procedure

Msg 544, Level 16, State 1, Procedure dbo.SavePhysicianBiodata, Line 35 [Batch Start Line 2]
Cannot insert explicit value for identity column in table 'Physician_Biodata' when IDENTITY_INSERT is set to OFF.

If somebody can help me it would be great.. Thanks in advance.

Answer 1

It appears that Physician_Biodata's ID column is an IDENTITY, hence the exception you have.

Changing this...

INSERT INTO Physician_Biodata (
    ID, Physician_Image, Physician_ImageType, Physician_ImageFileName
)
SELECT 
    @ID,
    dd.Physician_Image,
    dd.Physician_ImageType,
    dd.Physician_ImageFileName 
FROM DefaultImage AS dd
JOIN Physician_Biodata
    ON Physician_Biodata.Physician_ImageFileName = dd.Physician_ImageFileName
WHERE 
    Physician_Biodata.ID = @ID;

To this...

INSERT INTO Physician_Biodata (
    Physician_Image, Physician_ImageType, Physician_ImageFileName
)
SELECT 
    dd.Physician_Image,
    dd.Physician_ImageType,
    dd.Physician_ImageFileName 
FROM DefaultImage AS dd
JOIN Physician_Biodata
    ON Physician_Biodata.Physician_ImageFileName = dd.Physician_ImageFileName
WHERE 
    Physician_Biodata.ID = @ID;

Will make your "explicit value" exception go away as in your INSERT you are attempting to insert @ID into ID which is an identity column. You also use ID = @ID in your WHERE clause, which makes inserting @ID pointless as this would be a chicken-and-egg issue.

On another note, if @Physician_ImageType and @Physician_ImageFileName are both NULL going in, they'll still be NULL on your UPDATE given your existing SP's logic.

I've taken a little liberty to tidy/simplify your T-SQL and added a note about what I've questioned.

ALTER PROCEDURE [dbo].[SavePhysicianBiodata] (
    @ID int,
    @Physician_Bio nvarchar(MAX),
    @Physician_Mnemonic nvarchar(MAX),
    @Physician_Image image,
    @Physician_ImageType nvarchar(MAX),
    @Physician_ImageFileName nvarchar(MAX)
)
AS
BEGIN

    SET NOCOUNT ON;

    IF ISNULL( @ID, '' ) = ''
    BEGIN

        --if not image then deafult image will be applied
        IF ISNULL( @Physician_ImageType, '' ) = '' AND ISNULL( @Physician_ImageFileName, '' ) = ''
        BEGIN

            INSERT INTO Physician_Biodata ( Physician_Bio, Physician_Mnemonic )
                VALUES ( @Physician_Bio, @Physician_Mnemonic ) ;
            
            SET @ID = SCOPE_IDENTITY();

            /*
                Where are you setting the values for @Physician_Image, @Physician_ImageType, and @Physician_ImageFileName? These are still NULL?
            */

            UPDATE [dbo].[Physician_Biodata]
            SET
                Physician_Image = @Physician_Image,
                Physician_ImageType = @Physician_ImageType,
                Physician_ImageFileName = @Physician_ImageFileName
            WHERE 
                ID = @ID;
        
        END
        ELSE BEGIN
        
            -- Insert statements for procedure here when user adds photo as well
            INSERT INTO Physician_Biodata (
                Physician_Bio, Physician_Mnemonic, Physician_Image, Physician_ImageType, Physician_ImageFileName
            )
            VALUES (
                @Physician_Bio, @Physician_Mnemonic, @Physician_Image, @Physician_ImageType, @Physician_ImageFileName
            );
        
        END
    
    END
    ELSE BEGIN
    
        UPDATE [dbo].[Physician_Biodata]
        SET
            Physician_Bio = @Physician_Bio,
            Physician_Mnemonic = @Physician_Mnemonic,
            Physician_Image = @Physician_Image,
            Physician_ImageType = @Physician_ImageType,
            Physician_ImageFileName = @Physician_ImageFileName
        WHERE
            ID = @ID;
    
    END

END

Answer 2

Yes I have already changed the first insert statement (removed ID) and updated the 2nd query

set @ID = IDENT_CURRENT('Physician_Biodata') 
update Physician_Biodata 
set Physician_Biodata.Physician_Image= DefaultImage.Physician_Image, Physician_Biodata.Physician_ImageType= DefaultImage.Physician_ImageType, Physician_Biodata.Physician_ImageFileName=DefaultImage.Physician_ImageFileName from Physician_Biodata, DefaultImage where Physician_Biodata.ID=@ID 

and it worked