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
LVL 7
NavicertsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.