SELECT DISTINCT in DataView RowFilter

Question

I am trying to narrow the rows that are in my DataView based on a relationship with another table, and the RowFilter I use looks like this:

dv = new DataView(myDS.myTable, "id IN (SELECT DISTINCT parentID FROM myOtherTable)", "name asc", DataViewRowState.CurrentRows); 

The table myTable and myOther are associated with myTable.ID and myOtherTable.parentID, so the idea is that the DataView should contain only rows from "myTable" that have corresponding child rows in "myOtherTable".

Sorry, I get this error:

Syntax error: Missing operand after DISTINCT statement.

SQL is okay as far as I know, so I wonder if there are some restrictions on using the DISTINCT keyword as part of RowFilter SQL? Does anyone have an idea?

Answer 1

Unfortunately, I do not think you can perform a subquery in a DataView filter expression. You are allowed to use a subset of SQL in some expressions (documented here ).

You will probably need to execute your subquery ( SELECT DISTINCT parentID FROM myOtherTable ) separately.

This article describes the problem and possible solution.

Answer 2

Unfortunately, you cannot do this because the RowFilter property does not support a single keyword. The following is a list of expressions that you can execute in a RowFilter (which is only a DataColumn expression): http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

DataViews have a ToTable method, and several overloads take a boolean value to indicate whether only individual rows should be returned.

Here is one way: http://msdn.microsoft.com/en-us/library/wec2b2e6.aspx

Here's how you use it:

DataTable newDataTable = myDataView.ToTable (true, [array of column names as strings]);

Answer 3

This question will show you how to create a separate set (actually a new datatable) using a DataView in a table. This approach is slightly different from what you do above.

Answer 4

The following code retrieves the various values ​​/ records from the table / dataview, namely (PROD_DESP_TRN) having the field (CONTAINER_NO) Finally, this code fills the combobox (cmbContainerNo) with unique values ​​/ records

Declaration of Forms:

 Dim dsLocal As DataSet Dim dvm As DataViewManager Private Sub FillcomboContainer() Try Dim dv As DataView = New DataView cmbContainerNo.DataSource = Nothing dv = dvm.CreateDataView(dsLocal.Tables("PROD_DESP_TRN")) dv.Sort = "CONTAINER_NO" cmbContainerNo.DataSource = dv.ToTable(True, "CONTAINER_NO") cmbContainerNo.DisplayMember = "CONTAINER_NO" Catch ex As Exception MsgBox(ex.Message) Finally End Try End Sub 

Answer 5

Try just leaving "DISTINCT". In this case, the results should be the same with or without. Troubleshoot from there.

Answer 6

 DataView dvBindAssignedByDropDown = new DataView(); DataTable dtBindAssignedByDropDown = new DataTable(); dvBindAssignedByDropDown = ds.Tables[0].DefaultView; string[] strColnames=new string[2]; strColnames[0] = "RedNames"; strColnames[1] = "RedValues"; dtBindAssignedByDropDown = dvBindAssignedByDropDown.ToTable(true, strColnames); ddlAssignedby.DataTextField = "RedNamesNames"; ddlAssignedby.DataValueField = "RedNames"; ddlAssignedby.DataSource = dtBindAssignedByDropDown; ddlAssignedby.DataBind(); ddlAssignedby.Items.Insert(0, "Assigned By"); ddlAssignedby.Items[0].Value = "0";