Datagrid/DataViewManager Question

[SStory]

OK here's what I want to do...

I am writing a case managment system for law enforcement.

Case is the central table.

It has Event, Victim, Witness, Suspect (relatedt to it via case_ID)

I have a Win form that has a datagrid and I have a data adapter for each of
these tables.
I put them all in dsCases1 (dataset) using fill method for each da.

I placed dsCases1 in a dataviewmanager object (DVM). (hoping I could filter
each table).

I really want Case to show in the grid and the other to only show if the
item is expanded--as links.

Above all of this I have a filter tab which allows to filter by CaseID or
Criminal Code# or Victim Name or Suspect Name.

To do this I try to do
DVM.DataViewSettings("Victim").RowFilter="v_fname='" & txtVicFirstName.text
& "'"

This will work in a sense but not as I hoped. It still shows all cases
because case is the top table in the datagrid, and if I click on the victims
tables under each case Item, only the one that has the victim with v_fname=
to the search spec, will appear.

What I want to be able to do, is show these related tables below, but when
searching only show the cases which have these victims in them, or if by
criminal code, show only case Items which have that criminal code. Is
there are way to do this and leave cases as the default table?



ie.

search for criminal code 15-A-025 and get a data grid like this (only cases
with a related victim table(record) having 15-A-025 would be in the grid.

CASE#
1
-(when expanded it would allow me to view the other related tables)
20
36
72
80
100

Any help would be appreciated.

Thanks in advance...

Shane

 

[Cowboy \(Gregory A. Beamer\)]

Here is a shot in the dark.

Statement of problem:
Parent - child relation. Want user to be able to filter where only parent
records with children are shown.

Possible solution:
1. In original data source add a view:
CREATE VIEW MYView
SELECT ParentID, COUNT(*) AS ChildCount FROM ChildTable
2. When selecting the Parent DataTable, join the view with a LEFT JOIN

Now, you can filter on the child count. In this case, you will likely want
to set NULL in the ChildCount to 0 instead of null.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think Outside the Box!
****************************************************************************
****

 

[SStory]

thanks Cowboy...

I understand plenty about views, stored procedures and all in SQL Server.
My issues were with filtering a dataset, but I have since changed my
strategy and figured out how to make it work.

I get a new view or the child table, rowfilter it for the event_code and
then get the parent of each of those rows in the dataview and clone the case
table and add the rows returned from the afforementioned to it and set
datagrid.datasource= to the new datatable.
this does it sufficient enough.

I do wish they would instill the SQL language within a dataset to be used
for joins. We all already know it and that would make it powerful.

Thanks,

Shane

There are some things that are not fully thought out in .NET. Actually an
unfair assessment, but the designers did not have the time to figure out
everything we might do with the object model. Underneath the hood, there are
some things that are a bit more difficult, like filtering across multiple
tables with a simple object. As this feature is harped apon by many people,
I am sure they are working on something to alleviate the pain, but we will
have to wait until version 2.0 (or later? I hope not).

The view I mention is on the underlying database. I assume a table structure
like this, so you will have to alter to the actual table structure:

Case
-----
CaseID
CaseName
CaseDescription

Event
------
EventID
CaseID
EventName
EventDescription

You would create this view:
CREATE VIEW MyCaseView
AS
SELECT caseID, COUNT(*) AS EventCount FROM Event


To get the info into the DataTable, the select is altered to include this
info:

CREATE PROCEDURE [dbo].[sps_GetCases]
AS

SELECT c.CaseID, [other case fields here] FROM Case c
JOIN MyCaseView mcv
ON c.CaseID = mcv.CaseID

This will avoid all cases with a value of 0. You can also link the view to
Case
CREATE VIEW MyCaseView
AS
SELECT c.caseID, COUNT(*) AS EventCount FROM Event e
LEFT JOIN Case c
ON e.CaseID = c.CaseID

which will result in 0 being added to values with 0, so you can have the
table filter

EventCount <> 0

These are quick examples, so you may have to tweak them (have not tested
code, so I could have FUBARed a bit). The same data could be filtered on
victims or suspects, et al, by creating additional views. The step-by-step
is as follows:

1. Create the view and test it. This can be done either in Enterprise
Manager or Query Analyzer. You want to make sure either a) it only shows
cases that fit a criteria (in which case, you repull data from a different
view when criteria is changed, like only cases with suspects) or that it
produces zeros when there are no records (second example)).
2. Once view is tested, write the SQL that pulls the cases and make sure it
pulls correctly. The zero option might be better here, depending on your
app, as you do not have to requery data. If you ever intend on a web front
end, you might want to use option 1 instead, as the amount of data pulled
can alter the performance of the app (the reason I showed two examples).

Overall, take a deep breath; there are few tragedies in coding, just new
things to learn. At some time in the next few days you will likely have an
epiphany moment where you wonder why you ever thought this was difficult. It
happens to everyone when they move into new areas they have not played with
before. From my viewpoint, it is simple, but I had to take the same steps to
get there.

The SQL books online will help with creating the necessary T-SQL for your
views. They are not the easiest to read. You can also install the SQL Server
samples (although written in VB6, not VB.NET or C#) and look at the sample
views and stored procedures in the Pubs and Northwind sample databases
(these databases are not the best database designs, but they can help you
get the coding for SQL Server down).

Also, consider asking some SQL questions in the
microsoft.public.sqlserver.programming groups. There are plenty of active
MPVs and MS people that peruse the SQL Server groups that are very helpful.
And, my favorite site for searching for answers before posting is
http://groups.google.com; if you have never used it, it is a godsend, as you
can find out how others solved there problems.

If you come from a traditional COM background, .NET requires not only a code
change, but a thought process change. If you look at the first .NET books
that hit the market, there were plenty of examples of people trying to code
COM in .NET, which is a mistake. I mention this, as it points out that each
of us had to change our thought patterns when moving to .NET. The XML object
changes really kicked my butt.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think Outside the Box!
****************************************************************************
****

I am new to all of this.
I am not new to SQL, but all these classes.
Here is my thought. I decided to use a dataset because it is said to get
the data from the database and then let you do manythings without hittin the
db again.

So once I get the data using several dataadapters, and I want to change the
datasource in the grid to something else--only one grid with mult. related
tables.

You mentioned creating a view. Then joining that view. How do you join a
view? I am confused about how to manipulate my data after I use SQL to get
it to a DS through a DA. Some suggest refilling the DS via the DA.fill
method. That seems to defeat the purpose and would require a trip to the
server again wouldn't it?
If so shouldn't I just use command objects and a datareader and forget
datasets?

Sorry, but I am really confused.

What I need is for the data grid to sometimes do what it does now, show all
cases and any child tables records or not, and then when filtering criteria
is entered, only show the cases that have "suspects" if suspects was the
criteria, but all all other tables below each individual case...

i.e. the person searches for Case md12512 and the grid pulls it up and shows
all child tables below. Next search for suspect last name Johnson and the
grid pulls up several case records that have a "Johnson" in a related child
table, but no cases that don't.

In the old days I'd just change my SQL Statement, but with all these new
abstractions I don't know what to do to avoid hitting the server again.

Any help appreciated...

Thanks,

Shane

message news:[email protected]...

********************************************************************************************************************************************************

CaseID
or but
when

if

by