We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Dealing with Null DB results...

Navicerts
Navicerts asked
on
Medium Priority
206 Views
Last Modified: 2010-04-23
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
Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.