Tempory Recordset question

[Marc Vanzegbroeck]

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]

Search Google for "ado disconnected recordset".

EMG

[Marc Vanzegbroeck]

Hi,

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

I have tried with

Code: Select all  Expand view

oRs:ActiveConnection:hObj := nil
 

but the program keep deleting from the table

[Enrico Maria Giordano]

Try closing the connection too.

EMG

[Marc Vanzegbroeck]

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

[Enrico Maria Giordano]

It should be possible. Try changing oRs:CursorLocation.

EMG

[Marc Vanzegbroeck]

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

Code: Select all  Expand view

            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]

As I already said, try to close the connection:

Code: Select all  Expand view

oRs:ActiveConnection:Close()

EMG

[Armando]

Marc:

Perhaps wit the next sample:

Code: Select all  Expand view

            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

[Marc Vanzegbroeck]

As I already said, try to close the connection:

Code: Select all  Expand view

oRs:ActiveConnection:Close()

EMG

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

Code: Select all  Expand view

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]

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: Select all  Expand view

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: Select all  Expand view

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

[Rick Lipkin]

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]

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]

Code: Select all  Expand view

function FW_AdoExportToDBF( oRs, cDbf, lEditStruct )

FWH has answers to many issues.

[nageswaragunupudi]

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.