• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Dealing with Null DB results...

Hello,

I have this function that is summ'ing values from my DB.  some of the results are going to be Null but i get a "cannot convert String to Null" error.  whats the best way to handle this?  Here is my function so far...



    Private Sub GetTotals()
        Dim rsT As New ADODB.Recordset
        Dim Cmd As New ADODB.Command
        Dim Counter As Integer
        Dim LineNumber As Integer
        LineNumber = CurrentLine.SelectedValue

        conn.ConnectionString = ConnectionString
        conn.Open()

        SQL = "SELECT SumByLine.[Line Number], SumByLine.SetNTotal, SumByLine.InfertileTotal, SumByLine.[Early DeadTotal], SumByLine.HatchedTotal, SumByLine.CulledTotal, SumByLine.SurplusTotal, SumByLine.Placed_STotal, SumByLine.Placed_MTotal, SumByLine.Placed_FTotal FROM SumByLine WHERE (((SumByLine.[Line Number])=" & LineNumber & "))"
        rsT.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockUnspecified)
        Counter = rsT.Fields(0).Value
        rsT.Close()
        If Counter > 0 Then
            rsT.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockUnspecified)
            HatchSetlbl.Text = rsT.Fields(1).Value
            HatchInflbl.Text = rsT.Fields(2).Value
            HatchEDlbl.Text = rsT.Fields(3).Value
            HatchHatchlbl.Text = rsT.Fields(4).Value
            HatchCulllbl.Text = rsT.Fields(5).Value
            HatchSurpluslbl.Text = rsT.Fields(6).Value
            If MPlacedIn.Visible = True Then
                HatchMaleslbl.Text = rsT.Fields(8).Value
                HatchFemlbl.Text = rsT.Fields(9).Value
            Else
                HatchPlacedlbl.Text = rsT.Fields(7).Value
            End If
            rsT.Close()
        End If
    End Sub



Thanks!

-Navicerts
0
Navicerts
Asked:
Navicerts
1 Solution
 
iboutchkineCommented:
use System.DbNull.Value


If IsDBNull(dtrWhatever("colname")) Then
    lblWhatever.text="nill"
Else
    lblWhatever.text=dtrWhatever("colname")
End If
======================================
another
If Not dr.Item("SetupTime") Is System.DBNull.Value Then
     dTotalSetupTime = dr.Item("SetupTime")
Else
     dTotalSetupTime = 0
End If


If you are using IsDbNull(...) you are calling the function and need to import Microsoft.VisualBasic
namespace

I prefere to use System.DBNull.Value
0
 
NavicertsAuthor Commented:
It seems to be working (used the second idea), heres my current function...

Thank You!

-Navicerts    

Private Sub GetTotals()
        Dim rsT As New ADODB.Recordset
        Dim Cmd As New ADODB.Command
        Dim Counter As Integer
        Dim LineNumber As Integer
        LineNumber = CurrentLine.SelectedValue

        conn.ConnectionString = ConnectionString
        conn.Open()

        SQL = "SELECT SumByLine.[Line Number], SumByLine.SetNTotal, SumByLine.InfertileTotal, SumByLine.[Early DeadTotal], SumByLine.HatchedTotal, SumByLine.CulledTotal, SumByLine.SurplusTotal, SumByLine.Placed_STotal, SumByLine.Placed_MTotal, SumByLine.Placed_FTotal FROM SumByLine WHERE (((SumByLine.[Line Number])=" & LineNumber & "))"
        rsT.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockUnspecified)
        Counter = rsT.Fields(0).Value
        rsT.Close()
        If Counter > 0 Then
            rsT.Open(SQL, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockUnspecified)
            If rsT.Fields(1).Value Is System.DBNull.Value Then
                HatchSetlbl.Text = 0
            Else
                HatchSetlbl.Text = rsT.Fields(1).Value
            End If
            'HatchSetlbl.Text = rsT.Fields(1).Value
            If rsT.Fields(2).Value Is System.DBNull.Value Then
                HatchInflbl.Text = 0
            Else
                HatchInflbl.Text = rsT.Fields(2).Value
            End If
            'HatchInflbl.Text = rsT.Fields(2).Value
            If rsT.Fields(3).Value Is System.DBNull.Value Then
                HatchEDlbl.Text = 0
            Else
                HatchEDlbl.Text = rsT.Fields(3).Value
            End If
            'HatchEDlbl.Text = rsT.Fields(3).Value
            If rsT.Fields(4).Value Is System.DBNull.Value Then
                HatchHatchlbl.Text = 0
            Else
                HatchHatchlbl.Text = rsT.Fields(4).Value
            End If
            'HatchHatchlbl.Text = rsT.Fields(4).Value
            If rsT.Fields(5).Value Is System.DBNull.Value Then
                HatchCulllbl.Text = 0
            Else
                HatchCulllbl.Text = rsT.Fields(5).Value
            End If
            'HatchCulllbl.Text = rsT.Fields(5).Value
            If rsT.Fields(6).Value Is System.DBNull.Value Then
                HatchSurpluslbl.Text = 0
            Else
                HatchSurpluslbl.Text = rsT.Fields(6).Value
            End If
            'HatchSurpluslbl.Text = rsT.Fields(6).Value
            If MPlacedIn.Visible = True Then
                If rsT.Fields(8).Value Is System.DBNull.Value Then
                    HatchMaleslbl.Text = 0
                Else
                    HatchMaleslbl.Text = rsT.Fields(8).Value
                End If
                'HatchMaleslbl.Text = rsT.Fields(8).Value
                If rsT.Fields(9).Value Is System.DBNull.Value Then
                    HatchFemlbl.Text = 0
                Else
                    HatchFemlbl.Text = rsT.Fields(9).Value
                End If
                'HatchFemlbl.Text = rsT.Fields(9).Value
            Else
                If rsT.Fields(7).Value Is System.DBNull.Value Then
                    HatchPlacedlbl.Text = 0
                Else
                    HatchPlacedlbl.Text = rsT.Fields(7).Value
                End If
                'HatchPlacedlbl.Text = rsT.Fields(7).Value
            End If
            rsT.Close()
        End If
conn.close()
    End Sub
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now