NextResult Problem....ARGH>>>

8 Dec 2004 10:09 PM

mdjhome

Greetings,

I have an issue that I just can seem to get past today. I continually get
the following error:

Invalid attempt to NextResult when reader is closed

This is happening when calling this code, which is our standard block around
a simple fill function:

    If cnx.State = ConnectionState.Open Then
            cnx.Close()
        End If
        cnx.Open()
        Try
           adapterTimeRecords.Fill(DstTimeRecords.app__TimeRecords)
        Catch ex As SqlClient.SqlException
            MSCError.MSCErrorSQL(ex)
        Catch es As SystemException
            MSCError.MSCErrorSystem(es)
        Catch ea As Exception
            MSCError.MSCError(ea)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try

The select command is a simple SQL Statement where the following is the text
of that:

SELECT DISTINCT IDCODE_PERSNNEL, SSN, firstname, lastname, sortpjc FROM
app__TimeRecords where idcode_persnnel in (SELECT idcode_persnnel FROM
app__TimeRecords where idcode_ship = 3 AND PayPeriodID = 1 ) ORDER BY
SortPJC, ssn"

Notes:

- The standard code block ensures that the connection is open prior to the
calling of the fill statement (yes, I know that calling fill also opens the
connection automatically, but why leave anything to chance?)

- If I step through this code, when the adapter is in scope, the debug
window does not throw this error but rather gives me '41' which is correct
and what it should be, having tested this in SQL Profiler.

- when it throws the exception, it does not throw it via the
SQLClient.SQLConnection class, but rather the System.Exception class.

This is really stupid weird, as I have this same fill statement used
elsewhere and it always runs fine.

Michael

9 Dec 2004 12:24 AM

W.G. Ryan eMVP

Is there a DataReader open somewhere else on a different thread? Are you
getting this when calling Fill?

--Why leave anything to chance? You don't by letting the adapter take care
of it for you. You are using a Finally block and that's what matter
(although I'd test != ConnnectionState.Closed for future compatibility
issues) but you are adding unncessary code.

Where is the 41 visible? Are you iterating through the table somehwere?

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Show quote

"mdjhome" <mdjhome@nospam.nospam> wrote in message
news:827DC7DC-2594-477C-8077-702E90809B13@microsoft.com...
> Greetings,
>
> I have an issue that I just can seem to get past today. I continually get
> the following error:
>
> Invalid attempt to NextResult when reader is closed
>
> This is happening when calling this code, which is our standard block
around
> a simple fill function:
>
>     If cnx.State = ConnectionState.Open Then
>             cnx.Close()
>         End If
>         cnx.Open()
>         Try
>            adapterTimeRecords.Fill(DstTimeRecords.app__TimeRecords)
>         Catch ex As SqlClient.SqlException
>             MSCError.MSCErrorSQL(ex)
>         Catch es As SystemException
>             MSCError.MSCErrorSystem(es)
>         Catch ea As Exception
>             MSCError.MSCError(ea)
>         Finally
>             If cnx.State = ConnectionState.Open Then
>                 cnx.Close()
>             End If
>         End Try
>
> The select command is a simple SQL Statement where the following is the
text
> of that:
>
> SELECT DISTINCT IDCODE_PERSNNEL, SSN, firstname, lastname, sortpjc FROM
> app__TimeRecords where idcode_persnnel in (SELECT idcode_persnnel FROM
> app__TimeRecords where idcode_ship = 3 AND PayPeriodID = 1 ) ORDER BY
> SortPJC, ssn"
>
> Notes:
>
> - The standard code block ensures that the connection is open prior to the
> calling of the fill statement (yes, I know that calling fill also opens
the
> connection automatically, but why leave anything to chance?)
>
> - If I step through this code, when the adapter is in scope, the debug
> window does not throw this error but rather gives me '41' which is correct
> and what it should be, having tested this in SQL Profiler.
>
> - when it throws the exception, it does not throw it via the
> SQLClient.SQLConnection class, but rather the System.Exception class.
>
> This is really stupid weird, as I have this same fill statement used
> elsewhere and it always runs fine.
>
> Michael
>
>
>
>

9 Dec 2004 1:23 AM

mdjhome

Not any datareader open on another thread. I've been very clean on working
with this dataset in question. I'm putting together a different approach to
see if I can get this approach to work in my scenario.

quasi-code (where dstXXX is the dataset in question):

- open form - fills dstXXX (works just fine)
- user clicks button - form.showdialog to keep on top
- user clicks OK, app writes to database, underlying table of dstXXX
- upon closure of the form, the program does a dstXXX.clear - as I want to
re-read in and do some manipulation with the data from the table before
posting it to a grid
- fill dstXXX - dies a hard death here.

when doing the fill dstXXX (both for the first time at load and the
subsequent action after returning from the child window, these use the same
Sub, so it's not a matter of implementation, as one works.

Here's the other weird thing, if you step through this with the debugger, if
you let it sit for a minute or so, before doing the second fill, it'll run
just fine, no error, but run it release/debug or step thru the debug at a
normal pace, it dies.

Why leave to chance? Depends on how you look at it. Depending upon what
other clown has left an adapter open through an exception which was trapped
without a proper finally block, or just simply left it open with a command
object, I'll always be sure to have a clear execution path for the statements
in the try/catch block, that's the only reason for the leading if/then. It's
keeps sloppy "others" from doing something sloppy and it looking like it came
from your code. More of a philosophical debate if you should handle
sloppineness or eliminate it, that is subject of a blog somewhere.

Do you know something about the connectionstate.closed that I don't? I
think that I may try that, would appreciate any info that may be had on that.

As for the 41, this is the number of records returned when you are looking
at this fill statement.

Show quote

"W.G. Ryan eMVP" wrote:

> Is there a DataReader open somewhere else on a different thread? Are you
> getting this when calling Fill?
>
> --Why leave anything to chance? You don't by letting the adapter take care
> of it for you. You are using a Finally block and that's what matter
> (although I'd test != ConnnectionState.Closed for future compatibility
> issues) but you are adding unncessary code.
>
> Where is the 41 visible? Are you iterating through the table somehwere?
>
> --
> W.G. Ryan MVP (Windows Embedded)
>
> TiBA Solutions
> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> "mdjhome" <mdjhome@nospam.nospam> wrote in message
> news:827DC7DC-2594-477C-8077-702E90809B13@microsoft.com...
> > Greetings,
> >
> > I have an issue that I just can seem to get past today. I continually get
> > the following error:
> >
> > Invalid attempt to NextResult when reader is closed
> >
> > This is happening when calling this code, which is our standard block
> around
> > a simple fill function:
> >
> >     If cnx.State = ConnectionState.Open Then
> >             cnx.Close()
> >         End If
> >         cnx.Open()
> >         Try
> >            adapterTimeRecords.Fill(DstTimeRecords.app__TimeRecords)
> >         Catch ex As SqlClient.SqlException
> >             MSCError.MSCErrorSQL(ex)
> >         Catch es As SystemException
> >             MSCError.MSCErrorSystem(es)
> >         Catch ea As Exception
> >             MSCError.MSCError(ea)
> >         Finally
> >             If cnx.State = ConnectionState.Open Then
> >                 cnx.Close()
> >             End If
> >         End Try
> >
> > The select command is a simple SQL Statement where the following is the
> text
> > of that:
> >
> > SELECT DISTINCT IDCODE_PERSNNEL, SSN, firstname, lastname, sortpjc FROM
> > app__TimeRecords where idcode_persnnel in (SELECT idcode_persnnel FROM
> > app__TimeRecords where idcode_ship = 3 AND PayPeriodID = 1 ) ORDER BY
> > SortPJC, ssn"
> >
> > Notes:
> >
> > - The standard code block ensures that the connection is open prior to the
> > calling of the fill statement (yes, I know that calling fill also opens
> the
> > connection automatically, but why leave anything to chance?)
> >
> > - If I step through this code, when the adapter is in scope, the debug
> > window does not throw this error but rather gives me '41' which is correct
> > and what it should be, having tested this in SQL Profiler.
> >
> > - when it throws the exception, it does not throw it via the
> > SQLClient.SQLConnection class, but rather the System.Exception class.
> >
> > This is really stupid weird, as I have this same fill statement used
> > elsewhere and it always runs fine.
> >
> > Michael
> >
> >
> >
> >
>
>
>

9 Dec 2004 7:53 AM

Kevin Yu [MSFT]

Hi Michael,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're receiving an exception which
says the DataReader has been closed when doing a Fill. If there is any
misunderstanding, please feel free to let me know.

This is very weird since you don't have other DataReader on the same
connection in another thread. Is this a single threaded app? Is there any
other thread running? Also could you please try to replace the command text
with the following SELECT statement to see if the error still appears?

SELECT IDCODE_PERSNNEL, SSN, firstname, lastname, sortpjc FROM
app__TimeRecords

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."