RowFilter = column not in value from other table?

Posted on 2007-08-09
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

    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.

    LVL 1

    Author Comment


    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    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…
    This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now