We help IT Professionals succeed at work.
Get Started

Object cannot be cast from DBNull to other types error when calling stored Procedure...

882 Views
Last Modified: 2012-05-10
We recently upgraded to visual studio 2010 and since then I have an issue running one of my stored procedures.

On the line decimal decTotalHundPrice = Convert.ToDecimal(parmTotalPrice.Value) I get the following error: Object cannot be cast from DBNull to other types.

If I run the stored procedure through Enterprise Manager I have no issues and I get a price back.

I have attached my c# code, the manual execute code, and the stored procedure code.
declare 
@TotalPrice money

exec sp_CalcUpsHundredWeightGround '90210', 250, '13', 'PLANT001', 2,'',null,null,null,null,null,0,5,null, @TotalPrice OUTPUT
print 'Price = ' + str(@TotalPrice)

Open in new window

ALTER PROCEDURE [dbo].[sp_CalcUPSHundredWeightGround]
	-- Add the parameters for the stored procedure here
	
	@Zip nvarchar(10),
	@TotalWeight decimal(8,2),
	@ServiceLevel nvarchar(10),
	@PlantID nvarchar(50),
	@Tier tinyint,
	@Zone nvarchar(15),
	@UnitPrice money,
	@MinimumWeightPrice money,
	@MinimumShipPrice money,
	@ServiceFee money,
	@DeficitRate money,
	@IsGround bit,
	@TotalPackages int,
	@TotalActualPrice money,
	@TotalPrice money OUTPUT
	--@Results money OUTPUT

AS
BEGIN
--Finding the correct column to select from.    
    Select @Zone = 
	    Case
		 When @ServiceLevel = '01' Then sr6 
		 When @ServiceLevel = '13' Then sr5 
		 When @ServiceLevel = '59' Then sr4 
		 When @ServiceLevel = '02' Then sr3 
		 When @ServiceLevel = '12' Then sr2 
		 When @ServiceLevel = '03' Then sr1 
		End
	from upshundredweightzipzone
	where zipstart <= SubString(@Zip, 1,3) 
	and zipEnd >= SubString(@Zip, 1,3) 
	and PlantID = @PlantID 
	and Tier = @Tier 
        
     
    IF @@RowCount > 0
		Begin
			--Selecting to see if the zip code is in the Delivery Area Surcharge Table
			IF Exists (select top 1 zipcode from UPSDelAreaSurcharge where zipcode = @Zip)
				Begin
					Select @ServiceFee = 8.50
				End
			else
				Begin
					Select @ServiceFee = 0.00	
				End
			
			IF @IsGround = 1 
				Begin
					--Selecting the minimum price based off weight
					Select top 1 @UnitPrice = Price from upshundredweightzoneprice
					where Zone = @Zone and "weight" >= 20 * @TotalPackages and PlantID = @PlantID and Tier = @Tier
					Select @MinimumWeightPrice = (20 * @TotalPackages / 100.0) * @UnitPrice
					--Selecting Minimum SHipment Price
					Select @MinimumShipPrice = MinimumPrice from UpsHundredWeightMinimum
					where Zone = @Zone and Tier = @Tier and PlantID = @PlantID
					--Selecting Total Actual Price
					Select top 1 @UnitPrice = Price from upshundredweightzoneprice
					where Zone = @Zone and "weight" >= @TotalWeight and PlantID = @PlantID and Tier = @Tier
					Select @TotalActualPrice = (@TotalWeight / 100.0) * @UnitPrice
				--Selecting Deficit Rate Price
					If @ServiceLevel = '03'
						Begin
						Select top 1 @UnitPrice = Price from upshundredweightzoneprice
						where Zone = @Zone and "weight" >= 500 and PlantID = @PlantID and Tier = @Tier
						Select @DeficitRate = (500 / 100.0) * @UnitPrice
						End
				End
			Else
				Begin
					--Selecting the minimum Price based off weight for air shipments
					Select top 1 @UnitPrice = Price from upshundredweightzoneprice
					where Zone = @Zone and "weight" >= 18 * @TotalPackages and PlantID = @PlantID and Tier = @Tier
					Select @MinimumWeightPrice = (18 * @TotalPackages) * @UnitPrice
					--Selecting Total Actual Price for air shipments
					Select top 1 @UnitPrice = Price from upshundredweightzoneprice
					where Zone = @Zone and "weight" >= @TotalWeight and PlantID = @PlantID and Tier = @Tier
					Select @TotalActualPrice = @TotalWeight * @UnitPrice
				End
			
			IF @IsGround = 1
				Begin
					IF @MinimumWeightPrice > @MinimumShipPrice
						Begin
							IF @MinimumWeightPrice > @TotalActualPrice
								Begin
									Select @TotalPrice = @MinimumWeightPrice + @ServiceFee
								End
							Else 
								Begin
									Select @TotalPrice = @TotalActualPrice + @ServiceFee
								End
						End
				End
			Else
				Begin
					If @MinimumWeightPrice > @TotalActualPrice
						Begin
							Select @TotalPrice = @MinimumWeightPrice + @ServiceFee
						End
					Else
						Begin
							Select @TotalPrice = @TotalActualPrice + @ServiceFee
						End
				End
			
			--This next function is checking to see if it is better off rating it as 500 pounds.  If it is,
			--then it will return the deficit rate price.
			IF @ServiceLevel = '03'
				Begin
					IF @TotalWeight <= 500
						Begin
							Select @DeficitRate = @DeficitRate + @ServiceFee
							IF @TotalPrice > @DeficitRate
								Begin
									Select @TotalPrice = @DeficitRate
								End
							Else
								Begin
									Select @TotalPrice = @TotalPrice
								End
						End
				END
End	
Else
	Begin
		Select @TotalPrice = 0.00
	End
	
END

Open in new window

public double GetUpsHundredWeight(double dblTotalWeightHund, int intTotalPackages, string strServiceLevel)
        {
            SqlConnection ObjSQLCon = new SqlConnection();
            SqlDataReader sqlDR;
            string strPostalCode = req["txtpostc"];
            //strPostalCode = strPostalCode.Substring(0, 3);

            try
            {
                ObjSQLCon.ConnectionString = ConfigurationManager.AppSettings["SqlConStr"]; ;
                SqlCommand sqlCmd = new SqlCommand("sp_CalcUPSHundredWeightGround", ObjSQLCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@Zip", strPostalCode);
                sqlCmd.Parameters.AddWithValue("@TotalWeight", dblTotalWeightHund);
                sqlCmd.Parameters.AddWithValue("@ServiceLevel", strServiceLevel);
                sqlCmd.Parameters.AddWithValue("@PlantID", objclass1.strPlantId);
                sqlCmd.Parameters.AddWithValue("@Tier", intTier);
                sqlCmd.Parameters.AddWithValue("@Zone", "");
                sqlCmd.Parameters.AddWithValue("@UnitPrice", SqlDbType.Money);
                sqlCmd.Parameters.AddWithValue("@MinimumWeightPrice", SqlDbType.Money);
                sqlCmd.Parameters.AddWithValue("@MinimumShipPrice", SqlDbType.Money);
                sqlCmd.Parameters.AddWithValue("@ServiceFee", SqlDbType.Money);
                sqlCmd.Parameters.AddWithValue("@DeficitRate", SqlDbType.Money);
                sqlCmd.Parameters.AddWithValue("@IsGround", blnIsGround);
                sqlCmd.Parameters.AddWithValue("@TotalPackages", intTotalPackages);
                sqlCmd.Parameters.AddWithValue("@TotalActualPrice", SqlDbType.Money);
                SqlParameter parmTotalPrice = sqlCmd.Parameters.Add("@TotalPrice", SqlDbType.Money);
                parmTotalPrice.Direction = ParameterDirection.Output;
                ObjSQLCon.Open();
                sqlDR = sqlCmd.ExecuteReader();
                decimal decTotalHundPrice = Convert.ToDecimal(parmTotalPrice.Value);
                sqlDR.Close();
                ObjSQLCon.Close();
                return Convert.ToDouble(decTotalHundPrice);
            }
            catch (Exception ex) { }
            return 0;
        }

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE