CODEWITHSUNDEEP
c#sqlsql-serverado.net
sagesky36
sagesky36

Reputation: 4692

Cannot update record with SQL Server stored procedure

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 column 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 stored procedure or code work correctly?

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

Stored procedure

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 = NULL 
    END
ELSE
    BEGIN
        UPDATE dbo.HH_FuelTkt
            SET Image_ID = @imageID
            WHERE Ticket_No = @ticketNo
              AND Vehicle_No = @vehicleNo 
    END    END

C# 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 stored procedure 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 stored procedure 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

Upvotes: 0

Views: 468

Answers (1)

ah_hau
ah_hau

Reputation: 768

IF ((@vehicleNo = '') OR (@vehicleNo IS NULL))
BEGIN
    UPDATE dbo.HH_FuelTkt
        SET Image_ID = @imageID
        WHERE Ticket_No = @ticketNo
          **AND Vehicle_No = NULL** 
END

change the 

AND Vehicle_No = NULL

to 

AND Vehicle_No IS NULL

Normally on SQL check nullable value we use IS NULL instead of = NULL

Upvotes: 2

Related Questions