"Bryan" <bmomal***@gmail.com> wrote in message
news:1150986232.272243.79050@m73g2000cwd.googlegroups.com...
> I'm sure this is a basic question, but I'm just not getting it.
>
> I'm using a Try / Catch block to surround my database access code.  For
> example:
>
> Try
>  cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
>  cmdDBCommand.CommandType = CommandType.StoredProcedure
>  cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
>
>  dtrReader = cmdDBCommand.ExecuteReader
>
>  If dtrReader.HasRows Then
>    dtrReader.Read()
>    bResult = dtrReader.Item("BillMonthly")
>  End If
>  dtrReader.Close()
> Catch ex As Exception
>  bResult = False
> End Try
>
> The problem is this -- if there is a problem with the try block, we
> jump to the Catch and the reader is not closed.
>
> If I just blindly close it in the catch, I may get an error saying that
> it's not open.
>
> So, how can I safely check the reader to see if it needs to be closed?
>
> Thanks in advance for your help!
>
>  - Bryan
>

> "Bryan" <bmomal***@gmail.com> wrote in message
> news:1150986232.272243.79050@m73g2000cwd.googlegroups.com...
> > I'm sure this is a basic question, but I'm just not getting it.
> >
> > I'm using a Try / Catch block to surround my database access code.  For
> > example:
> >
> > Try
> >  cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> >  cmdDBCommand.CommandType = CommandType.StoredProcedure
> >  cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
> >
> >  dtrReader = cmdDBCommand.ExecuteReader
> >
> >  If dtrReader.HasRows Then
> >    dtrReader.Read()
> >    bResult = dtrReader.Item("BillMonthly")
> >  End If
> >  dtrReader.Close()
> > Catch ex As Exception
> >  bResult = False
> > End Try
> >
> > The problem is this -- if there is a problem with the try block, we
> > jump to the Catch and the reader is not closed.
> >
> > If I just blindly close it in the catch, I may get an error saying that
> > it's not open.
> >
> > So, how can I safely check the reader to see if it needs to be closed?
> >
> > Thanks in advance for your help!
> >
> >  - Bryan
> >
>
> Check to see if your SqlDataReader is open before attempting to close.
>
> The IsClosed property may be useful.
>
>       Visual Basic (Usage)
> Dim instance As SqlDataReader
> Dim value As Boolean
>
> value = instance.IsClosed
>
>
>
> --
> Peter [MVP Visual Developer]
> Jack of all trades, master of none.

"Bryan" <bmomal***@gmail.com> wrote in message
news:1150991981.177090.176770@g10g2000cwb.googlegroups.com...
> Peter,
>
> Thanks for the response.  That approach is what I had been doing, but
> it returns:
>
> Object reference not set to an instance of an object.
>
> Here's the test example:
>
>
> Dim dtrReader As SqlDataReader
>
> Try
>       dtrReader.Read()
> Catch ex As Exception
> Finally
>        If Not dtrReader.IsClosed Then
>          dtrReader.Close()
>        End If
> End Try
>
> Since dtrReader hasn't been instanciated yet, just defined, the
> IsClosed won't work.
>
> I guess I need to figure out if it's been instanciated first, then if
> it needs to be closed, right?
>
> - Bryan
>
Right.

> I'm sure this is a basic question, but I'm just not getting it.
>
> I'm using a Try / Catch block to surround my database access code.  For
> example:
>
> Try
>   cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
>   cmdDBCommand.CommandType = CommandType.StoredProcedure
>   cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
>
>   dtrReader = cmdDBCommand.ExecuteReader
>
>   If dtrReader.HasRows Then
>     dtrReader.Read()
>     bResult = dtrReader.Item("BillMonthly")
>   End If
>   dtrReader.Close()
> Catch ex As Exception
>   bResult = False
> End Try
>
> The problem is this -- if there is a problem with the try block, we
> jump to the Catch and the reader is not closed.
>
> If I just blindly close it in the catch, I may get an error saying that
> it's not open.
>
> So, how can I safely check the reader to see if it needs to be closed?
>
> Thanks in advance for your help!
>
>   - Bryan
>

> Bryan wrote:
> > I'm sure this is a basic question, but I'm just not getting it.
> >
> > I'm using a Try / Catch block to surround my database access code.  For
> > example:
> >
> > Try
> >   cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
> >   cmdDBCommand.CommandType = CommandType.StoredProcedure
> >   cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
> >
> >   dtrReader = cmdDBCommand.ExecuteReader
> >
> >   If dtrReader.HasRows Then
> >     dtrReader.Read()
> >     bResult = dtrReader.Item("BillMonthly")
> >   End If
> >   dtrReader.Close()
> > Catch ex As Exception
> >   bResult = False
> > End Try
> >
> > The problem is this -- if there is a problem with the try block, we
> > jump to the Catch and the reader is not closed.
> >
> > If I just blindly close it in the catch, I may get an error saying that
> > it's not open.
> >
> > So, how can I safely check the reader to see if it needs to be closed?
> >
> > Thanks in advance for your help!
> >
> >   - Bryan
> >
>
> Use a nested try block:
>
> Try
>    cmdDBCommand = New SqlCommand("dbo.GetMonthlyBilling", oConnection)
>    cmdDBCommand.CommandType = CommandType.StoredProcedure
>    cmdDBCommand.Parameters.Add("@CustomerID", iCustomerID)
>    dtrReader = cmdDBCommand.ExecuteReader
>
>    Try
>      If dtrReader.HasRows Then
>        dtrReader.Read()
>        bResult = dtrReader.Item("BillMonthly")
>      End If
>    Catch ex As Exception
>      'Errors from dtrReader goes here
>      bResult = False
>    Finally
>      'Close reader
>      dtrReader.Close()
>    End Try
> Catch ex As Exception
>    'Errors from cmdDBCommand goes here
>    bResult = False
> Finally
>    'Don't forget to dispose the Command
>    cmdDBComand.Dispose
> End Try