With Block Error when DataReader is null ... 1

[VBRookie]

Hi I keep hitting this error when my sql statement doesn't return any data ...

Object variable or With block variable not set. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NullReferenceException: Object variable or With block variable not set.

I'm dumping the rows from the sql statement into a datareader. Here is the code that is generating the error:

   If dtRdr.HasRows Then

The datareader is coming back null and when it hits .HasRows it gives this error. How do I check for a null datareader first? I've tried the following with no success:

If Not (dtRdr Is DBNull.Value) Then

still barks on that ... Please advise.

Many Thanks,
- VB Rookie

 

[Isadore]

VB: I'm sure there are a number of ways to accomplish this task. What I do is to check a DataSet populated by a DataAdapter prior to drilling a table for records (this might not help in your situation but thought I'd share it with you at any rate).

Once the DataSet has been filled by the DataAdapter I use this line to continue:

If myDs.Tables("myTable").Rows.Count = 0 Then
 Exit Sub
Else
 'continue
End If

I'm sure there's a more eloquent way to check your statement and a solution that banks on a direct read of the status of the DataReader. i.e., some modfication of your statement:

If Not (dtRdr Is DBNull.Value) Then
'...
End If

..might convert to:

If dtRdr("RecordCount") > 0 Then, etc. Using a NULL to approach this problem may not reconcile with the dtRdr as an object.

Another combination:

If Not IsDBNull(dr.item("Field1") Then
    myText.Text = CType(dr.Item("Field1"), String)
Else
    myText.Text = ""
End If

VB: Try running a quick Search at Google Groups using "NULL" + "Datareader". I browsed for a few minutes and saw several approaches to this problem.

 

[VBRookie]

Thanks Isadore,

I googled this and came up with some of what you have but I'll try the new ones and see if any help ...

Manu Thanks!
- VB Rookie

 

[Isadore]

VB: There's a nice solution at thread855-830961.

In the case above I should add that in my case I "know" when/when not to expect the possibility of a NULL showing up; hence the use of the DataSet which works well -- however, on 2d thought, I wouldn't use something like this routinely since DataSets are a bit demanding on the server, etc.

 

[Isadore]

VB: One more you might want to look at: thread855-390489

 

[VBRookie]

Thanks Isadore!

- VB Rookie