SQL Reader

[gv]

Hi all,

I'm trying to exit out of the sub if there is nothing returned. But I'm
having problems evaluating
when nothing is returned. This part doesn't seam to work " If
rdr.IsDBNull(0) Then"


Dim SQLgetNewFiles As String = " Declare @Cnt int " & _
" set @Cnt = (Select count(*) from notes where
FileWasCreated is null) " & _
" If @Cnt <> 0 " & _
" BEGIN " & _
" Select hl7file from notes where FileWasCreated
is null " & _
" EXECUTE InsertIncrementnumber " & _
" End "

SQLNoteCmd = New SqlCommand(SQLgetNewFiles.ToString,
SQLNoteConn)

Dim NewFiles As String
Dim rdr As SqlDataReader = SQLNoteCmd.ExecuteReader()
While rdr.Read()
If rdr.IsDBNull(0) Then
Exit Sub ' Need to just Exit out
Else
NewFiles = NewFiles & rdr.GetString(0)
End If
End While
rdr.Close()


thanks
Gerry

 

[Marina]

What does 'not seem to work' mean?? Is there an error? In which case you
need to tell us the exception message. Does the work not work as expected?
In which case, what do you expect to happen, and what actually does happen?

You have to give specific details when posting.

Regardless of that, you are not closing the data reader if 'Exit Sub'
actually ever gets executed. This is asking for a connection leak. You need
to close the datareader before leaving this method. If the connection is not
used elsewhere in your code, it should be closed before leaving this method
as well.

 

[Guest]

While rdr.Read() would prevent anything inside this to run if there are no
records.
There is no need for testing inside the while loop.

Hope that helps.

 

[Teemu Keiski]

Hi,

As was said, there's no reason to exit at that point because it won't happen
if there are not rows and isDbNull check concerns if the returned field in
that particular row has NULL value (means there must be at least 1 row). So
design the loop so that it does the required stuff only when there are rows
and non-null value and otherwise let it "run though" (remove the other part
of the If, the one doing Exit Sub).

Second thing is that you don't want to leave the DataReader in any
conditions before it gets closed. So you don't want to call Exit Sub, before
you call rdr.Close(), that's the other reason to think the logic that way.