ADO Recordsets

[smash81]

hello,
I need help with ADO Recordsets.
What i need to know is, is it possible to have the fields of a
recordset getting data from other recordsets?
what i mean is, is it possible to assign one field of recordset A and one field of recordset B to a third recordset C?
If so please help me on how this may be done.

 

[MatsHulten]

Not that I know of. Remember that a recordset is no more than a reference to a set of data in (usually) a database.
You can duplicate the reference (the recordset) to the data, but you would still be left with no more than multiple references to the same data. (Wich is great if this is what you are after

However, if you are looking for a way to copy data from one database to another simply by taking a copy of the recordset I haven't found any better way than going through the source RS row by row and copy the content to the destination RS.

Good Luck
-Mats Hulten

 

[smash81]

Thank you for the replies.
Let me give some more details.
I have a table with two fields; amount and payment_type.
amount holds the amount of money paid and the payment_type holds the type of payment(Debit/Credit).
Now what i want is to have one recordset having two fields;
one with all the amounts where the type is Debit and the other with all the amounts where the type is Credit.
Is this possible?If so how?
Please help

 

[MatsHulten]

Thats eazy!

Dim oConnection as New ADODB.Connection
Dim orsDebit as New ADODB.Recordset
Dim orsCredit as New ADODB.Recordset
Dim strQueryDebit as String
Dim strQueryCredit as String

oConnection.Open <Insert connectionstring to DB here>

strQueryDebit  = &quot;Select * From <Insert table name>&quot; & _
                 &quot;Where payment_type = 'Debit'&quot;
strQueryCredit = &quot;Select * From <Insert table name>&quot; & _
                 &quot;Where payment_type = 'Credit'&quot;

orsDebit.Open strQueryDebit, oConnection
orsCredit.Open strQueryCredit, oConnection

Good Luck!
-Mats Hulten

 

[smash81]

Thanks but that is the first step.
The main part is to have a third recordset which should contain both the fields(columns) i.e the one from orsDebit and the one from orsCredit.
How can this be done?

 

[MatsHulten]

strQueryBoth = &quot;Select * From <Insert table name>&quot;

-Mats

 

[smash81]

thanks however what i need is to have amounts of different types i.e Debit and Credit to be in different columns/fields of the recordset.
this solution will give me all the data in the same field/column of the recordset

 

[jknair]

Hi,
SQL statement(on any RDBMS) can acheive this !!

Say your table actran has following fields

acno int
dbcrtype char(2)
amount money

dim rs as adodb.recordset
rs.open "select acno,cramt=(case dbcrtype when "CR" then amount else 0 end),dbamt=(case dbcrtype when "DB" then amount else 0 end) from actran",etc....

...

Now you have both credits and debits in a single recordset..