Procedure Error : Unable to cast object of type 'System.DBNull' to type 'System.String'

Question

 alter procedure SAVE_PERSONAL_DETAILS_OP
    @v_PNO CHAR(4),
    @v_EMAILID VARCHAR(80),
    @v_MOBILE CHAR(10),
    @v_AADHAR CHAR(12),
    @v_PANNO CHAR(10),
     @v_PASSPORT CHAR(9),
     @v_EXPIRY_DT date,   
    @ERROR VARCHAR(100) OUTPUT 

 AS
   BEGIN 
        DECLARE @nCnt float=0
        DECLARE @vCnt float=0

        if @v_PNO=''
        begin
        set @ERROR = 'Pno not in Session..!!!!'
        return;
        end         
        if @v_MOBILE =''
        begin
        set @ERROR = 'Please Enter Mobile Number !!!!'
        return;
        end
        if @v_AADHAR =''
        begin
        set @ERROR = 'Please Enter Aadhar Number !!!! '
        return;
        end
        if @v_PANNO =''
        begin
        set @ERROR = 'Please Enter PAN Number !!!! '
        return;
        end
        if @v_PASSPORT =''
        begin
        set @ERROR = 'Please Enter Passport Number !!!!'
        return;
        end
        if @v_EXPIRY_DT =''
        begin
        set @ERROR = 'Please Select Expiry Date !!!!'
        return;
        end         

         --SET NOCOUNT ON

            SET @nCnt=0
            select @nCnt = count(*) from Tbl_Aadhar where pno=@v_PNO
                 if @nCnt=0 
                    begin try 
                        INSERT INTO Tbl_Aadhar(PNO , AadharNo , Pan, CivilPassportNo, PassportExpiry, InsertDate) 
                        VALUES (@v_PNO,@v_AADHAR,@v_PANNO, @v_PASSPORT,@v_EXPIRY_DT, GETDATE())
                     if @nCnt !=0   
                         begin                    
                          declare @ID Char(10)
                          declare @MobileId Char(10)
                          set @ID=(select ID from Tbl_Aadhar where pno=@v_PNO)
                          set @MobileId=(select registrationid from tbl_Registration where pno=@v_PNO and Cader='OP')
                          update Tbl_Aadhar set AadharNo=@v_AADHAR , Pan =@v_PANNO , CivilPassportNo =@v_PASSPORT , PassportExpiry= @v_EXPIRY_DT 
                          where ID=@ID and Pno=@v_PNO

                          update tbl_Registration set Mobile=@v_MOBILE where PNO=@v_PNO and RegistrationId=@MobileId
                        end 

                             SET @ERROR = 'The Details has been saved successfully. for Pno  '  +  @v_PNO                                                             
                    end try
                    begin catch
                            SET @ERROR = 'Something Went Wrong. Please Try Agian Later'
                    end catch       

   END

Please provide more details and the SQL technology. This appears to be an APP error calling your SP, but I dont see the app code — Brad, Mar 12 '20 at 13:43
Does this answer your question? [Unable to cast object of type 'System.DBNull' to type 'System.String\`](https://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string) — Vijay, Mar 12 '20 at 13:49
You need to include the program code, not just the stored procedure since the error obviously comes from the program. — mortb, Mar 12 '20 at 13:50
Probably you execute the stored procedure in your program and the stored procedure executes without errors, leaving the `@error` parameter unassigned having a database `NULL` value, then you try to read the value in the program casting it to `string` which will not work since the `@error` will be assigned `DBNull.Value` which is not possible to cast — mortb, Mar 12 '20 at 13:58
That is NOT how you check / handle errors. And your catch block will eat most sql errors, hiding them from the app and providing back a useless (for debugging / understanding purposes) generic "problem" string. All of this checking should be in the user interface. I'll guess that some of this information should be encrypted as well. — SMor, Mar 12 '20 at 13:58
Also it seems the line `if @nCnt !=0` will not be hit since it seems nested below the `begin try` which is nested below `if @nCnt =0` — mortb, Mar 12 '20 at 14:01
You are getting a .NET error but you have shown your SQL code only. It's going to be very difficult to help you unless you add the .NET code that throws the error. — Jacob H, Mar 12 '20 at 14:18

score 0 · Accepted Answer · answered Mar 12 '20 at 16:39

If any of your Sql Parameters are returning Null value and at the DAO layer you are reading / converting that entity as String then you will face the same error.

To avoid such conditions, Use ISNull checks at Sp level

Example.

Select Name from Table ;

Here Name is a string. If this is Null and you will use this String anywhere in your code as string than this will give error as DB Null conversion error.

Solution: Select ISNULL ( Name , ' ' ) From Table ;

Here you are chect the Null possibilities and of in case we get null value then it will display ' ' blank which can be used anywhere in the code.

Procedure Error : Unable to cast object of type 'System.DBNull' to type 'System.String'

1 Answers1