Greetings,
I'm trying to let my users dynamically filter records from a table that
relate to other tables.
RELATIONSHIPS:
[CustomersTable].[CustomerID] = [OrdersTable].[CustomerID]
[SalesRepsTable].[SalesRepID] = [OrdersTable].[SalesRepID]
There is a Many-to-Many relationship between CustomersTable and
SalesRepsTable.
On my Windows form, I basically dump all the SQL Server column names as text
boxes and check boxes to give users the ability to filter and sort on
anything they wish.
However, when I establish the relationship between Customers and SalesReps,
the RowFilter property of the dvCustomers DataView can get quite lengthy. I
tested the actual RowFilter statement, and it checks out. It only errors
off when the RowFilter is applied. An example:
dvCustomers.RowFilter = "CustomerID=32 OR CustomerID=434 OR
CustomerID=4955..."
The largest RowFilter I've successfully set so far contained 231 CustomerID
values. The next largest I tried (384 CustomerIDs) failed with the
following error: "An unhandled exception of type
'System.StackOverflowException' occurred in system.windows.forms.dll"
Because this is a M:M relationship, there is no way to get around the length
of the RowFilter string. For each value, I have to query the OrdersTable
and SalesRepsTable just to get the CustomerID. Does anyone have any ideas
on how to get this to work? Also, is the RowFilter limited by length,
number of parameters, or something completely different?
Many thanks,
Marty McFly 9 5858
Hi Marty,
You can have a many to many relation, however when you want to let the user
select than there is in my opinion a one to many relation.
Why are you not using the normal datarelation? http://msdn.microsoft.com/library/de...ClassTopic.asp
I hope this helps?
Cor
Hi Cor,
Thank you for the reply. You can have a many to many relation, however when you want to let the
user select than there is in my opinion a one to many relation.
I agree. That is how I set up the RowFilter. When the user specifies
column values for a foreign, I query the database to get the primary keys.
Example:
1. The user wants to view all customers that purchased items from a sales
representative named Johnson.
The user then enters "Johnson" into the textbox that references
[SalesRepsTable].[LastName] and clicks the "Filter Customers" button.
2. In the btnFilterCustomers click event, I create a T-SQL SELECT statement:
\\\
SELECT ct.CustomerID
FROM CustomersTable ct
INNER JOIN OrdersTable ot
ON ct.CustomerID = ot.CustomerID
INNER JOIN SalesRepsTable st
ON st.SalesRepID = ot.SalesRepID
WHERE st.LastName = 'Johnson'
///
3. I query the database to generate a RowFilter string, using code like:
\\\
Dim filter As String = ""
Dim dr As SqlDataReader = cmd.ExecuteReader
While dr.Read()
filter &= "CustomerID=" & dr("CustomerID") & " OR "
End While
If filter.EndsWith("OR ") Then _
filter = filter.Substring(0, filter.Length - 3)
///
The resulting filter:
filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "
(As I mentioned in my original post, this works fine, with the exception
of overflowing the RowFilter for larger resultant sets.)
4. I then set the RowFilter:
dvCustomers.RowFilter = filter
Is there a better way to do this?
Why are you not using the normal datarelation?
Do you mean that there is a way to use relation objects, so that other
tables can be referenced more succinctly in a RowFilter? I guess I was
under the impression that relations among data tables are usually just used
for data integrity (cascading updates/deletes, etc.).
Thanks again, Cor. Much appreciated.
Marty
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl... Hi Marty,
You can have a many to many relation, however when you want to let the
user select than there is in my opinion a one to many relation.
Why are you not using the normal datarelation?
http://msdn.microsoft.com/library/de...ClassTopic.asp I hope this helps?
Cor
The resulting filter: filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "
If all the same column, try using IN keyword. (I think this works in a
filter.)
filter = "CustomerID IN(123,321,1234)"
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:el**************@tk2msftngp13.phx.gbl... The resulting filter: filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "
If all the same column, try using IN keyword. (I think this works in a filter.) filter = "CustomerID IN(123,321,1234)"
Hi Greg,
You're right. IN works perfectly. Do you know of a resource that lists all
the acceptable RowFilter syntax? I couldn't find anything on MSDN.
Thank you very much, Greg. Brilliantly simple.
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:el**************@tk2msftngp13.phx.gbl... The resulting filter: filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234 "
If all the same column, try using IN keyword. (I think this works in a filter.) filter = "CustomerID IN(123,321,1234)"
I am not sure of any specific resources either. I think anything valid in a
WHERE clause in SQL works.
Noticed in your code your are doing...
While dr.Read()
filter &= "CustomerID=" & dr("CustomerID") & " OR "
End While
This is horribly inefficient when dealing with strings. Check out
stringbuilder class.
Try something akin to:
Dim sb as new system.text.stringbuilder
sb.append("Customer ID IN (")
While dr.Read()
sb.append(dr("CustomerID").ToString)
sb.append(",")
End While
sb.remove(sb.length-1,1)
sb.append(")")
filter = sb.ToString
Are you using SQL server or MSDE? I would think about redesigning this
using a stored procedure and a temporary table.
Greg
"Marty McFly" <Ma***@Mc.Fly> wrote in message
news:%2*****************@TK2MSFTNGP09.phx.gbl... Hi Greg,
You're right. IN works perfectly. Do you know of a resource that lists
all the acceptable RowFilter syntax? I couldn't find anything on MSDN.
Thank you very much, Greg. Brilliantly simple.
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message news:el**************@tk2msftngp13.phx.gbl... The resulting filter: filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234
"
If all the same column, try using IN keyword. (I think this works in a filter.) filter = "CustomerID IN(123,321,1234)"
> This is horribly inefficient when dealing with strings. Check out stringbuilder class.
I've never used the StringBuilder class...I'll give it a try.
Are you using SQL server or MSDE? I would think about redesigning this using a stored procedure and a temporary table.
I'm using SQL Server. I decided to build the string in VB just to save
resources on the server. (We have quite a few databases on the server. The
less processing, the better. And the workstations are fairly high-end, so
it's not a problem to offload processing to the clients.) Thanks for the
suggestion, though. Under normal circumstances, I would go your route.
Thanks again, Greg. You know your stuff. :)
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:Oz**************@TK2MSFTNGP10.phx.gbl... I am not sure of any specific resources either. I think anything valid in
a WHERE clause in SQL works.
Noticed in your code your are doing... While dr.Read() filter &= "CustomerID=" & dr("CustomerID") & " OR " End While
This is horribly inefficient when dealing with strings. Check out stringbuilder class.
Try something akin to:
Dim sb as new system.text.stringbuilder
sb.append("Customer ID IN (") While dr.Read() sb.append(dr("CustomerID").ToString) sb.append(",") End While sb.remove(sb.length-1,1) sb.append(")") filter = sb.ToString
Are you using SQL server or MSDE? I would think about redesigning this using a stored procedure and a temporary table.
Greg
"Marty McFly" <Ma***@Mc.Fly> wrote in message news:%2*****************@TK2MSFTNGP09.phx.gbl... Hi Greg,
You're right. IN works perfectly. Do you know of a resource that lists all the acceptable RowFilter syntax? I couldn't find anything on MSDN.
Thank you very much, Greg. Brilliantly simple.
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message news:el**************@tk2msftngp13.phx.gbl... > The resulting filter: > filter = "CustomerID=123 OR CustomerID=321 OR
CustomerID=1234 " >
If all the same column, try using IN keyword. (I think this works in
a filter.) filter = "CustomerID IN(123,321,1234)"
Marty,
The syntax that ADO.NET supports for Expressions (such as those passed to
Select & Compute, and DataView.RowFilter) is documented under
DataColumn.Expression. http://msdn.microsoft.com/library/de...ssionTopic.asp
If you define a DataRelation between two tables you can use
DataRow.GetChildRows to get the child rows for a parent DataRow or use
GetParentRow & GetParentRows to get the parents for the children. When you
data bind you can bind to the DataRelation object so you can have the parent
rows in one datagrid, plus the child rows for the selected records in a
second datagrid.
Rather then building the RowFilter directly, I would consider defining extra
"work" DataTables that demoralizes enough of the data to simplify the look
ups. Of course with an eye on the size these DataTables become.
Alternatively I would leave the data on the SQL Server and call stored
procedures and let it return the "selected" data.
Hope this helps
Jay
"Marty McFly" <Ma***@Mc.Fly> wrote in message
news:%2*****************@TK2MSFTNGP09.phx.gbl... Hi Greg,
You're right. IN works perfectly. Do you know of a resource that lists
all the acceptable RowFilter syntax? I couldn't find anything on MSDN.
Thank you very much, Greg. Brilliantly simple.
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message news:el**************@tk2msftngp13.phx.gbl... The resulting filter: filter = "CustomerID=123 OR CustomerID=321 OR CustomerID=1234
"
If all the same column, try using IN keyword. (I think this works in a filter.) filter = "CustomerID IN(123,321,1234)"
Thanks Jay. Your point about GetChildRows will be very helpful.
Marty
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl... Marty, The syntax that ADO.NET supports for Expressions (such as those passed to Select & Compute, and DataView.RowFilter) is documented under DataColumn.Expression.
http://msdn.microsoft.com/library/de...ssionTopic.asp If you define a DataRelation between two tables you can use DataRow.GetChildRows to get the child rows for a parent DataRow or use GetParentRow & GetParentRows to get the parents for the children. When you data bind you can bind to the DataRelation object so you can have the
parent rows in one datagrid, plus the child rows for the selected records in a second datagrid.
Rather then building the RowFilter directly, I would consider defining
extra "work" DataTables that demoralizes enough of the data to simplify the look ups. Of course with an eye on the size these DataTables become. Alternatively I would leave the data on the SQL Server and call stored procedures and let it return the "selected" data.
Hope this helps Jay
"Marty McFly" <Ma***@Mc.Fly> wrote in message news:%2*****************@TK2MSFTNGP09.phx.gbl... Hi Greg,
You're right. IN works perfectly. Do you know of a resource that lists all the acceptable RowFilter syntax? I couldn't find anything on MSDN.
Thank you very much, Greg. Brilliantly simple.
Marty
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message news:el**************@tk2msftngp13.phx.gbl... > The resulting filter: > filter = "CustomerID=123 OR CustomerID=321 OR
CustomerID=1234 " >
If all the same column, try using IN keyword. (I think this works in
a filter.) filter = "CustomerID IN(123,321,1234)"
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Webgour |
last post by:
I'm tring to filter a dataview with multiple rowfilters. The problem is that
each time I use RowFilter on the DataView i'm quering the full dataview as
it was loaded, not the filtered version. Is...
|
by: Ryan Moore |
last post by:
I have a dataset (called adset) which has a column called "Large" which is
either an integer 0 or 1.
I am creating 2 dataviews, one which should display the 1's and one which
should display the...
|
by: Dave Hagerich |
last post by:
I'm using a DataGrid with a DataSet and I'm trying to filter the data being
displayed, using the following code as a test:
DataView theView = new DataView(theDataSet.Tables);
theView.RowFilter =...
|
by: KC |
last post by:
For a DataView.Rowfilter can I use more than one expression? I want to
filter out two different things.
For example can I take:
dv.RowFilter = "MTX <> 'Customer Forcast'"
and
...
|
by: randy1200 |
last post by:
I have an orders table. Each record in the orders table contains a customer id.
I have a customer table. The primary key of each record in the customer
table is the customer id.
After getting...
|
by: neeraj |
last post by:
Hi, all
Could anny one give me help how can I use like operator with these data
types "integer , datetime or boolean" in DataView.RowFilter
Actually when I try to get the data from dataview...
|
by: neeraj |
last post by:
Hi, all
Could anny one give me help how can I use like operator with these data
types "integer , datetime or boolean" in DataView.RowFilter
Actually when I try to get the data from dataview...
|
by: AlexW |
last post by:
Hi
I am in the process of developing an inventory application in visual basic. I keep coming up against a problem with using the dataview.rowfilter property.
Basically what happens is this:
-a...
|
by: AlexW |
last post by:
Hi
I am in the process of developing an inventory application in visual basic. I keep coming up against a problem with using the dataview.rowfilter property.
Basically what happens is this:
-a...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |