Align matching rows in recordSet
I have two recordsets that have the same primary key: a combination of two columns named "item_no" and "Order_id" ..... Both recordsets have this primary key (combination) and the rest of the other columns .. ...
Now I want to order (these) rows in two recordsets in such a way that the position of the records (with the same primary key values) is the same ...
For example, consider rset1 and rset2 ... with the above columns as a combination of primary keys, and row5 in rset1 has values for "item_no" and "order_id" as 2,300 and row 8 in rset2 has values for above as 2,300 ( which is the same) ....
1) Now I want to cast row5 (in rset1) to position 8 such that both records are the same at position in recordsets
2) if there are no common entries in both rsets ... then I will leave this blnk at position tht (in rset1) for the corresponding line (in rset2) [tht value for entry in rset2 ther does not match entries exist in rset1]
He always concluded that no: entries in rset2> tht in rset1 ...
Can anyone help aligning the records in a recordset in VB6.0 as I will be binding these recordsets to Excel to display side by side ??
Any built-in method (for alignment or whatever) in VB6.0 ??
a source to share
There is no built-in method in VB6 (not that I know), but it's easy enough to do it yourself.
What you need to do is create two spurious lists of entries, after you get them you can spit them out as you want (to a csv file, succeed, to access ms). CAVEAT: records must be sorted by primary key in order to work.
This code executes both recordsets sequentially, creating ordered lists with spaces or record references (although it looks a lot like VB code, consider it pseudocode, the logic is correct, it won't compile)
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs1List As Collection
Dim rs2List As Collection
REM code here to initialise the collections to new Collection and fill the record sets
Do While Not rs1.EOF And Not rs2.EOF
If rs1("PKey") = rs2("PKey") Then
rs1List.Add rs1.Bookmark
rs2List.Add rs2.Bookmark
rs1.Movenext
rs2.Movenext
ElseIf rs1("PKey") < rs2("PKey") Then
rs1List.Add rs1.Bookmark
rs2List.Add Nothing
rs1.Movenext
ElseIf rs1("PKey") > rs2("PKey") Then
rs1List.Add Nothing
rs2List.Add rs2.Bookmark
rs2.Movenext
End If
Loop
Do While Not rs2.EOF
rs1List.Add Nothing
rs2List.Add rs2.Bookmark
rs2.Movenext
Loop
Do While Not rs1.EOF
rs1List.Add rs1.Bookmark
rs2List.Add Nothing
rs1.Movenext
Loop
Assumes the primary key field is PKey, and that. Bookmark is a method you can use to navigate directly to that entry (maybe an ordinal position can be done if available).
Hope it helps
Edit
Just changed some bits in the last two loops, they weren't quite right.
a source to share