Cannot update record with SQL SPROC

Vote count:

0

I've tried everything to get this to work and I know I'm missing something very simple.

I have a method that calls a stored procedure to update a record in a table.

It just so happens that one of the parameters is nullable in the database and the program value for it comes in as an empty string ("") for the "vehicleNo" field in this particular kind of situation.

All the other records get updated except if an empty string comes in and I try and update the record with the stored procedure.

Can someone please point out what I need to add to make the SPROC or code work correctly?

Below the code, I've tried executing the SPROC with hard coded values, but neither updates with the ImageID.

SPROC

ALTER PROCEDURE [dbo].[SPR_UPDATE_IMAGEID] 
    @ticketNo int,
    @vehicleNo varchar(6) = NULL,
    @imageID varchar(20)
AS
BEGIN
    IF ((@vehicleNo <> '') OR (@vehicleNo IS NULL))
        BEGIN
            UPDATE dbo.HH_FuelTkt
                SET Image_ID = @imageID
                WHERE Ticket_No = @ticketNo
                  AND Vehicle_No = @vehicleNo 
        END
    ELSE
        BEGIN
            UPDATE dbo.HH_FuelTkt
                SET Image_ID = @imageID
                WHERE Ticket_No = @ticketNo
                  AND Vehicle_No = NULL 
        END
END

CODE

public static bool UpdateData(int ticketNo, string vehicleNo, string imageID)
        {
            int retValue = 0;

            try
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HHInboundSqlConnection"].ToString()))
                {

                    SqlCommand cmd = new SqlCommand("SPR_UPDATE_IMAGEID", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@ticketNo", ticketNo);
                    cmd.Parameters.AddWithValue("@vehicleNo", vehicleNo);
                    cmd.Parameters.AddWithValue("@imageID", imageID);

                    cmd.Connection.Open();
                    retValue = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            return (retValue > 0);
        }

MANUAL SPROC EXECUTION # 1

DECLARE @return_value int

EXEC    @return_value = [dbo].[SPR_UPDATE_IMAGEID]
        @ticketNo = 147058,
        @vehicleNo = N'''''',
        @imageID = N'39084'

SELECT  'Return Value' = @return_value

MANUAL SPROC EXECUTION # 2

DECLARE @return_value int

EXEC    @return_value = [dbo].[SPR_UPDATE_IMAGEID]
        @ticketNo = 147058,
        @vehicleNo = N'NULL',
        @imageID = N'39084'

SELECT  'Return Value' = @return_value

asked 55 secs ago

sagesky36

985

Cannot update record with SQL SPROC