topic-32988

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Tempory Recordset question
Posted: Sat Sep 24, 2016 09:13 AM

In my program I select a list of records that a client have to order.
There he can delete the records he doesn't want to order.

In my old program, where I use DBF, I did a 'copy to for' to a temporary database of the records, and open it.
There the client can delete the records he doesn't want.

In my SQL-version, I open a recordset of the records with a SELECT FROM ... WHERE ....
Than the client get te records he can order.

The problem now is, if he delete the records, he doesn't want, the also are deleted from the table in my SQL-database

Is there a way that I can open it like a temporary recordset, that doesn't affect the original table?

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 09:32 AM

Search Google for "ado disconnected recordset".

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 10:42 AM

Hi,

I found that I have to disconnect from the database bu setting ActiveConnection to nothing.

I have tried with

Code (fw): Select all Collapse

oRs:ActiveConnection:hObj := nil

but the program keep deleting from the table

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 10:50 AM

Try closing the connection too.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 11:28 AM

Then, I can't use the Recordset anymore. I want to use it local..0

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 11:30 AM

It should be possible. Try changing oRs:CursorLocation.

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 12:39 PM

This is what I'm using, but the connection stay

Code (fw): Select all Collapse

            oRs = CREATEOBJECT( "ADODB.Recordset" ) 
            oRs:cursortype :=1
            oRs:cursorlocation :=3
            oRs:locktype := 3
            oRs:open('SELECT * FROM artikel',Connectionstring)
            oRs:ActiveConnection:hObj := nil

Enrico Maria Giordano

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 12:43 PM

As I already said, try to close the connection:

Code (fw): Select all Collapse

oRs:ActiveConnection:Close()

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Armando

Posts: 3358

Joined: Fri Oct 07, 2005 08:20 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 12:51 PM

Marc:

Perhaps wit the next sample:

Code (fw): Select all Collapse

            oRs = CREATEOBJECT( "ADODB.Recordset" ) 
            oRs:cursortype :=1         // Try with number 2  adOpenDynamic
            oRs:cursorlocation :=3    // adUseClient
            oRs:locktype := 3           // adLockOptimistic
            oRs:open('SELECT * FROM artikel',Connectionstring)
        oRsWrk:Properties("Unique Table"):Value := "artikel"   // Here
            oRs:ActiveConnection:hObj := nil

Best regards

SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 12:55 PM

Enrico Maria Giordano wrote:As I already said, try to close the connection:
Code (fw): Select all Collapse
oRs:ActiveConnection:Close()
EMG

Enrico, as i said, than I can't browse the recordset anymore.

Code (fw): Select all Collapse

oRs = CREATEOBJECT( "ADODB.Recordset" ) 
oRs:cursortype :=1
oRs:cursorlocation :=3
oRs:locktype := 3 //alleen lees
oRs:open('SELECT * FROM artikel',Connectionstring)
?oRs:recordcount() -----> Give number of records  
oRs:ActiveConnection:close()
?oRs:recordcount() ------> Give error

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 01:55 PM

Detatched RecordSets:

Setting oRs.ActiveConnection = Nothing works well with VB.
But I never succeeded in doing it with (x)Harbour so far.
I am eager to learn if a working sample is posted to do this.

To achieve the same objective I adopt 2 alternatives:

1) Open with locktype adLockBatchOptimistic and never use oRs:UpdateBatch

Code (fw): Select all Collapse

function Test()

   local oCn, oRs

   oCn   := FW_OpenAdoConnection( "testins.mdb" )
   oRs   := FW_OpenRecordSet( oCn, "testtable", adLockBatchOptimistic )

   xbrowser oRs fastedit
   // Edit, delete, addnew as you like
   // but do not use oRs:UpdateBatch()

   oRs:Close()
   oCn:Close()

return nil

My preferred and safest way is to save a local copy of the recordset and use it
Example:

Code (fw): Select all Collapse

function TestDiscon()

   local oCn, oRs
   local cDat := "mycopy.dat"

   oCn   := FW_OpenAdoConnection( "testins.mdb" )
   oRs   := FW_OpenRecordSet( oCn, "testtable", adLockBatchOptimistic )

   if File( cDat )
      FErase( cDat )
   endif
   oRs:Save( cDat )
   oRs:Close()
   oCn:Close()

   oRs   := FW_OpenRecordSet( nil, cDat )
   // oRs:ActiveConnection is NIL
   xbrowser oRs fastedit

   // Edit, delete, addnew as you like

   // In case you still like to save changes
   // Assign oRs:ActiveConnection := oCn
   // and then oRs:UpdateBatch

   oRs:Close()

return nil

For more details about the usage
viewtopic.php?f=3&t=26907&p=149354&hilit=recordset+locally#p149354

Regards

G. N. Rao.

Hyderabad, India

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 02:44 PM

Marc

As Rao suggests .. You could use\create a temp Access table .mdb OR good old dbf\cdx .. When I write reports, I always create temp .dbf\cdx and write my oRs results to the temp database somewhere on the client machine. When the report is done, Close Databases, and ferase() works just fine for me.

Rick Lipkin

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 03:04 PM

Roa, Rick,

Thank you for the information.
Save it local will be the best option.
I even can do it like I did before, in a DBF-file
Then I did a 'copy to ... for' to a temporay DBF, and open that DBF
Now I can save the recordset to a DBF, and open it

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 03:42 PM

Code (fw): Select all Collapse

function FW_AdoExportToDBF( oRs, cDbf, lEditStruct )

FWH has answers to many issues.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Tempory Recordset question
Posted: Sat Sep 24, 2016 03:43 PM

Still by experience, let me advise my second option to save the recordset and open independently is the best option.
Please do not consider other alternatives.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums