How to convert a DBNULL to double?

im getting a value from the database. it came out an error saying "Conversion from DBNULL to Double is not Valid." anyone, pls help me?

Public Function Total() As Double Dim Total As Double

    Dim strConn As String
    strConn = ConfigurationManager.ConnectionStrings("***").ToString
    Dim conn As New SqlConnection(strConn)

    Dim strSql As String
    strSql = "SELECT SUM (p.Price * c.Quantity) as 'Total' " & _
    "FROM CartItem sci INNER JOIN Product p ON c.ProductID=p.ProductID " & _
    "WHERE c.CartID=@CartID "

    Dim cmd As New SqlCommand(strSql, conn)

    cmd.Parameters.开发者_如何学CAddWithValue("@CartID", CartID)

    Dim da As New SqlDataAdapter(cmd)

    Dim ds As New DataSet

    conn.Open()

    da.Fill(ds, "CartItem")

    conn.Close()

    If (ds.Tables("CartItem").Rows.Count) <> 0 Then
        **Total = ds.Tables("ShopCartItem").Rows(0)("Total")**
    Else
        Total = 0.0
    End If
    Return Total
End Function

---

You can't cast a DbNull to a double. You can, though, cast it to a nullable double (c# double?).

I wrote a bunch of DataRow extension methods (C#) that help a lot with this. Makes the syntax a lot tidier. Usage is simple. A C# example:

public class Hormone
{
    public int           ID               { get ; private set ; }
    public HormoneLuType Type             { get ; private set ; }
    public int           AgeStarted       { get ; private set ; }
    public int           AgeStopped       { get ; private set ; }
    public int           DurationInMonths { get ; private set ; }
    public bool          IsCurrentlyUsing { get ; private set ; }
    public DateTime?     DateLastEdited   { get ; private set ; }

    public string Name { get { return Type.ToString() } }

    public Hormone( DataRow dr )
    {
        this.ID               =                    dr.CastAsInt(              "ihormoneid" )        ;
        this.Type             = new HormoneLuType( dr.CastAsIntNullable(      "ihormluid"  ) ?? 0 ) ;
        this.AgeStarted       = (int)              dr.CastAsDecimal(          "nstartage"  )        ;
        this.AgeStopped       = (int)              dr.CastAsDecimal(          "nendage"    )        ;
        this.DurationInMonths = (int)              dr.CastAsDecimal(          "nduration"  )        ;
        this.IsCurrentlyUsing =                    dr.CastAsBool(             "lusingnow"  )        ;
        this.DateLastEdited   =                    dr.CastAsDateTimeNullable( "tedit"      )        ;

        return ;
    }
}

Here's the extension class:

using System;
using System.Data;

namespace DataAccess.Utils
{
    public static class DataRowExtensions
    {

        #region downcast to DateTime

        public static DateTime CastAsDateTime( this DataRow row , int index )
        {
            return toDateTime( row[index] ) ;
        }
        public static DateTime CastAsDateTime( this DataRow row , string columnName )
        {
            return toDateTime( row[columnName] ) ;
        }

        public static DateTime? CastAsDateTimeNullable( this DataRow row , int index )
        {
            return toDateTimeNullable( row[index] );
        }
        public static DateTime? CastAsDateTimeNullable( this DataRow row , string columnName )
        {
            return toDateTimeNullable( row[columnName] ) ;
        }

        #region conversion helpers

        private static DateTime toDateTime( object o )
        {
            DateTime value = (DateTime)o;
            return value;
        }

        private static DateTime? toDateTimeNullable( object o )
        {
            bool  hasValue = !( o is DBNull );
            DateTime? value    = ( hasValue ? (DateTime?) o : (DateTime?) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to DateTime

        #region downcast to byte[]

        public static byte[] CastAsByteArray( this DataRow row , int index )
        {
            return toByteArray( row[index] );
        }
        public static byte[] CastAsByteArray( this DataRow row , string columnName )
        {
            return toByteArray( row[columnName] );
        }

        #region conversion helpers

        private static byte[] toByteArray( object o )
        {
            bool   hasValue = !( o is DBNull );
            byte[] value    = ( hasValue ? (byte[]) o : (byte[]) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to Byte[]

        #region downcast to int

        public static int CastAsInt( this DataRow row , int index )
        {
            return toInt( row[index] ) ;
        }
        public static int CastAsInt( this DataRow row , string columnName )
        {
            return toInt( row[columnName] ) ;
        }

        public static int? CastAsIntNullable( this DataRow row , int index )
        {
            return toIntNullable( row[index] );
        }
        public static int? CastAsIntNullable( this DataRow row , string columnName )
        {
            return toIntNullable( row[columnName] ) ;
        }

        #region conversion helpers

        private static int toInt( object o )
        {
            int value = (int)o;
            return value;
        }

        private static int? toIntNullable( object o )
        {
            bool hasValue = !( o is DBNull );
            int? value    = ( hasValue ? (int?) o : (int?) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to int

        #region downcast to int

        public static decimal CastAsDecimal( this DataRow row , int index )
        {
            return toDecimal( row[index] ) ;
        }
        public static decimal CastAsDecimal( this DataRow row , string columnName )
        {
            return toDecimal( row[columnName] ) ;
        }

        public static decimal? CastAsDecimalNullable( this DataRow row , int index )
        {
            return toDecimalNullable( row[index] );
        }
        public static decimal? CastAsDecimalNullable( this DataRow row , string columnName )
        {
            return toDecimalNullable( row[columnName] ) ;
        }

        #region conversion helpers

        private static decimal toDecimal( object o )
        {
            decimal value = (decimal)o;
            return value;
        }

        private static decimal? toDecimalNullable( object o )
        {
            bool     hasValue = !( o is DBNull );
            decimal? value    = ( hasValue ? (decimal?) o : (decimal?) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to int

        #region downcast to bool

        public static bool CastAsBool( this DataRow row , int index )
        {
            return toBool( row[index] ) ;
        }
        public static bool CastAsBool( this DataRow row , string columnName )
        {
            return toBool( row[columnName] ) ;
        }

        public static bool? CastAsBoolNullable( this DataRow row , int index )
        {
            return toBoolNullable( row[index] );
        }
        public static bool? CastAsBoolNullable( this DataRow row , string columnName )
        {
            return toBoolNullable( row[columnName] ) ;
        }

        #region conversion helpers

        private static bool toBool( object o )
        {
            bool value = (bool)o;
            return value;
        }

        private static bool? toBoolNullable( object o )
        {
            bool  hasValue = !( o is DBNull );
            bool? value    = ( hasValue ? (bool?) o : (bool?) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to bool

        #region downcast to string

        public static string CastAsString( this DataRow row , int index )
        {
            return toString( row[index] );
        }
        public static string CastAsString( this DataRow row , string columnName )
        {
            return toString( row[columnName] );
        }

        #region conversion helpers

        private static string toString( object o )
        {
            bool   hasValue = !( o is DBNull );
            string value    = ( hasValue ? (string) o : (string) null ) ;
            return value;
        }

        #endregion

        #endregion downcast to string

    }
}

Hope this helps!

---

Try changing your SQL to:

SELECT SUM (ISNULL(p.Price,0) * ISNULL(c.Quantity,0) ) as 'Total' " & _
    "FROM CartItem sci INNER JOIN Product p ON c.ProductID=p.ProductID " & _
    "WHERE c.CartID=@CartID 

You could also set your database table to not allow nulls

---

You'll need to check that ds.Tables("ShopCartItem").Rows(0))("Total") = DbNull.Value before you set it.

If (ds.Tables("CartItem").Rows.Count) <> 0 Then
    If ds.Tables("ShopCartItem").Rows(0))("Total") = DbNull.Value
       Total = 0.0
    else
      Total = ds.Tables("ShopCartItem").Rows(0)("Total")**
Else
    Total = 0.0
End If

---

If it's possible for the value to be null, your return type needs to be double? (nullable double).

---

Can you instead use Nullable(Of double) as your data type on the VB side?

---

You have to check if Nullable(of Double).HasValue. total = 0.0

If (ds.Tables("CartItem").Rows.Count) <> 0 Then
     Dim sciValue As Nullable(Of Double) = DirectCast(ds.Tables("ShopCartItem").Rows(0)("Total"), Nullable(Of Double))
     If sciValue.HasValue Then
         Total = sciValue.Value
     End If
End If