All worked fine yesterday..sqldatareader yet again

[JAKSupport]

I asked a question yesterday and suddenly before I got down to the answer the app was working fine, went good through many tests...Now it blows up again complaining that the datareader cannot be read because it is closed...How so?

here is the calling code:

Dim db As New Database.SQLDatabase("User ID=test;Password=test;Initial Catalog=QuikFix;Data Source=test;")
 
        Dim dr As SqlDataReader
        Dim da As SqlDataAdapter
        Dim ds As DataSet
 
        db.SetSQLDBCommand("select_ticket_by_id") 'pass procedure name
        db.AddSQLDBCmdParameter("@TicketID", SqlDbType.BigInt, 3357) 'pass a parameter
        dr = db.UseSQLDBExecuteReader() 'grab the data
 
[B]        While dr.Read()[/B]
            MsgBox(dr("TicketID"))
        End While
        dr.Close()
 
        db.SetSQLDBCommand("select_ticket_ids") 'another procedure
        db.AddSQLDBCmdParameter("@ClientID", SqlDbType.Int, 1) 'parameters
        db.AddSQLDBCmdParameter("@FacilityID", SqlDbType.BigInt, 1)
        db.AddSQLDBCmdParameter("@bOpen", SqlDbType.Bit, 1)
        db.AddSQLDBCmdParameter("@bOnHold", SqlDbType.Bit, 1)
 
        dr = db.UseSQLDBExecuteReader() 'execute it
 
        While dr.Read()
            MsgBox(dr("TicketID"))
        End While
        dr.Close()
 
        db.SetSQLDBCommand("select_ticket_by_id") 'procedure
        db.AddSQLDBCmdParameter("@TicketID", SqlDbType.BigInt, 3357) 'param
        ds = db.UseSQLDBDataSet() 'works with datasets as well
 
        Dim dd As New ComboBox
        Controls.Add(dd)
 
        With dd
            .DataSource = ds.Tables(0)
            .DisplayMember = "TicketID"
            .ValueMember = "TicketID"
        End With

It errors out in the first while loop when it tries to read dr.
Ill post the SQLDatabase class in a seperate reply to this post since it is lengthy.

[JAKSupport]

SQL Database class

Imports System.Data.SqlClient
Public Class SQLDatabase
    Private mSQLDBConnString As String
    Private mSQLDBCmd As SqlCommand
    Private mSQLDBConnector As SqlConnection
    Public Sub New(ByVal pConnStr As String)
        mSQLDBConnString = pConnStr
        Try
            mSQLDBConnector = New SqlConnection(pConnStr)
            mSQLDBConnector.Open()
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        End Try
    End Sub
    Public Property SQLDBConnString()
        Get
            Return mSQLDBConnString
        End Get
        Set(ByVal Value)
            mSQLDBConnString = Value
        End Set
    End Property
    Public Sub CloseSQLDB()
        If IsSQLDBOpen() Then
            mSQLDBConnector.Dispose()
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End If
    End Sub
    Public Sub OpenSQLDB()
        If Not IsSQLDBOpen() Then
            If mSQLDBConnector Is Nothing Then
                'not even a valid database object
                'try to recreate the object, object recreated if a string exists
                If mSQLDBConnString.Length > 0 Then
                    mSQLDBConnector = New SqlConnection(mSQLDBConnString)
                    mSQLDBConnector.Open()
                Else
                    'no string
                    Throw New Exception("Database null, please create a valid SQLDB object!")
                    Exit Sub
                End If
            Else
                mSQLDBConnector.Open()
            End If
        End If
    End Sub
    Public Function IsSQLDBOpen() As Boolean
        Return mSQLDBConnector.State = ConnectionState.Open
    End Function
    Public Function SQLDBUsed() As String
        Return mSQLDBConnector.Database
    End Function
    Public Sub SetSQLDBCommand(ByVal pStrSprocName As String)
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
 
        mSQLDBCmd = New SqlCommand(pStrSprocName, mSQLDBConnector)
        With mSQLDBCmd
            .CommandType = CommandType.StoredProcedure
        End With
    End Sub
    Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)
            End With
            Return lRead
        Catch ex As Exception
                      Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End Try
    End Function
    Public Function UseSQLDBDataSet() As DataSet
        Dim lDs As DataSet
        Dim lDataAdapter As SqlDataAdapter
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                lDataAdapter = New SqlDataAdapter
                With lDataAdapter
                    .SelectCommand = mSQLDBCmd
                End With
                lDs = New DataSet
                lDataAdapter.Fill(lDs)
            End With
            Return lDs
        Catch ex As Exception
                       Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            lDs = Nothing
            lDataAdapter = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End Try
    End Function
    Public Sub UseSQLDBExecuteNonQuery()
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Sub
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
                        Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End Try
    End Sub
    Public Function UseSQLDBExecuteScalar() As Integer
        Dim intNumRows As Integer
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                intNumRows = CInt(.ExecuteScalar().ToString())
            End With
            UseSQLDBExecuteScalar = intNumRows
        Catch ex As Exception
                        Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End Try
    End Function
    Public Sub DeleteSQLDBCmd()
        mSQLDBCmd.Dispose()
        mSQLDBCmd = Nothing
    End Sub
    Public Sub AddSQLDBCmdParameter(ByVal pName As String, ByVal pType As SqlDbType, ByVal pVal As Object)
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetDBCommand to initialize SQLCommand object!")
            Exit Sub
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                .Parameters.Add(pName, pType).Value = pVal
            End With
        Catch ex As Exception
                       Throw New Exception(ex.ToString())
        End Try
    End Sub
    Public Sub ClearSQLDBCmdParameters()
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetDBCommand to initialize SQLCommand object!")
            Exit Sub
        End If
        With mSQLDBCmd
            .Parameters.Clear()
        End With
    End Sub
End Class

[kleinma]

maybe add an

If Dr.HasRows then

before you attempt to read it?

[JAKSupport]

Tried that before to but it complains that an invalid attempt was made when the reader was closed. I know there are rows though, when it calls the UseSQLDBExecuteReader() function in my class it pulls the data and in debug mode HasRows = True...
I even ran the query in SQL Query Analyzer and it returned the row that I expected...

Why does it complain the reader is closed ?

[JAKSupport]

Here is the strange thing...
If I am in debug mode and I move the yellow arrow from the debug mode to the end try in that function:

Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)
            End With
            Return lRead
        Catch ex As Exception
                      Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
 
        End Try
    End Function

So it skips the finally portion it works absolutely fine...so one would think ok let me try getting rid of the entire finally statement so I even tried this:

Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)
            End With
            Return lRead
        Catch ex As Exception
                      Throw New Exception(ex.ToString())
        End Try
    End Function

Seems logical right? Wrong, it doesnt work with this either!!! Explain that one to me...it works ONLY when you move the debugger to the end try ?????

Umm...WHAT!!~~ :|

[JAKSupport]

Klienma did that make sense what I posted, or are you as stumped as me ?

[kleinma]

what happens if you step through your UseSQLDBExecuteReader method (using F8) one line at a time?

[JAKSupport]

Same thing, explain this a step by step F8 / F11 going through the debug line by line I get the same error, If I move the debugger by dragging and dropping the yellow line to the end try before the finally statement it works FINE...ending the code..
If I delete the finally statement entirely and just run the application OR even step through the code line by line it FAILS!!!

I dont get it at all..cant be the finally statement, it cant be deleting the finally statement, it only works when I move the debugger past it..it makes no sense...

[JAKSupport]

Do you run sql server?
Can you try my code, simply compile the class into a dll, reference it in say a web form, create a simple web form with one button and place that code that I posted in my first thread of this topic. Simply replace the stored procedure name with one of your own procedures, etc...

[kleinma]

Perhaps what is going on is the DataReader is being closed because you are closing the DB connection associated with it?

That is the only thing that seems to make any sense. You are closing the connection to the database, which the DR needs, so the DR closes as well??

That is just a guess, but it seems to make sense... set a watch on your lRead.IsClosed property when you are in your UseSQLDBExecuteReader, and see if it changes from open to closed as your finally runs. Also even if you remove the finally, the connection is going out of scope when your function ends, which would explain why even if you remove the finally it still happens?

[JAKSupport]

Nope not the case, I could go line by line and close the connection (note the code where I set the Command Behavior:

mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)

I could go line by line and LET IT close the db connection and then move the debugger to line to the end try and the function works fine, but if I remove that code of closing the db connection and run the code it errors out...driving me nuts !

[JAKSupport]

Perhaps what is going on is the DataReader is being closed because you are closing the DB connection associated with it?

That is the only thing that seems to make any sense. You are closing the connection to the database, which the DR needs, so the DR closes as well??

That is just a guess, but it seems to make sense... set a watch on your lRead.IsClosed property when you are in your UseSQLDBExecuteReader, and see if it changes from open to closed as your finally runs. Also even if you remove the finally, the connection is going out of scope when your function ends, which would explain why even if you remove the finally it still happens?

Why would it work then if I move the debugger doesnt it still lose the scope since the function ends?
That doesnt make any sense its valid when I move the debugger to the "end try" line of the finally statement without hitting the actual dispose and close methods...yet if I even comment them out and run the same code..it fails?

How would you go around returing lRead then ? I do not want to maintain a connection to the db as this reduces the performance of this entire class...

[kleinma]

I will see if I can run your code here and get a better idea of the issue...

[kleinma]

you are using 1.1 or 2.0?

[JAKSupport]

Im running 1.1

[JAKSupport]

Any luck with it, you dont have to include all that code (the callee code front end / ui code) you can just keep the first part upto the first while loop...should compile and run with no issues. Just change the sproc name, and pass a parameter or not to it, I dont think it will matter if u use the parameter function, if your procedure expects one use it otherwise dont.

Im still puzzled.

[kleinma]

well what I have found so far is your DataReader closes when you call
mSQLDBConnector.Close()
in your finally section

[kleinma]

I don't think there is a way to return a datareader without it's connection still open and alive... DataReaders aren't disconnected recordsets like a datatable is, so the reader needs the connection in order to dish records out.

When someone creates an instance of your class, you open the DB right away, so why close it after they use one method of your class? Leave it open and have your class implement IDisposable. Then when they call dispose on your class, you close and kill off any objects that are still alive.

[JAKSupport]

This doesn't make any sense it worked fine yesterday, same code...
The CommandBehavior should handle the disconnection though.
It still doesnt make sense about the entire app working when moving the debug portion down and then when I GET RID of the entire finally statement even the connection to the database why does it not work then ?

[JAKSupport]

I don't think there is a way to return a datareader without it's connection still open and alive... DataReaders aren't disconnected recordsets like a datatable is, so the reader needs the connection in order to dish records out.

When someone creates an instance of your class, you open the DB right away, so why close it after they use one method of your class? Leave it open and have your class implement IDisposable. Then when they call dispose on your class, you close and kill off any objects that are still alive.

But even if I DONT close the db (get rid of the entire finally statement) it does not work...
So again I ask how does one return lRead...This was all production code last night, why would it suddenly not work. I think it goes deeper into what happens in the internals of the connection (pooling , etc).

[kleinma]

ok, I found if you do this in your finally

Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            'mSQLDBConnector.Close()
            'mSQLDBConnector = Nothing
        End Try

it works. The connection remains open and all looks well. When you close the returned datareader object, it will close the connection. That should sort things out for you right?

[JAKSupport]

klienma if that is the case, lets assume we do keep the connection alive...get rid of the .close on the connection object, and the code still fails...so that is what I dont understand.

To answer your second question why the connection is not kept alive, is for memory and performance reasons. If I were to dispose of the object I would need to reconnect and reestablish the database. The object may be persistant across an entire application, therefore for performance reasons the object is closed. THis is just the scenerio, but I'm more wondering why if you do not close the connection why it doesnt work still ?

[JAKSupport]

Doesnt work on my system I got rid of the closing and setting the db connection class to nothing and still no go ?

[JAKSupport]

Here's what I have:

Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection) 
            End With
            
        Catch ex As Exception
                      Throw New Exception(ex.ToString())
Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
     '       mSQLDBConnector.Close()
      '      mSQLDBConnector = Nothing
     
        End Try
Return lRead
    End Function

And the calling code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim db As New Database.SQLDatabase("User ID=QuikFix;Password=ibm123;Initial Catalog=QuikFix;Data Source=HERCULES;")
 
        Dim dr As SqlDataReader
        Dim da As SqlDataAdapter
        Dim ds As DataSet
 
        db.SetSQLDBCommand("select_ticket_by_id") 'pass procedure name
        db.AddSQLDBCmdParameter("@TicketID", SqlDbType.BigInt, 3357) 'pass a parameter
        dr = db.UseSQLDBExecuteReader() 'grab the data
 
        If dr.HasRows() Then
            While dr.Read()
                MsgBox(dr("TicketID"))
            End While
            dr.Close()
        End If
 
 
        db.SetSQLDBCommand("select_ticket_ids") 'another procedure
        db.AddSQLDBCmdParameter("@ClientID", SqlDbType.Int, 1) 'parameters
        db.AddSQLDBCmdParameter("@FacilityID", SqlDbType.BigInt, 1)
        db.AddSQLDBCmdParameter("@bOpen", SqlDbType.Bit, 1)
        db.AddSQLDBCmdParameter("@bOnHold", SqlDbType.Bit, 1)
 
        dr = db.UseSQLDBExecuteReader() 'execute it
 
        While dr.Read()
            MsgBox(dr("TicketID"))
        End While
        dr.Close()
 
       
    End Sub

And that works for you ? So why doesnt it work for me ???

[kleinma]

no the one that works for me, the return is NOT outside the try... here is the method I used that worked

Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)
            End With
            Return lRead
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            'mSQLDBConnector.Close()
            'mSQLDBConnector = Nothing
        End Try
    End Function

[JAKSupport]

I replaced mine with yours:

--------------------------------------------------------------------------------
    Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = mSQLDBCmd.ExecuteReader(CommandBehavior.CloseConnection)
            End With
            Return lRead
        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
            'mSQLDBConnector.Close()
            'mSQLDBConnector = Nothing
        End Try
    End Function

And my calling code is above and I still get the error...see what Im saying how this is driving me insane .

[kleinma]

Don't know what to tell you man. It works for me just fine... I called a storedproc in one of my databases, and it returned the data I expected it to.. no issues...

only thing I changed in the calling code was that I needed to pass 2 params, and I changed BitInt to Int because BitInt is for 64bit integers...

[JAKSupport]

Err...

These sorts of things piss me off , but you were definately helpful so dont be sorry. I think this is something very minor and prolly could be an issue on my pc (uhh ya thats it ). So im going to call it a night...because I have to deal with datareaders in this class I think Im going to need to Implement IDisposable and just handle the close for this specific case...do you think thats a good idea ?

Thanks again Kleinma.

[kleinma]

just as a sanity check, you may want to reboot your PC incase anything is acting screwy with connection pooling.. you probably have run this code several times on your machine today...

a fresh reboot should clear that up if it is an issue...

[JAKSupport]

:| Umm after a reboot it is working normally now :|...wow...I have to implement idisposable either way tomorrow...unbelievable..this whole time I thouhgt it was the cold . I wasted the whole day . Had to recompile the class as well.

[kleinma]

well better late than never... 1 day isnt bad.. I spent 2 weeks debugging a problem once, and it wasn't even my code.. friggin zone alarm was screwing up my PC even though I had fully disabled all its features

[JAKSupport]

Err...

Now I tried Implementing IDisposable...
and that is being a bug too...

Imports System.Data.SqlClient
Public Class SQLDatabase
    Implements IDisposable
    Private disposed As Boolean = False
    Private mSQLDBConnString As String
    Private mSQLDBCmd As SqlCommand
    Private mSQLDBConnector As SqlConnection
    Private Sub Dispose() Implements IDisposable.Dispose
        If mSQLDBCmd Is Nothing Then
            'no need
        Else
 
            mSQLDBCmd.Dispose()
            mSQLDBCmd = Nothing
        End If
 
        If mSQLDBConnector Is Nothing Then
            'no need to deallocate
        Else
            'coded by kumar
            mSQLDBConnector.Close()
            mSQLDBConnector.Dispose()
            mSQLDBConnector = Nothing
        End If
 
    End Sub

What is so wrong here? VS.net 1.1 Im using..it is placing a blue line under the Dispose() as well as the IDisposable.Dispose portion...Not sure why, am I not implementing it correctly. I could of sworn the IDisposable interface has ONE sub routine and that is namely Dispose()...

So whats the dilly ?

[JAKSupport]

O my lord..never mind had two signatures of the dispose method...wow Im on a roll today I think im going home now !