Reputation: 2865
Problem:
When values are provided to the following script then executed using a setup in C# like below (or in SQL Server environment) the values do not update in the database.
Stored procedure:
-- Updates the Value of any type of PropertyValue
-- (Type meaining simple Value, UnitValue, or DropDown)
CREATE PROCEDURE [dbo].[usp_UpdatePropertyValue]
@PropertyValueID int,
@Value varchar(max) = NULL,
@UnitValue float = NULL,
@UnitOfMeasureID int = NULL,
@DropDownOptionID int = NULL
AS
BEGIN
-- If the Property has a @Value, Update it.
IF @Value IS NOT NULL
BEGIN
UPDATE [dbo].[PropertyValue]
SET
Value = @Value
WHERE
[dbo].[PropertyValue].[ID] = @PropertyValueID
END
-- Else check if it has a @UnitValue & UnitOfMeasureID
ELSE IF @UnitValue IS NOT NULL AND @UnitOfMeasureID IS NOT NULL
BEGIN
UPDATE [dbo].[UnitValue]
SET
UnitValue = @UnitValue,
UnitOfMeasureID = @UnitOfMeasureID
WHERE
[dbo].[UnitValue].[PropertyValueID] = @PropertyValueID
END
-- Else check if it has just a @UnitValue
ELSE IF @UnitValue IS NOT NULL AND @UnitOfMeasureID IS NULL
BEGIN
UPDATE [dbo].[UnitValue]
SET
UnitValue = @UnitValue
WHERE
[dbo].[UnitValue].[PropertyValueID] = @PropertyValueID
END
-- Else check if it has a @DropDownSelection to update.
ELSE IF @DropDownOptionID IS NULL
BEGIN
UPDATE [dbo].[DropDownSelection]
SET
SelectedOptionID = @DropDownOptionID
WHERE
[dbo].[DropDownSelection].[PropertyValueID] = @PropertyValueID
END
END
When I do an execution of this script, like below, it does not update any values.
Example execution:
String QueryString = "EXEC [dbo].[usp_UpdatePropertyValue] @PropertyValueID, @Value, @UnitValue, @UnitOfMeasureID, @DropDownOptionID";
SqlCommand Cmd = new SqlCommand(QueryString, this._DbConn);
Cmd.Parameters.Add(new SqlParameter("@PropertyValueID", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@Value", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@UnitValue", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@UnitOfMeasureID", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@DropDownOptionID", System.Data.SqlDbType.Int));
Cmd.Parameters["@PropertyValueID"].Value = Property.Value.ID; // 1
Cmd.Parameters["@Value"].IsNullable = true;
Cmd.Parameters["@Value"].Value = DBNull.Value;
Cmd.Parameters["@UnitValue"].IsNullable = true;
Cmd.Parameters["@UnitValue"].Value = DBNull.Value;
Cmd.Parameters["@UnitOfMeasureID"].IsNullable = true;
Cmd.Parameters["@UnitOfMeasureID"].Value = DBNull.Value;
Cmd.Parameters["@DropDownOptionID"].IsNullable = true;
Cmd.Parameters["@DropDownOptionID"].Value = 2; // Current Value in DB: 3
Details:
After running an execute (via C# code or SQL Server environment) it does not update dbo.DropDownSelection.SelectedOptionID
. I'm guessing that it might be because dbo.DropDownSelection.SelectedOptionID
is non-nullable and the parameter I'm using to set it is nullable (despite that when setting it shouldn't ever be null). Upon execution the return value is 0. If I run one of the Updates outside of the procedure they work perfectly, hence my suspicion that it has to do with null-able types.
Question(s):
Could this be because the parameters to the stored procedure are nullable and the fields I'm setting aren't?
If not, what could it be?
Upvotes: 32
Views: 213877
Reputation: 440
You can/should set your parameter to value to DBNull.Value;
if (variable == "")
{
cmd.Parameters.Add("@Param", SqlDbType.VarChar, 500).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("@Param", SqlDbType.VarChar, 500).Value = variable;
}
Or you can leave your server side set to null and not pass the param at all.
Upvotes: 5
Reputation: 701
It looks like you're passing in Null for every argument except for PropertyValueID and DropDownOptionID, right? I don't think any of your IF statements will fire if only these two values are not-null. In short, I think you have a logic error.
Other than that, I would suggest two things...
First, instead of testing for NULL, use this kind syntax on your if statements (it's safer)...
ELSE IF ISNULL(@UnitValue, 0) != 0 AND ISNULL(@UnitOfMeasureID, 0) = 0
Second, add a meaningful PRINT statement before each UPDATE. That way, when you run the sproc in MSSQL, you can look at the messages and see how far it's actually getting.
Upvotes: 17