Querying a query 1

[Guggly]

I'm not sure if I'm explaining this question correctly, but basically I'm working on a database project in VB .Net and using a Microsoft Access backend for the database (might eventually upgrade it to SQL Server, not sure).

How can I query the contents of a query (which is returned in form of a recordset) with an SQL statement? In other words, I don't want to have to write the query to the database either as a query or as a table, I just want to be able to use the values that were returned, in a recordset, and perform some further calculations on them via an SQL statement.

Is this possible? If not, what are my options?

Thanks! -- Mike

 

[jubble]

not sure if this is what you're after but have a look at the Datatable.Select command.
This allows you to run a query on a table and returns an array of rows.
One of the best features of ADO.Net

 

[Guggly]

Thanks Jubble! Can I actually run an SQL statement there? In the examples I've seen, it only uses Select with criteria as the filter. Can I put some kind of an IN SELECT subquery here and filter on that?

Also, what if I want to join my recordset with another table or query in order to get the data I need? That's something that couldn't be done with a filter, how can I go about that?

 

[ca8msm]

You can run a full SQL statement in the Select method (like you've found out it is used for setting criteria on a particular field).

If you want to join one statement to anohter table, either do that in the original SQL statement or use a DataSet and set up Relations.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[Guggly]

Okay, I've finally gotten around to working on this project again, but when I try the above suggestions it throws an error which says "Missing operand before 'In' operator."

Below is my line of code, what am I doing wrong?

Thanks! -- Mike

objDataSet.Tables("ItemsList").Select("Not In (SELECT tbl_UsedItems.ID FROM tbl_UsedItems WHERE ((tbl_UsedItems.Reusable)=False))")

 

[ca8msm]

You can run a full SQL statement in the Select method (like you've found out it is used for setting criteria on a particular field).

This is my fault due to a typo as it should have been "You can't run a full SQL statement..."

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ThatRickGuy]

Don't you need to specify the Field name that contains the value to check against the IN also? ie:

ID Not In (SELECT tbl_UsedItems.ID FROM tbl_UsedItems WHERE ((tbl_UsedItems.Reusable)=False))

And like Ca8msm said, I don't think you can run another select statment inside a select, unless perhaps if you are selecting against the data in the datatable...

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[Guggly]

Thanks, no wonder it didn't work . Rick, you're absolutely right, I didn't put in a field name to compare the list agaist.

So, where do I go from here? ca8msm, you suggested using Relations, but from what I can tell a Relation is what is in Access as a Relationship and MSSQL as a Database Diagram. If so, then I don't see how I could use that to filter out my list. Or am I missing something?

I could selectively create my dataset as it's being built by checking it against the table I need to compare with, but since SQL seems much faster for this type of thing I've been trying to do it via SQL. Is that even possible?

Thanks! -- Mike