Remove row in db result set

General
1

I have a result set of database query and want to remove the row having NULL value in the 4th column.
Below code doesn’t work.
Can you let me know how to do that?

While Not rs.EOF

If rs.IdxField(4).StringValue = "" Then 
  rs.MoveNext
Else 
  dataList.AddRow("")
End If

For i As Integer = 0 To rs.FieldCount-1
  dataList.Cell(dataList.LastIndex, i) = rs.IdxField(i+1).StringValue
Next
rs.MoveNext

Wend

Thanks.

2

For starters, don’t know for sure which database you’re using (I’m guessing SQLite), but in my SQL Server database code I use the following to check for NULL:

if rs.Field("NotesData").Value.IsNull THEN

There are a lot of posts in this forum that deal with the differences between NULL, Nil, and “”. It’s an education all unto itself! I know I ran into problems originally assuming that “” and NULL were the same thing. Also, check out DatabaseField in the Language Reference to see the difference between using DatabaseField.StringValue [which is a Property(as String)] versus DatabaseField.Value [which is a Property(as Variant)]

3

Why not alter the driving SQL statement to not read those records to begin with?

AND NOTEFIELD IS NOT NULL

Personally I never refer to database fields by index, I ALWAYS use NAME