C# Object cannot be cast from DBNull Error even upon successfull execution

Question

I am trying to update values in my database (whilst returning a status msg) and am getting the following InvalidCastError - Object cannot be cast from DBNull to other types. However, the information does update in the database. It just doesn't seem to want to return 'true' or success equivalent. Code shown below:

C#:

 public bool Update(Customer pCustomer)
    {
        using (SqlConnection sqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            using (SqlCommand sqlComm = new SqlCommand("Update_Customer", sqlConnect))
            {
                sqlComm.CommandType = CommandType.StoredProcedure;
                //Other items to update (Not causing issues)

                sqlComm.Parameters.Add("@Message", SqlDbType.Bit).Direction = ParameterDirection.Output;
                sqlComm.Connection = sqlConnect;

                try
                {
                    sqlComm.Connection.Open();
                    sqlComm.ExecuteNonQuery();
                    return Convert.ToBoolean(sqlComm.Parameters["@Message"].Value);
                }

                catch(Exception)
                {
                    throw;
                }

                finally
                {
                    sqlComm.Connection.Close();
                }
            }
        }
    }

SQL:

     //Unrelevent code removed
  AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
            BEGIN
                UPDATE Customer
                SET
                    Password = @Password,
                    RecordTimeStamp = @NewRecordTimeStamp
                WHERE CustomerID = @CustomerID AND RecordTimeStamp = @OldRecordTimeStamp
            END
            IF @@ROWCOUNT <> 1
                BEGIN
                    Set @Message = 1
                END
        IF @@ERROR <> 0
            ROLLBACK TRAN
        ELSE
            COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @Err nvarchar(500)
        SET @Err = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 1)
    END CATCH
END
GO

Are you checking to see if anything you are sending or returning is null? — Brian
– Brian, Commented Feb 27, 2013 at 22:32
Everything has a value, it updates correctly in the database. Just doesn't seem to register as a successful update. — ZeeeeeV
– ZeeeeeV, Commented Feb 27, 2013 at 22:32
OK, everything updates in the database, BUT does @Message have either the words "True" or "False" within it? — Kenneth K.
– Kenneth K., Commented Feb 27, 2013 at 22:34
@KennethK. - beat me to the punch. If you aren't returning 'True' or 'False' this will throw an exception; as it is now. You need to set a breakpoint and check the value of @Message. — Brian
– Brian, Commented Feb 27, 2013 at 22:35
well, maybe i'm missing something here, but I'm going to state the obvious, where are you assigning a value to @Message??? I'd recommend you a nice ternary with dbnull.value , but you're straight up missing the line I'd recommend it on, pilot error? See Grant Clement's post for syntax. — RandomUs1r
– RandomUs1r, Commented Feb 27, 2013 at 22:42

Grant Clements · Accepted Answer · 2013-02-27 22:34:28Z

3

Taking an educated guess, I think sqlComm.Parameters["@Message"].Value is returning System.DBNull.Value.

Is it possible that @@ROWCOUNT is equal to 1 then therefore @Message isn't being set?

answered Feb 27, 2013 at 22:34

Grant Clements

9846 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohsen Heydari · Accepted Answer · 2013-02-27 23:33:05Z

1

AS
BEGIN
Set @Message = 0
    BEGIN TRY
        BEGIN TRAN
            BEGIN
                UPDATE Customer
                SET
                    Password = @Password,
                    RecordTimeStamp = @NewRecordTimeStamp
                WHERE CustomerID = @CustomerID AND RecordTimeStamp = @OldRecordTimeStamp
            END
            IF @@ROWCOUNT = 1
                    Set @Message = 1
            END
            IF @@ERROR <> 0
            ROLLBACK TRAN
            ELSE
            COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @Err nvarchar(500)
        SET @Err = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 1)
    END CATCH
END
GO

answered Feb 27, 2013 at 23:33

Mohsen Heydari

7,2924 gold badges33 silver badges47 bronze badges

Comments

Dustin Kingen · Accepted Answer · 2013-02-27 23:19:48Z

0

Unless my T-Sql is terrible this should work.

BEGIN
    SET @Message = 0;

    BEGIN TRANSACTION

    BEGIN TRY
        UPDATE Customer
        SET Password        = @Password,
            RecordTimeStamp = @NewRecordTimeStamp
        WHERE CustomerID    = @CustomerID
        AND RecordTimeStamp = @OldRecordTimeStamp

        IF @@ROWCOUNT <> 1
            Set @Message = 1;

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH

        DECLARE @Err nvarchar(500)
        SELECT @Err = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 1)

        ROLLBACK TRANSACTION
    END CATCH
END

answered Feb 27, 2013 at 23:19

Dustin Kingen

21.4k7 gold badges55 silver badges94 bronze badges

Collectives™ on Stack Overflow

C# Object cannot be cast from DBNull Error even upon successfull execution

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related