Fog Creek Software
Discussion Board




Design question II

Since everyone was so helpful in my first question (http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=64144&ixReplies=24),
here's another. 

I want to be able to dynamically generate a recordset.  I have a table that tells what columns to pull from what table.  However, I need to have another way to filter.  For example, message A may need "WHERE orderID=5", but message B may need "WHERE inventory_type=5 and qty > 1000".  My current thought is to let the calling method assemble and pass in an entire WHERE clause.  That way my query just dynamically assembles from passed in WHERE clause and the table that tells what columns to pull, etc.

Any better ideas?

Thanks in advance.

shiggins
Friday, August 22, 2003

For those who are interested.  I ended up creating my email message in XML using MSXML and transforming it with an XSL.  It works like a dream!  Thanks again.

shiggins
Friday, August 22, 2003

Not sure I understand the question.

You could try the Recordset.filter property.

http://www.devguru.com/Technologies/ado/quickref/recordset_filter.html

If you are working with you XML and XSL, perhaps you could filter using xpath.

http://www.w3schools.com/xpath/

Ged Byrne
Friday, August 22, 2003

First off, didn't the link at the bottom used to say "Reply" or "Reply to Topic"?  Or did I completely lose my mind.  Mine said "Add comment".

OK.  GED I though about both those solutions.  However, you still have to pass in the filter (or multiple filters).  So if the user is on the site in Order #123, I need to send them an email that grabs all relevant order data "where orderID=123".  I don't use ADO from the ASP page.  I use it from my COM object.  I was taught that it was more secure (and some other reasons I can't think of right now).  So if I use a filter, I still have to go:
rsRecordSet.Filter = " orderID=123 "

But what if this particular message has more that one ID to filter on?  You would have
rsRecordSet.Filter = " orderID=123 and qty < 2000 "

You also have the potential of setting a filter and that column not being in the select list.  I think this is probably the only way to do it, but I thought I'd ask just in case.  To me setting a filter this way could easily lead to errors.  Do you think?

shiggins
Friday, August 22, 2003

Read this, it's good stuff:

www.algonet.se/~sommar/dyn-search.html

I would not use rs.Filter as a substitute for the WHERE clause of your SELECT statement, it's resource piggy and probably less flexible (I never use it so I don't really know it's limitations).

Saying "the column might not be in the SELECT list" sounds strange to me.  Your app should be pretty confident about what columns it's going to be working with.

Matt Conrad
Friday, August 22, 2003

Matt, why would that sound strange? You can put columns in the WHERE that aren't in the SELECT list.

SELECT a FROM mytable WHERE b=10

Nothing strange about that.

Troy King
Saturday, August 23, 2003

You're right.  I misunderstood what shiggins meant there.

Matt Conrad
Saturday, August 23, 2003

Are you saying that you want to combine multiple requests.  You program may receive 5 different filter requests.

You want to combine all 5 filter requests, and return a single recordset?

If this is the case, then passing in the requests as Where clauses is probably a bad idea.  Text parsing is not VBs strength.  It could also be very hard to debug.

You'll be much better off creating an SQL statement object with a collection of Filters.  Each filter specifies one of your criteria, and you can add these one at a time.

Then have an execute method that assembles the sql statement and returns the recordset.

Ged Byrne
Tuesday, August 26, 2003

*  Recent Topics

*  Fog Creek Home