Issue w/updating a disconnected recordset

Issue w/updating a disconnected recordset

Post by Robert » Sat, 25 Aug 2001 08:27:59



I'm building a middle tier using VB that handles data
access. I've created a method that returns a ado recordset
to a client front-end which will use it to produce a
report. I extract the data set using an ADO recordset.  
Prior to returning the recordset the method also needs to
update a column in all the
records without modifying the database.  To accomplish
this I disconnect the recordset from the database.  I
create a loop to process through the recordset.  I recieve
an 'Out of Memory' error during the processing of the
recordset.  It seems to only work with small datasets but
when the dataset is larger
than 20,000 records (record size = 800 bytes) it doesn't.  
How can I
extract a variable size dataset without running out of
memory ?  The dataset could get very large if a user tries
to run it for the report for an entire year.

Thanks for any help you can offer.

 
 
 

Issue w/updating a disconnected recordset

Post by Robert Elli » Sat, 25 Aug 2001 19:30:51


Robert,

Yikes!
You want to *avoid* building large, optimistic recordsets--it will kill your
client.
You want to avoid traversing your recordset 1 row at a time--painfully slow.

Quote:> Prior to returning the recordset the method also needs to
> update a column in all the
> records without modifying the database.

Modify your SQL select statement so that the *server* produces this data. By
way of example, if I have a table like this

Col1    Col2
5        5
6    7
3    4

I can derive the data like this :
SELECT col1, col2, UCol=(col1+col2) from Table

So as a result, the *entire* data set is being produced by the server--and
your client PC suddenly has a *much* lower workload.
Next -- modify your code to bring the data across as a read_only recordset,
which will also reduce resources on the client.

Note this approach will also give you a *major* performance boost.

R.


Quote:> I'm building a middle tier using VB that handles data
> access. I've created a method that returns a ado recordset
> to a client front-end which will use it to produce a
> report. I extract the data set using an ADO recordset.
> Prior to returning the recordset the method also needs to
> update a column in all the
> records without modifying the database.  To accomplish
> this I disconnect the recordset from the database.  I
> create a loop to process through the recordset.  I recieve
> an 'Out of Memory' error during the processing of the
> recordset.  It seems to only work with small datasets but
> when the dataset is larger
> than 20,000 records (record size = 800 bytes) it doesn't.
> How can I
> extract a variable size dataset without running out of
> memory ?  The dataset could get very large if a user tries
> to run it for the report for an entire year.

> Thanks for any help you can offer.


 
 
 

Issue w/updating a disconnected recordset

Post by Robert » Wed, 29 Aug 2001 04:17:33


Robert thanks for your reply, but my issue is still
unsolved.  The reason I can't use the solution you
proposed >SELECT col1, col2, UCol=(col1+col2) from Table
is because I'm not trying to create a calculated column
from two existing columns but trying to create a
calculated column by processing an existing column. (ie.  
SELECT DecryptDataColumn(EncCreditCardNumber) as
CardNumber, DecryptDataColumn(EncCCExpirationDate) as
CCExpirationDate from TABLE. The above statement errors
with 'Undefind Function'.  

Also what do you mean by >You want to *avoid* building
large, optimistic recordsets--it will kill your

Quote:>client.

If my Server.exe GetRecordSet() Method returns the
recordset by value, are you saying it will kill the Server
PC (the middle-tier is running on; Server.exe) or the
Client PC(receiving the recordset which crystal reports
will use).  

If I returned the recordset not by value but by reference
then the when the client PC is running the report it is
very very slow because within the detail band it calls the
recordset' column properties individually for every record
in the recordset.

Quote:>-----Original Message-----
>Robert,

>Yikes!
>You want to *avoid* building large, optimistic recordsets-
-it will kill your
>client.
>You want to avoid traversing your recordset 1 row at a

time--painfully slow.
Quote:

>> Prior to returning the recordset the method also needs
to
>> update a column in all the
>> records without modifying the database.

>Modify your SQL select statement so that the *server*

produces this data. By
Quote:>way of example, if I have a table like this

>Col1    Col2
>5        5
>6    7
>3    4

>I can derive the data like this :
>SELECT col1, col2, UCol=(col1+col2) from Table

>So as a result, the *entire* data set is being produced
by the server--and
>your client PC suddenly has a *much* lower workload.
>Next -- modify your code to bring the data across as a

read_only recordset,
>which will also reduce resources on the client.

>Note this approach will also give you a *major*
performance boost.

>R.



>> I'm building a middle tier using VB that handles data
>> access. I've created a method that returns a ado
recordset
>> to a client front-end which will use it to produce a
>> report. I extract the data set using an ADO recordset.
>> Prior to returning the recordset the method also needs
to
>> update a column in all the
>> records without modifying the database.  To accomplish
>> this I disconnect the recordset from the database.  I
>> create a loop to process through the recordset.  I
recieve
>> an 'Out of Memory' error during the processing of the
>> recordset.  It seems to only work with small datasets
but
>> when the dataset is larger
>> than 20,000 records (record size = 800 bytes) it
doesn't.
>> How can I
>> extract a variable size dataset without running out of
>> memory ?  The dataset could get very large if a user
tries
>> to run it for the report for an entire year.

>> Thanks for any help you can offer.

>.

 
 
 

1. Bnding disconnected recordset to form control issues

Hello Gurus:

the following code works fine until I disconnect the recordset.

1) I am opening a record set for update operations using the following
syntax:

   rs.Open sSql, msConnection, adOpenDynamic, adLockOptimistic, adCmdText

2) then I am attempting to bind some text controls to the recordset.  The
datafield property in each control is hardcoded to its proper and matching
fieldname in the recordset.

    For i = 0 To txtCust.Count - 1
        Set txtCust.Item(i).DataSource = rs
    Next

3) the above works fine until I attempt to utilize a disconnected recordset.
   then even though rs has the data as evidenced by

                  for i = 0 to 10: ?rs.fields.item(i).value: next

   in the immediate window, none of the text boxes display any text!!!
arrrgh

                  for i = 0 to 10: ?"'" & txtcust.item(i).Text  & "'": next

   yields only the '' chars on each of the 11 output lines.

4) **Question**
   Does the record set need to be opened with different flags when creating
a disconnected recordset?
   Can disconnected recordsets be bound like this to form controls?
   Do I have to set some kind of persistance, I'm winging it here, any
ideas?

--------------

5) When and if I get past problem 1, I plan to write the recordset back to
the database thusly:

Public Sub UpdateData(rs as adodb.recordset)
    Dim cnn As New ADODB.Connection

    'Connect to the database, associate the Recordset with the connection,
    'then update the database table with the changed Recordset.
    cnn.Open msConnection
    rs.ActiveConnection = cnn
    rs.UpdateBatch

    rs.Close
    cnn.Close
Exit Function

Is this the proper way to assign the data in rs formal function argument to
the connection?
It seems so simple, should it work this way?

Is there anything missing here, or have I made any glaring mistakes?

Thanks very much for your time,

Ed

2. SQL Problem

3. Disconnected Recordset - Issue with Cast or Converted data from SQL

4. keyword conflicts - SQL 6.5

5. updating disconnected recordsets

6. Question re: Multiple join

7. Why won't disconnected Recordset Update??

8. ! MASS POST Was Here! (dkRMpF)

9. Update on disconnected ADO Recordset

10. Updating disconnected recordsets with the Jet provider

11. PROBLEM USING BATCH UPDATE ON DISCONNECTED (HIERARCHAL) RECORDSET

12. updating virtual fields in an ADO disconnected recordset

13. ADO Update the Database with the modify done on a disconnected recordset in background mode