How to retrieve the decimal values from database and store it in a variable and use it in if statement?

Question

I have a stored procedure in Sql server. This sp returns a dataset (with 49 columns). There is a column "Product_Amnt" in the dataset which has decimal values.

I am trying to retrieve this column data in asp.net application using c#.

DataTable dt = new DataTable();
try
{
    SqlCommand com = new SqlCommand("sp_ProductDetails");
    com.CommandType = CommandType.StoredProcedure;
    com.Connection = con;
    com.Parameters.Add("@ID", SqlDbType.Int).Value = this._ID;
    SqlDataAdapter da = new SqlDataAdapter(com);
    da.Fill(dt);
}
catch (Exception ex) { new Error(ex); }
return dt;

I want to use the "Product_Amnt" column to display the products based on conditions.

decimal pr_charges = Convert.ToDecimal(dr["Product_Amnt"]);

But it gives an error saying "object cannot be cast from dbnull to other types". So I modified the code like this,

 decimal pr_charges = dr["Product_Amnt"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Product_Amnt"]);
if (pr_charges == 0.00M)
   {
      counter++;
   }

But it always gives me value 0 in pr_charges variable.

How to retrieve the decimal values from database and store it in a variable?

drop your data into a grid or print it to the console to be sure you're pulling back what you think you are. You don't have a casting problem; DBNull is just that, null. When you compare `dr["Product_Amnt"] == DBNull.Value` and get 0 it's because your data in that column *is* null — DrewJordan, Mar 12 '15 at 19:14
Please, read this thread: http://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values — Erwin, Mar 12 '15 at 19:24
When you stop the code in the debugger and look at the data in the dataTable you've gotten back, you see values other than NULL in that column? Or do you just assume they are there because they are in your database? — Tab Alleman, Mar 12 '15 at 19:36
Can you post your code in which you process **dt** (I assume you loop through the table)? — Joe Eveleigh, Mar 12 '15 at 20:34
@nrvbha Did you check your `dt` to verify if values other than `0` and `DBNull.Value` are returned? Is the datatype of the database column `decimal`? — Code.me, Mar 12 '15 at 21:05

score 1 · Accepted Answer · answered Mar 12 '15 at 20:45

On the base you wrote, It can be assumed that Product_Amnt column is nullable. In this case it is good idea to declare pr_charges variable like nullable decimal . decimal? pr_charges

You can use not nullable just putting insted of Null some default value, actually you try to do that but again seems that value is taken vice versa

decimal pr_charges = dr["Product_Amnt"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Product_Amnt"]);

check please also enter link description here

and enter link description here

How to retrieve the decimal values from database and store it in a variable and use it in if statement?

1 Answers1