Check if dataset values are NULL

2012-07-05

Question

_{Thursday, July 5, 2012 1:12 PM}

I am trying to iterate through a dataset to check if the database has returned NULL values before trying to use them to format date / time etc. When I run the code without this validation the program doesnt process data any further than where the NULL value has been passed.

I have the following code which still doesnt go past the point where there is a NULL value in one of the columns.

Dim table As DataTable = ShipToDataset2.Tables("CustomerShipTo")
            Dim i As Integer = table.Columns.Count
            MessageBox.Show(CStr(i)) 'which displays the number of columns ok
            For Each dColumn As DataColumn In table.Columns
                If Not (ShipToDataset2.Tables("CustomerShipTo").Columns Is DBNull.Value) Then 'trying to check if some of my column values are null

                    txtShipToCode.Text = ShipToDataset2.Tables("CustomerShipTo").Rows(0)("ShipToCode").ToString()
                    txtShipToName.Text = ShipToDataset2.Tables("CustomerShipTo").Rows(0)("ShipToName").ToString()
                    txtCallFreq.Text = ShipToDataset2.Tables("CustomerShipTo").Rows(0)("CallFrequency_DEV004016").ToString()

                    If CBool(ShipToDataset2.Tables("CustomerShipTo").Rows(0)("IsCallMonday_DEV004016")) = True Then
                        chkCallMon.Checked = True
                    End If

Any help would be appreciated.

Cheers

Dionne

All replies (5)

_{Thursday, July 5, 2012 1:30 PM ✅Answered | 1 vote}

As you assign the datatable to the table variable, you should use it everywhere. Same can be done for the row:

      Dim table As DataTable = ShipToDataset2.Tables("CustomerShipTo")
      Dim i As Integer = table.Columns.Count
      MessageBox.Show(CStr(i)) 'which displays the number of columns ok
      For Each dColumn As DataColumn In table.Columns
         If Not (table.Columns Is DBNull.Value) Then 'trying to check if some of my column values are null
            Dim row = table.Rows(0)

            txtShipToCode.Text = row("ShipToCode").ToString()
            txtShipToName.Text = row("ShipToName").ToString()
            txtCallFreq.Text = row("CallFrequency_DEV004016").ToString()

            If CBool(row("IsCallMonday_DEV004016")) = True Then
               chkCallMon.Checked = True
            End If
         End If
      Next

To check if any row is null, write a loop to check all columns or call a function that does it:

Dim IsAnyColumnNULL = row.ItemArray.Any(Function(col) col Is DBNull.Value)

Armin

_{Wednesday, July 11, 2012 4:13 AM ✅Answered}

Instead of iterating through the columns with your for/next loop try the following technique. You already have named columns so lets take advantage of them.

 Dim table As DataTable = ShipToDataset2.Tables("CustomerShipTo")
 Dim currentRow As DataRow
 currentRow = table.Rows(0)

 txtShipToCode.text = IIf(IsDBNull(currentRow("CustomerShipTo")), "", currentRow("CustomerShipTo"))
 txtShipToName.Text = IIf(IsDBNull(currentRow("ShipToName")), "", currentRow("ShipToName"))
 txtCallFreq.Text = IIf(IsDBNull(currentRow("CallFrequency_DEV004016")), "", currentRow("CallFrequency_DEV004016"))

 chkCallMon.checked = IIf(IsDBNull(currentRow("IsCallMonday_DEV004016")), False, CBool(currentRow("IsCallMonday_DEV004016")))

Basically what we are doing here is substituting an empty string for any NULL values for the columns that represent string data. The checkbox data that is NULL gets a FALSE value.

Ideally you would use a variable to indicate the current row's index but for our example here we are just looking at the first row. (0).

Hope that helps!

Jeff

_{Wednesday, July 11, 2012 2:08 PM ✅Answered}

please check like this

if isdbnull(ShipToDataset2.Tables("CustomerShipTo").Rows(0)("ShipToCode").ToString())=false then

txtShipToCode.Text = ShipToDataset2.Tables("CustomerShipTo").Rows(0)("ShipToCode").ToString()

else

txtShipToCode.Text =""

endif

_{Thursday, July 5, 2012 2:54 PM | 1 vote}

Yea not so strange because you iterate with a for each through the table columns.

That returns a separated column from the collection in the datatable datacolumns with the name dColumn and then you do nothing with it.

You have to turn it around

Foreach row in myDataTable.Rows 
      foreach column in my DataTable.Columns
          if not row(column)  is DbNull.Value then
           ' do what you want to do
           'be aware that if a database returns a null column, it does not return a Null value but an object type from the type. DBNull.Value

          end if
      next
next

Be aware typed in this message so watch small mistakes or typos.

Success
Cor

_{Wednesday, July 11, 2012 9:44 AM}

Thanks for your contribution. Though some hints:

The first action after installing VB should be setting Option Strict On as the default value in the IDE options. For already created projects you can enable it in the project properties, or on a per-file basis by putting "Option Strict On" at the top of a code file. With Option Strict Off, your code may fail because you've activated unsafe programming, disabled compiler checks and enabled the automatic and unattended generation of implicit conversions that may fail or not, or may give unexpected or undesirable results. In addition, it enables late-binding which is slow at run-time and steals the ability of verifying the existence of type members from the compiler.
It is often hard for people trying to help if the code is not compilable and errors have to be fixed first. Data type awareness and correct data type handling are most essential for every programmer.
It is more straighforward to do a comparison instead of calling a function doing the comparsion. In this case it means, use "Is DBNull.Value" instead of calling IsDBNull.
It's good programming style to assign a property vaule to a temp variable instead of accessing the property twice. It's quicker in execution and less prone to errors.

Armin

שתף באמצעות

Check if dataset values are NULL

Question

All replies (5)

משאבים נוספים