RowFilter = column not in value from other table?

Posted on 2007-08-09
Medium Priority
Last Modified: 2013-11-26
From the bygone days of the dataview, I have a table that is a part of a dataset and I want to filter it off of another table's value.

select poo_suboptionrefid suboptionrefid, description   from product_or_option poo , suboptionref sor
where poo.poo_suboptionrefid = sor.suboptionrefid
and poo_active_sts_cd = 'A'
and poo_product_flag = 'Y'
and poo_suboptionrefid not in
select pop_product_id from product_option
where pop_option_id = 1310

I have pulled a tiny table for Products, and another for FlexOptions.  I am trying to show you what you can still pick from in the FlexOptions  

With dvPicked
            .Table = dsProds.Tables("Products")
            .RowFilter = "suboptionrefid  not in ( select pop_product_id from flexoptions where pop_option_id =" & Me.cboOptions.SelectedValue(0) & " )"

Error it pops up "Syntax error: Missing operand after 'pop_product_id' operator."

Thinks that this is a plain vanilla query and not a subtle sub query.

Is there a work around for this?   I could do a lot of this low level functionality in my gui instead of calling my bizLayer to set up the SQL for a data call.

Question by:srussell705
LVL 96

Accepted Solution

Bob Learned earned 1000 total points
ID: 19666117
The RowFilter for a DataView is not an SQL query, and you can't make it work that way.  It is a simple filter expression, with some extra functions to make it easier to reference rows in a parent/child data relationship.


Author Comment

ID: 19669982

I thought that this was the case :(

I appreciate your backing up my experience.  To bad because i could do a lot with the dataview.  Maybe 3.5 will look at this differently?  Not so much as a Linq thing because Linq seems to be tied to SQL Server.  I want this to be ADO centric instead.


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question