[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.vb.general.discussion

Automation Error when setting grid's DataSource = rs

(Mike Mitchell)

7/19/2012 8:24:00 PM

Extract from the code:

Set Conn = New ADODB.Connection
OpenConnection Conn, DatabaseName

Set rs = New ADODB.Recordset
rs.Open SQL, Conn, adOpenStatic, adLockOptimistic, adAsyncFetch
Set vsFlexHeaders.DataSource = rs ' <----- Automation Error

The Automation Error is always "The object invoked has disconnected
from its clients."

If I repeat exactly the same code (same SQL, same everything) the AE
doesn't occur. If I put in an error handler, then Resume back to the
same line, i.e. Set vsFlexHeaders.DataSource = rs then the error
doesn't occur.

I haven't yet been able to pin down exactly the particular sequence
that leads to the AE, but it's roughly like this:

One of the Access database fields is a Boolean. The other fields are a
mix of numeric, text and date. There are 6 fields in the RecordSet
(this is all MDAC 2.8 BTW)

I can either select all records, approximately 80,000, or I can select
only those records where the Boolean is True. (The Boolean signifies a
special condition on the record(s) in question.)

The sequence is:

1. Select all records.

2. Flag with mouse in grid the records to be downloaded.

3. Select only records where Boolean field = True

4. Automation Error.

I've tried changing adOpenStatic to adOpenKeyset and I've also tried
without the adAsyncFetch (commented it out), but nothing I've tried so
far has made any difference.

I'm going to carry on looking, but has anyone seen this error when
setting a grid's Datasource to a recordset?

BTW, in break mode I've checked that the recordset leading to the AE
is all hunky dory, i.e. if two records were flagged for download, then
the recordset contains two records. It all looks fine.

Never had this AE before.

MM
10 Answers

Henning

7/19/2012 9:54:00 PM

0


"MM" <kylix_is@yahoo.co.uk> skrev i meddelandet
news:mrpg08ta99smhaqpbv3rjkfbk3gh6ddr6n@4ax.com...
> Extract from the code:
>
> Set Conn = New ADODB.Connection
> OpenConnection Conn, DatabaseName
>
> Set rs = New ADODB.Recordset
> rs.Open SQL, Conn, adOpenStatic, adLockOptimistic, adAsyncFetch
> Set vsFlexHeaders.DataSource = rs ' <----- Automation Error
>
> The Automation Error is always "The object invoked has disconnected
> from its clients."
>
> If I repeat exactly the same code (same SQL, same everything) the AE
> doesn't occur. If I put in an error handler, then Resume back to the
> same line, i.e. Set vsFlexHeaders.DataSource = rs then the error
> doesn't occur.
>
> I haven't yet been able to pin down exactly the particular sequence
> that leads to the AE, but it's roughly like this:
>
> One of the Access database fields is a Boolean. The other fields are a
> mix of numeric, text and date. There are 6 fields in the RecordSet
> (this is all MDAC 2.8 BTW)
>
> I can either select all records, approximately 80,000, or I can select
> only those records where the Boolean is True. (The Boolean signifies a
> special condition on the record(s) in question.)
>
> The sequence is:
>
> 1. Select all records.
>
> 2. Flag with mouse in grid the records to be downloaded.
>
> 3. Select only records where Boolean field = True
>
> 4. Automation Error.
>
> I've tried changing adOpenStatic to adOpenKeyset and I've also tried
> without the adAsyncFetch (commented it out), but nothing I've tried so
> far has made any difference.
>
> I'm going to carry on looking, but has anyone seen this error when
> setting a grid's Datasource to a recordset?
>
> BTW, in break mode I've checked that the recordset leading to the AE
> is all hunky dory, i.e. if two records were flagged for download, then
> the recordset contains two records. It all looks fine.
>
> Never had this AE before.
>
> MM

Try a DoEvents before Set vsFlexHeaders?

/Henning

Ralph

7/20/2012 6:28:00 AM

0

On Thu, 19 Jul 2012 21:23:46 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>Extract from the code:
>
>Set Conn = New ADODB.Connection
>OpenConnection Conn, DatabaseName
>
>Set rs = New ADODB.Recordset
>rs.Open SQL, Conn, adOpenStatic, adLockOptimistic, adAsyncFetch
>Set vsFlexHeaders.DataSource = rs ' <----- Automation Error
>
>The Automation Error is always "The object invoked has disconnected
>from its clients."
>
>If I repeat exactly the same code (same SQL, same everything) the AE
>doesn't occur. If I put in an error handler, then Resume back to the
>same line, i.e. Set vsFlexHeaders.DataSource = rs then the error
>doesn't occur.
>
>I haven't yet been able to pin down exactly the particular sequence
>that leads to the AE, but it's roughly like this:
>
>One of the Access database fields is a Boolean. The other fields are a
>mix of numeric, text and date. There are 6 fields in the RecordSet
>(this is all MDAC 2.8 BTW)
>
>I can either select all records, approximately 80,000, or I can select
>only those records where the Boolean is True. (The Boolean signifies a
>special condition on the record(s) in question.)
>
>The sequence is:
>
>1. Select all records.
>
>2. Flag with mouse in grid the records to be downloaded.
>
>3. Select only records where Boolean field = True
>
>4. Automation Error.
>
>I've tried changing adOpenStatic to adOpenKeyset and I've also tried
>without the adAsyncFetch (commented it out), but nothing I've tried so
>far has made any difference.
>
>I'm going to carry on looking, but has anyone seen this error when
>setting a grid's Datasource to a recordset?
>
>BTW, in break mode I've checked that the recordset leading to the AE
>is all hunky dory, i.e. if two records were flagged for download, then
>the recordset contains two records. It all looks fine.
>
>Never had this AE before.
>

Always a painful error.

What's odd in your case is the fact that you get the error on first
attempt. Usually this error occurs on subsquent attempts with the
initial call being error free. This is because while the details will
vary the exact cause always comes down to a vague, highly technical -
"something has changed and is not as expected". <g>

The cure is to try and remove the chances for surprises.

1) Make sure for a given connection session (database, provider, mode)
there is one and only one Connection object for the lifetime of your
program. Create it once (New, CreatObject), then open and close as
needed.

2) Try and clear out the vsFlexGrid before assigning a new source.
If using VirtualData, set it to false
Set vsFlexHeaders.DataSource to Nothing
Then assign the new datasource

[While it likely has nothing to do with this problem, note that just
saying you are using MDAC 2.8, tells us nothing about which version of
ADO you are using - ie, ADO 2.5, ADO 2.8, ... (Except of course you
can't be using ADO 2.1. <g>) I usually use ADO 2.8 with vsFlexGrid 8.]

-ralph

(Mike Mitchell)

7/20/2012 9:22:00 AM

0

On Fri, 20 Jul 2012 01:28:06 -0500, ralph <nt_consulting64@yahoo.com>
wrote:

>On Thu, 19 Jul 2012 21:23:46 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>>Extract from the code:
>>
>>Set Conn = New ADODB.Connection
>>OpenConnection Conn, DatabaseName
>>
>>Set rs = New ADODB.Recordset
>>rs.Open SQL, Conn, adOpenStatic, adLockOptimistic, adAsyncFetch
>>Set vsFlexHeaders.DataSource = rs ' <----- Automation Error
>>
>>The Automation Error is always "The object invoked has disconnected
>>from its clients."
>>
>>If I repeat exactly the same code (same SQL, same everything) the AE
>>doesn't occur. If I put in an error handler, then Resume back to the
>>same line, i.e. Set vsFlexHeaders.DataSource = rs then the error
>>doesn't occur.
>>
>>I haven't yet been able to pin down exactly the particular sequence
>>that leads to the AE, but it's roughly like this:
>>
>>One of the Access database fields is a Boolean. The other fields are a
>>mix of numeric, text and date. There are 6 fields in the RecordSet
>>(this is all MDAC 2.8 BTW)
>>
>>I can either select all records, approximately 80,000, or I can select
>>only those records where the Boolean is True. (The Boolean signifies a
>>special condition on the record(s) in question.)
>>
>>The sequence is:
>>
>>1. Select all records.
>>
>>2. Flag with mouse in grid the records to be downloaded.
>>
>>3. Select only records where Boolean field = True
>>
>>4. Automation Error.
>>
>>I've tried changing adOpenStatic to adOpenKeyset and I've also tried
>>without the adAsyncFetch (commented it out), but nothing I've tried so
>>far has made any difference.
>>
>>I'm going to carry on looking, but has anyone seen this error when
>>setting a grid's Datasource to a recordset?
>>
>>BTW, in break mode I've checked that the recordset leading to the AE
>>is all hunky dory, i.e. if two records were flagged for download, then
>>the recordset contains two records. It all looks fine.
>>
>>Never had this AE before.
>>
>
>Always a painful error.
>
>What's odd in your case is the fact that you get the error on first
>attempt. Usually this error occurs on subsquent attempts with the
>initial call being error free. This is because while the details will
>vary the exact cause always comes down to a vague, highly technical -
>"something has changed and is not as expected". <g>
>
>The cure is to try and remove the chances for surprises.
>
>1) Make sure for a given connection session (database, provider, mode)
>there is one and only one Connection object for the lifetime of your
>program. Create it once (New, CreatObject), then open and close as
>needed.
>
>2) Try and clear out the vsFlexGrid before assigning a new source.
> If using VirtualData, set it to false
> Set vsFlexHeaders.DataSource to Nothing
> Then assign the new datasource
>
>[While it likely has nothing to do with this problem, note that just
>saying you are using MDAC 2.8, tells us nothing about which version of
>ADO you are using - ie, ADO 2.5, ADO 2.8, ... (Except of course you
>can't be using ADO 2.1. <g>) I usually use ADO 2.8 with vsFlexGrid 8.]

I'm doing some proof-reading right now, but I'll get back to the app
later this morning. However, I've just read your post and am confused
about ADO and MDAC. I thought the two terms were synonymous! That is,
if one is using MDAC 2.8, then one is using the latest ADO. I don't
recall ever updating ADO specifically. When I check my References list
in VB6 I have "Microsoft ActiveX Data Objects 2.8 Library" listed and
that is the one I choose nowadays. (I still have references to the
older 2.7, 2.6, 2.5, 2.1 and 2.0 versions.)

MM

Ralph

7/20/2012 1:25:00 PM

0

On Fri, 20 Jul 2012 10:22:08 +0100, MM <kylix_is@yahoo.co.uk> wrote:


>
>I'm doing some proof-reading right now, but I'll get back to the app
>later this morning. However, I've just read your post and am confused
>about ADO and MDAC. I thought the two terms were synonymous! That is,
>if one is using MDAC 2.8, then one is using the latest ADO. I don't
>recall ever updating ADO specifically. When I check my References list
>in VB6 I have "Microsoft ActiveX Data Objects 2.8 Library" listed and
>that is the one I choose nowadays. (I still have references to the
>older 2.7, 2.6, 2.5, 2.1 and 2.0 versions.)
>

The MDAC was only a distribution package for Microsoft's Data Access
Components, which included ADO and more, eg, DAO, RDS, Jet, OLE DB,
ADOX, OLE DB Providers, ODBC Drivers, and Jet drivers. They were
distributed separately because these data components were language and
platform neutral and the underlying technologies were on a development
and repair cycle separate from any O/S or Project. The exact
components included has changed over the years. The biggest change
coming when MS removed the Jet components and bundled them into their
own distribution package.

MDACs were versioned by the highest ADO version they included. Which
made sense, but has caused confusion over the years. All subsquent ADO
library components have included support for all previous ADO
versions. (The exception being ADO 1.5, in spite of the fact the ADO
library has kept the same 1.5 name - msado15.dll.)

Each MDAC provided components that contained numerous bug and security
repairs which included all previous ADO versions. Therefore even if a
developer is using ADO 2.5, he should be using components distributed
with the latest 'n greatest MDAC.

All of which is essentially mute now, since Vista, MS has rolled the
data access components into the Windows platforms. They are now
updated and distributed as part of O/S service packs.

I only brought the subject up because in some rare cases when working
with older 3rd party libraries and controls, and running across some
strange error, I've found using ADO 2.5 occasionally corrects the
problem. This shouldn't matter since each of the ADO components are
backward compatible with previous ADO versions. So other than
attempting to use new features, the using of either interface should
be seemless - but like I said I've stubbled a few times across
situations where reverting has helped. Not even saying that that was
the actual cause, but only that it made the problem go away. <g>

That is the problem with that particular error. It normally comes
"out-of-the-blue" within perfectly "good" code. Its root cause is
often buried deep within the bowels of some obscure proprietory object
handling. Occasionally you get lucky and can ferret out the reason and
make an intelligent repair, but far more often you just fiddle around
till it goes away with a "fix" that is obscure as the error itself.
<bg>

ADO History
http://msdn.microsoft.com/en-us/library/windows/deskto...(v=vs.85).aspx

Microsoft Data Access Components
http://en.wikipedia.org/wiki/Microsoft_Data_Access_...

-ralph
[Note: I'm suggesting you might change to using the ADO 2.5 interface
in Project Settings. I'm not suggesting you revert to using the older
ADO 2.5 components from the MDAC 2.5 distribution package.]

(Mike Mitchell)

7/20/2012 3:03:00 PM

0

On Fri, 20 Jul 2012 08:24:42 -0500, ralph <nt_consulting64@yahoo.com>
wrote:

[snip]

Update:

The grid (vsFlex8) can accommodate several hundred thousand rows
(VirtualData = True).

Column 1 contains the Boolean, which vsFlexGrid renders as a checkbox
automatically.

The SQL query selects only rows where the Boolean is set.

If the Boolean is set in one or more rows greater than row 254, the
Automation Error occurs, otherwise it doesn't.

Note that the grid has one fixed row for column headers and one fixed
column (column 0) for a 'grabber', and I'm counting the rows from 0.
So row 254 is actually the 255th row, including the fixed row. Magic
number 255??

I've tried with the grid loaded with 80,000 records and a second try
with 499,859 records. It is not the number of records in the grid, but
whether the Boolean flag is set in ~any~ row past row 254.

I've just switched from 2.8 to 2.5. No difference.

Henning: DoEvents made no difference.

MM

(Mike Mitchell)

7/20/2012 3:43:00 PM

0

On Fri, 20 Jul 2012 16:02:44 +0100, MM <kylix_is@yahoo.co.uk> wrote:

Update 2:

Switched back to vsFlex7 and Automation Error did not occur.

However, VirtualData doesn't work in vsFlex7, so it's no real fix.

MM

(Mike Mitchell)

7/20/2012 4:51:00 PM

0

On Fri, 20 Jul 2012 16:42:54 +0100, MM <kylix_is@yahoo.co.uk> wrote:

Update 3:

I fixed it with a kludge.

The line that gives the Automation Error is:

Set vsFlexHeaders.DataSource = rs

So I've stuck a Retry: label in front, then in the error handler, if
Err.Number = -2147417848 then Resume Retry

Works! Don't notice any pause while VB6 works out what to do.

MM

(Mike Mitchell)

7/20/2012 5:39:00 PM

0

On Fri, 20 Jul 2012 17:50:39 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>On Fri, 20 Jul 2012 16:42:54 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>Update 3:
>
>I fixed it with a kludge.
>
>The line that gives the Automation Error is:
>
> Set vsFlexHeaders.DataSource = rs
>
>So I've stuck a Retry: label in front, then in the error handler, if
>Err.Number = -2147417848 then Resume Retry
>
>Works! Don't notice any pause while VB6 works out what to do.
>
>MM

Update 4: A better fix

I removed the Retry: kludge.

I now do:

vsFlexHeaders.Rows = 1
DoEvents
Set vsFlexHeaders.DataSource = rs

No Automation Error !

It seems that first clearing the grid, then setting the recordset
causes the AE to go away. However, vsFlexHeaders.Clear didn't work. I
have to use vsFlexHeaders.Rows = 1

MM

Norm

7/20/2012 11:21:00 PM

0

MM formulated the question :
> On Fri, 20 Jul 2012 17:50:39 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>> On Fri, 20 Jul 2012 16:42:54 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>>
>> Update 3:
>>
>> I fixed it with a kludge.
>>
>> The line that gives the Automation Error is:
>>
>> Set vsFlexHeaders.DataSource = rs
>>
>> So I've stuck a Retry: label in front, then in the error handler, if
>> Err.Number = -2147417848 then Resume Retry
>>
>> Works! Don't notice any pause while VB6 works out what to do.
>>
>> MM
>
> Update 4: A better fix
>
> I removed the Retry: kludge.
>
> I now do:
>
> vsFlexHeaders.Rows = 1
> DoEvents
> Set vsFlexHeaders.DataSource = rs
>
> No Automation Error !
>
> It seems that first clearing the grid, then setting the recordset
> causes the AE to go away. However, vsFlexHeaders.Clear didn't work. I
> have to use vsFlexHeaders.Rows = 1
>
> MM

I just thought I would mention this as I have run into something
similar
quite often and found that if I just put a slight delay where the
problem is occuring it goes away, almost as if VB6 cannot work at the
speed of the new computers. lol

Something like this:

Dim i as long
i = 0
Do Until i = 50
DoEvents
Sleep 10
i = i + 1
Loop

Norm

(Mike Mitchell)

7/21/2012 5:00:00 AM

0

On Fri, 20 Jul 2012 16:21:13 -0700, Norm Fowler <NormF4@spoof.com>
wrote:

>MM formulated the question :
>> On Fri, 20 Jul 2012 17:50:39 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>>
>>> On Fri, 20 Jul 2012 16:42:54 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>>>
>>> Update 3:
>>>
>>> I fixed it with a kludge.
>>>
>>> The line that gives the Automation Error is:
>>>
>>> Set vsFlexHeaders.DataSource = rs
>>>
>>> So I've stuck a Retry: label in front, then in the error handler, if
>>> Err.Number = -2147417848 then Resume Retry
>>>
>>> Works! Don't notice any pause while VB6 works out what to do.
>>>
>>> MM
>>
>> Update 4: A better fix
>>
>> I removed the Retry: kludge.
>>
>> I now do:
>>
>> vsFlexHeaders.Rows = 1
>> DoEvents
>> Set vsFlexHeaders.DataSource = rs
>>
>> No Automation Error !
>>
>> It seems that first clearing the grid, then setting the recordset
>> causes the AE to go away. However, vsFlexHeaders.Clear didn't work. I
>> have to use vsFlexHeaders.Rows = 1
>>
>> MM
>
>I just thought I would mention this as I have run into something
>similar
>quite often and found that if I just put a slight delay where the
>problem is occuring it goes away, almost as if VB6 cannot work at the
>speed of the new computers. lol
>
>Something like this:
>
>Dim i as long
>i = 0
>Do Until i = 50
>DoEvents
>Sleep 10
>i = i + 1
>Loop
>
>Norm
>

Yes, I have tried something like that in the past and was going to
keep it on the back burner as something else to try. And then I hit
upon the idea of clearing the grid first, and that worked.

MM