[RESOLVED] Checking database field

[Tinbeard]

I'm trying to populate a custom data table with info from some tables in a dataset, the problem is I can't seem to check if the field is empty (if it is I just want to pass over it and grab the next row).

This is the code I'm using but it just doesn't function as I'd expected.

For rowCount=1 to objDataSet.Tables("medical").rows.count
        If objDataSet.Tables("medical").rows(rowCount-1).Item(condition) IS Nothing then
            expireDate=""
        ELSE
            expireDate=objDataSet.Tables("medical").rows(rowCount-1).Item(condition)
        End If
 
        if expireDate >= dateToday then
            strName=objDataSet.Tables("medical").rows(rowCount-1).Item("FirstName") & " " & objDataSet.Tables("medical").rows(rowCount-1).Item("LastName")
            objDataRow=dtblExpired.NewRow()
            objDataRow.Item("Name")= strName
            dtblExpired.Rows.Add(objDataRow)
        End If
    Next

[wild_bill]

try
objDataSet.Tables("medical").rows(rowCount-1).Item(condition) = Nothing then

[mar_zim]

or
objDataSet.Tables("medical").rows(rowCount-1)("condition") = Nothing

i assume "condition" is a column name

[jmcilhinney]

A null field in a DataRow is not equal to Nothing. It is equal to DBNull.Value. Also, if "expireDate" is a Date variable then why are you setting it to an empty string? How about this for some slighly neater code:

For Each row As DataRow In objDataSet.Tables("medical").Rows
    If row(condition) <> DBNull.Value AndAlso CDate(row(condition)) >= Date.Today Then
        objDataRow = dtblExpired.NewRow()
        objDataRow("Name") = row("FirstName") & " " & row("LastName")
        dtblExpired.Rows.Add(objDataRow)
    End If
Next row

This code is easier to read and more efficient because the number of property accesses and method calls is smaller. Also, the overuse of local variables can often make code harder to understand rather than easier.

[Tinbeard]

Thanks for the help on this, I'm new to VB.NET and I'm starting to look at my code in a different light

However I'm getting this error message with your code jmcilhinney, can you take a look and advise me please.

BC31080: Operator '<>' is not defined for types 'System.Object' and 'System.DBNull'. Use 'Is' operator to compare two reference types.

Source Error:

Line 84: For Each row As DataRow In objDataSet.Tables("medical").Rows
Line 85: If row(condition) <> DBNull.Value AndAlso CDate(row(condition)) >= Date.Today Then
Line 86: objDataRow = dtblExpired.NewRow()
Line 87: objDataRow("Name") = row("FirstName") & " " & row("LastName")

[jmcilhinney]

Sorry. Change:

If row(condition) <> DBNull.Value AndAlso CDate(row(condition)) >= Date.Today Then

to

If Not row(condition) Is DBNull.Value AndAlso CDate(row(condition)) >= Date.Today Then

I think that should do the trick. One of the hazards of typing code into a post directly rather than copying from the IDE.

[Tinbeard]

Thanks for your time on this, it's really appreciated, it works a treat and I've learned alot about my code, your solution is so much slicker