Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Filter property brain teaser!

VBBrandonVBBrandon Member Posts: 1
[b][red]This message was edited by VBBrandon at 2004-7-15 19:53:48[/red][/b][hr]
IF THIS POST IS TOO LONG AND YOU DON'T WANT TO READ IT, WOULD YOU AT LEAST REPLY WITH A QUICK POST AND TELL ME SO?!


Okay, now that you have come to read this intriguing post, do you think you can solve the next problem I am about to lay before you?


THE SETUP:
I open a recordset using a command object. The command object's CommandType = adCmdStoredProcedure (I think that's the constant's correct name). Any way, the command object is executing a query in an Access database, so this means that the Delete method is not available to the recordset. What does that have to do with the filter property?! Well, it's simply a way of saying that I could have solved this problem easily if I was allowed to use the Delete method.


THE ACTUAL GOAL OF THIS PROBLEM:
I want to use the recordset like it's a list, so that I can remove one, or as many items that I want, at a time, until I've eliminated all of the items from the list... er, records from the recordset.


A GUESS AT HOW TO SOLVE IT:
I need to be able to filter the recordset once, and completely eliminate the hidden records from the recordset so that I can filter it again at a later time.


SOME OF THE ALTERNATIVES:
If there's another way to do this without the Filter property, that would be acceptable. The problem is that I cannot use the Delete method to do it, and I cannot simply reconstruct a new SQL statement to go and get the new set of records from the database - that would mean that the SQL statement would have to get larger and larger, to include criteria against very specific records that I wanted to keep out. Let me show you-




SOME EXEMPLARY CODE AND THE PROBLEM WITH IT:
Here's a pretend SQL statement-

--------------------------------------------------------------
"SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993#"
--------------------------------------------------------------


So, if I were to open this into a recordset object, the recordset might come out looking something like this...

----------------------------------------
----------------------------------------
CustomerID ProductID OrderDate
C1 P1 1/1/1993
C1 P1 4/1/1993
C1 P1 7/13/1995
C1 P3 3/3/1993
C1 P5 6/12/1998
C3 P2 1/9/1995
C3 P2 2/15/1994
C3 P3 4/13/1994
C3 P5 3/11/1997
C3 P8 12/22/1996
... ... ...
----------------------------------------
----------------------------------------


Now, let's say I wanted to eliminate all of the records with ProductID = P1. I use a SQL statement to regather the original recordset only with the additional clause to not include P1...

---------------------------------------------------------------
"SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993# AND ProductID <> 'P1'"
---------------------------------------------------------------


All fine and dandy. Now let's say that after my code goes running around for a few more milliseconds, it determines that it's time to eliminate products P3 and P5 but only where they coincide with customer C1. But remember, now we also have to keep in mind that we must keep P1 out of the resultant recordset, as well. Whoa! How in the heck do you set that one up? I think it might be like something below, but in any case, I think you get the idea of how ridiculous this process is for just simply eliminating items from a list (records from a recordset).

-----------------------------------------------------------------------
"SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993# AND ProductID <> 'P1' AND ((CustomerID <> 'C1' AND ProductID <> 'P3') OR (CustomerID <> 'C1' AND ProductID <> 'P5'))"
-----------------------------------------------------------------------


I also tried using a second recordset by filtering the first and then setting a reference to the second one. But that doesn't get rid of the hidden records. It just passes them right along with the reference to the first, obviously. Hey, it was worth a shot! Got any ideas on this possible solution? Maybe there's another way to use a second recordset. You wouldn't happen to know would you? I was kind of setting my hopes on solving this problem by somehow dumping the hidden records, anyway. I've got to believe ADO is setup to do that. You'd think they'd think that if somebody filtered out the records, they might, at some time, want to just get rid of them and get them off their back.

Anyway, I really appreciate you taking the long long time to read this message. I made it so long so that I could be as unambiguous about the problem as possible. I've tried similar posts at other sights. Good luck with this one! If you want a good brain teaser, this is it!



Comments

  • jaked6803jaked6803 Member Posts: 3
    Go to this web site http://www.able-consulting.com/ADO_Faq.htm
    and look at Q42) How do I create a "copy" (not a clone) of an ADO Recordset?
    I think you can use this to start on your solution.
    I hope this helps.

    jaked6803

    : [b][red]This message was edited by VBBrandon at 2004-7-15 19:53:48[/red][/b][hr]
    : IF THIS POST IS TOO LONG AND YOU DON'T WANT TO READ IT, WOULD YOU AT LEAST REPLY WITH A QUICK POST AND TELL ME SO?!
    :
    :
    : Okay, now that you have come to read this intriguing post, do you think you can solve the next problem I am about to lay before you?
    :
    :
    : THE SETUP:
    : I open a recordset using a command object. The command object's CommandType = adCmdStoredProcedure (I think that's the constant's correct name). Any way, the command object is executing a query in an Access database, so this means that the Delete method is not available to the recordset. What does that have to do with the filter property?! Well, it's simply a way of saying that I could have solved this problem easily if I was allowed to use the Delete method.
    :
    :
    : THE ACTUAL GOAL OF THIS PROBLEM:
    : I want to use the recordset like it's a list, so that I can remove one, or as many items that I want, at a time, until I've eliminated all of the items from the list... er, records from the recordset.
    :
    :
    : A GUESS AT HOW TO SOLVE IT:
    : I need to be able to filter the recordset once, and completely eliminate the hidden records from the recordset so that I can filter it again at a later time.
    :
    :
    : SOME OF THE ALTERNATIVES:
    : If there's another way to do this without the Filter property, that would be acceptable. The problem is that I cannot use the Delete method to do it, and I cannot simply reconstruct a new SQL statement to go and get the new set of records from the database - that would mean that the SQL statement would have to get larger and larger, to include criteria against very specific records that I wanted to keep out. Let me show you-
    :
    :
    :
    :
    : SOME EXEMPLARY CODE AND THE PROBLEM WITH IT:
    : Here's a pretend SQL statement-
    :
    : --------------------------------------------------------------
    : "SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993#"
    : --------------------------------------------------------------
    :
    :
    : So, if I were to open this into a recordset object, the recordset might come out looking something like this...
    :
    : ----------------------------------------
    : ----------------------------------------
    : CustomerID ProductID OrderDate
    : C1 P1 1/1/1993
    : C1 P1 4/1/1993
    : C1 P1 7/13/1995
    : C1 P3 3/3/1993
    : C1 P5 6/12/1998
    : C3 P2 1/9/1995
    : C3 P2 2/15/1994
    : C3 P3 4/13/1994
    : C3 P5 3/11/1997
    : C3 P8 12/22/1996
    : ... ... ...
    : ----------------------------------------
    : ----------------------------------------
    :
    :
    : Now, let's say I wanted to eliminate all of the records with ProductID = P1. I use a SQL statement to regather the original recordset only with the additional clause to not include P1...
    :
    : ---------------------------------------------------------------
    : "SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993# AND ProductID <> 'P1'"
    : ---------------------------------------------------------------
    :
    :
    : All fine and dandy. Now let's say that after my code goes running around for a few more milliseconds, it determines that it's time to eliminate products P3 and P5 but only where they coincide with customer C1. But remember, now we also have to keep in mind that we must keep P1 out of the resultant recordset, as well. Whoa! How in the heck do you set that one up? I think it might be like something below, but in any case, I think you get the idea of how ridiculous this process is for just simply eliminating items from a list (records from a recordset).
    :
    : -----------------------------------------------------------------------
    : "SELECT CustomerID, ProductID, OrderDate FROM tblOrders WHERE OrderDate >= #1/1/1993# AND ProductID <> 'P1' AND ((CustomerID <> 'C1' AND ProductID <> 'P3') OR (CustomerID <> 'C1' AND ProductID <> 'P5'))"
    : -----------------------------------------------------------------------
    :
    :
    : I also tried using a second recordset by filtering the first and then setting a reference to the second one. But that doesn't get rid of the hidden records. It just passes them right along with the reference to the first, obviously. Hey, it was worth a shot! Got any ideas on this possible solution? Maybe there's another way to use a second recordset. You wouldn't happen to know would you? I was kind of setting my hopes on solving this problem by somehow dumping the hidden records, anyway. I've got to believe ADO is setup to do that. You'd think they'd think that if somebody filtered out the records, they might, at some time, want to just get rid of them and get them off their back.
    :
    : Anyway, I really appreciate you taking the long long time to read this message. I made it so long so that I could be as unambiguous about the problem as possible. I've tried similar posts at other sights. Good luck with this one! If you want a good brain teaser, this is it!
    :
    :
    :
    :

Sign In or Register to comment.