none
DbNull, how to handle RRS feed

  • Question

  • In my dataset I have a table of telephone numbers.  With a column of telephoneExtension (AllowDbNull = true, NullValue = (throwexception))

    in my code the following line fails with exception:

    If IsDbNull(curTele.TelephoneExtension) = False (fails with null exception)

    I know that if I change the dataset NullValue to Nothing or Empty then the code will work.

    My question is how can I test for the dbnull value w/o firing the exception, there are field types that cannot be set to nothing or empty e.g. Integers and date/time, so they have to be handled somehow.

    In the meantime, I will be setting the values to Empty and testing for that, but seems awkward.


    Ed Warren Raising sails on masts not masts on sails
    Tuesday, October 25, 2011 12:24 PM

Answers

  • Edward,

    I just realized/remembered that since you are using a Strongly Typed DataSet, you can access it's specific null value property.  Is should be something like:

    If curTele.IsTelephoneExtensionNull Then

    ...or somerthing to that effect.  See what the intellisense gives you after curTele. ---- I'll check in my apps if I have a specific answer.

     

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by Edward Warren Tuesday, October 25, 2011 10:05 PM
    Tuesday, October 25, 2011 6:15 PM
  • If you look at the MyDataSet.Designer.cs file, what gets generated for your Typed DataSet, you'll see that it throws an exception when it can't cast the object (the TelephoneExtension) to a string ...and a DBNull.Value cannot be cast to anything. That is why the Typed DataSet generator also will generate a method for you to check that, the Istelephoneextensionnull() method. Take a look at that method while you're at it ... the syntax is something like this:

    return this.IsNull(this.tableTele.TelephoneExtensionColumn)

    Note that it's passing the column name to the IsNull() method, not the actual value that's in the column. The IsNull() method is a method of the DataRow and behind the scenes it probably does this (which you could do also, rather than use the generated Is...null() method:

    return this[columnname] == DBNull.Value

    In other words, you could use this in your code:

    If curTele(0).Items("TelephoneExtension") = DBNull.Value

    (I think that's correct VB syntax).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 26, 2011 5:10 AM

All replies

  • Hi Edward,

    To check DataSet is Null or not use the below code

      If Not (rsData Is Nothing) Then
            If rsData.Tables(0).Rows.Count > 0 Then

                  -- your code goes here

            End if

       End if

     

    For More information check the below link

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6248efe6-5832-4331-aa79-fd23d75efbb7/#6acc901b-d328-4595-98a5-7116f23f3941


    PS.Shakeer Hussain
    Tuesday, October 25, 2011 12:34 PM
  • Thanks for the response but, it does not address the question.  I'm not interested in finding if the dataset is null, I'm trying to test for a null value in one of the columns, where I have a valid telephone number, but a null telephone extension.  Really I'm trying to figure out why the code throws the exception, when I expect it not to, and to find how to do the dbnull test w/o getting the exception.
    Ed Warren Raising sails on masts not masts on sails
    Tuesday, October 25, 2011 12:40 PM
  • Instead of:

    If IsDbNull(curTele.TelephoneExtension) = False Then
    
    

     

    Use:

    If IsDbNull(curTele.TelephoneExtension("FieldName").Value) Then
    
     'Do something because the value is null
    
    ElseIf Not IsDbNull(curTele.TelephoneExtension("FieldName").Value) Then
    
     'Do something because the value is NOT null
    
    End If
    
    
    

     

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, October 25, 2011 1:22 PM
  • That answer is provided without knowing exactly what "curTele.TelephoneExtension" is.

     

    Edit: ok, I *think* curTele is the DataSet and TelephoneExtension is the DataTable.  Correct?  If so, then try something like:

     

    If IsDbNull(curTele.TelephoneExtension(0)(curTele.TelephoneExtension.TheColumnName, DataRowVersion.Current)) Then
    

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions

    Tuesday, October 25, 2011 1:24 PM
  • That's interesting, I will give it a try, now I wonder what is different between the two approaches. Thanks for the help.

    By the way. 

    Table: Telephones

         Columns:

              TelephoneNumber

             TelephoneExtension

              ......other stuff ...

     

     


    Ed Warren Raising sails on masts not masts on sails
    Tuesday, October 25, 2011 4:34 PM
  • I tried the following line of code, and it also throws the exception.

    If IsDBNull(curTele("TelephoneExtension").value Then

     

    It appears the issue is that at the dataset level the TelephoneExtension NullValue (throw exception) is being fired when one tries to test for a dbnull.  I got the code to run clean by changing this to NullValue (empty) then testing for the Telephone extension = "", but still fail to understand why the exception is thrown.

     


    Ed Warren Raising sails on masts not masts on sails
    Tuesday, October 25, 2011 4:49 PM
  • Edward,

    I just realized/remembered that since you are using a Strongly Typed DataSet, you can access it's specific null value property.  Is should be something like:

    If curTele.IsTelephoneExtensionNull Then

    ...or somerthing to that effect.  See what the intellisense gives you after curTele. ---- I'll check in my apps if I have a specific answer.

     

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by Edward Warren Tuesday, October 25, 2011 10:05 PM
    Tuesday, October 25, 2011 6:15 PM
  • If Not curTele.IsTelephoneExtensionNull Then

    that works, so now I have 1/2 of the answer :).  Still puzzled by why the other code fails, In any case I'm good to test any other fields that fail, thanks for your help.


    Ed Warren Raising sails on masts not masts on sails
    Tuesday, October 25, 2011 10:05 PM
  • Ed, what's the other 1/2 of the question?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 26, 2011 1:36 AM
  • Question #1:  How to make it work?

    Question #2: Why did it fail?

    I do not understand why the code:

    If IsDbNull(curTele.TelephoneExtension) = false,  fails but the code  if not curtele.istelephoneextensionnull works.


    Ed Warren Raising sails on masts not masts on sails
    Wednesday, October 26, 2011 3:20 AM
  • If you look at the MyDataSet.Designer.cs file, what gets generated for your Typed DataSet, you'll see that it throws an exception when it can't cast the object (the TelephoneExtension) to a string ...and a DBNull.Value cannot be cast to anything. That is why the Typed DataSet generator also will generate a method for you to check that, the Istelephoneextensionnull() method. Take a look at that method while you're at it ... the syntax is something like this:

    return this.IsNull(this.tableTele.TelephoneExtensionColumn)

    Note that it's passing the column name to the IsNull() method, not the actual value that's in the column. The IsNull() method is a method of the DataRow and behind the scenes it probably does this (which you could do also, rather than use the generated Is...null() method:

    return this[columnname] == DBNull.Value

    In other words, you could use this in your code:

    If curTele(0).Items("TelephoneExtension") = DBNull.Value

    (I think that's correct VB syntax).


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 26, 2011 5:10 AM
  • Truly the geek-goddess.  That is the answer I was looking for, now it makes sense.  Thanks, I can now sleep better at night :)
    Ed Warren Raising sails on masts not masts on sails
    Wednesday, October 26, 2011 11:48 AM
  • LOL! You're welcome, Ed ... glad I could help.  =0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 26, 2011 1:53 PM