Filtering SQL view

[Tim Kelley]

I am trying to populate a dataviewgrid with records from a database that are
duplicates. I have created 3 views to get the information I want, but if I
try to apply a filter to my bindingsource control, I am returned all
records, not just the duplicates. Here are my 3 SQL views:
View1: - records are dated by year, this view filters all except current
year
SELECT TOP 100 PERCENT fldCliID, fldName2, fldName1, fldAdd1, fldAdd2,
fldCity, fldState, fldZip, fldStudioNo, fldGraduationYear,
fldOrganizationID,
fldCliNo
FROM dbo.tblClients
WHERE (fldGraduationYear = N'2007')
ORDER BY fldName2, fldName1

View2: - this view actully picks out the dups only
SELECT TOP 100 PERCENT fldName2, fldName1, fldCliID, fldAdd1, fldAdd2,
fldCity, fldZip, fldCliNo, fldState, fldGraduationYear, fldOrganizationID
FROM dbo.VIEW1
WHERE (fldName2 IN
(SELECT [fldName2]
FROM [View1] AS Tmp
GROUP BY [fldName2], [fldName1]
HAVING COUNT(*) 1 AND fldName1 =
dbo.View1.fldName1))
ORDER BY fldName2, fldName1

View3: - this is the view used in the program to pull the records into a
datatab;e
SELECT fldName2, fldName1, fldCliID, fldAdd1, fldAdd2, fldCity, fldZip,
fldCliNo, fldState, fldGraduationYear, fldOrganizationID
FROM dbo.VIEW2

After binding the datatable to the binding source and applying a filter (on
fldorganizationID), all records (for the selected organization) are
displayed.

Any ideas on a different way to accomplish this?

[JTC ^..^]

"Tim Kelley" <tk*****@company.comwrote in
news:OK**************@TK2MSFTNGP03.phx.gbl:

I am trying to populate a dataviewgrid with records from a database
that are duplicates. I have created 3 views to get the information I
want, but if I try to apply a filter to my bindingsource control, I am
returned all records, not just the duplicates. Here are my 3 SQL
views: View1: - records are dated by year, this view filters all
except current year
SELECT TOP 100 PERCENT fldCliID, fldName2, fldName1, fldAdd1,
fldAdd2, fldCity, fldState, fldZip, fldStudioNo, fldGraduationYear,
fldOrganizationID,
fldCliNo
FROM dbo.tblClients
WHERE (fldGraduationYear = N'2007')
ORDER BY fldName2, fldName1

View2: - this view actully picks out the dups only
SELECT TOP 100 PERCENT fldName2, fldName1, fldCliID, fldAdd1,
fldAdd2, fldCity, fldZip, fldCliNo, fldState, fldGraduationYear,
fldOrganizationID FROM dbo.VIEW1
WHERE (fldName2 IN
(SELECT [fldName2]
FROM [View1] AS Tmp
GROUP BY [fldName2], [fldName1]
HAVING COUNT(*) 1 AND fldName1 =
dbo.View1.fldName1))
ORDER BY fldName2, fldName1

View3: - this is the view used in the program to pull the records into
a datatab;e
SELECT fldName2, fldName1, fldCliID, fldAdd1, fldAdd2, fldCity,
fldZip, fldCliNo, fldState, fldGraduationYear, fldOrganizationID
FROM dbo.VIEW2

After binding the datatable to the binding source and applying a
filter (on fldorganizationID), all records (for the selected
organization) are displayed.

Any ideas on a different way to accomplish this?

It not clear what you are trying to do. As I am currently learning all
this I decided to take up your challenge.

------------------------------------------------------------------------
The following view will give you all records that are duplicates.
(assuming you want/need to use views, though I've no idea why you are
using 3)
------------------------------------------------------------------------
CREATE VIEW vwClientDuplicates
(fldName2, fldName1, fldCliID, fldAdd1, fldAdd2, fldCity, fldZip,
fldCliNo, fldState, fldGraduationYear, fldOrganizationID, [Rows])
AS
SELECT fldName2, fldName1, fldCliID, fldAdd1, fldAdd2, fldCity,
fldZip,
fldCliNo, fldState, fldGraduationYear, fldOrganizationID, count(*)
FROM tblClients
WHERE fldGraduationYear != DATEPART(yyyy, getdate())
GROUP BY fldName2, fldName1, fldCliID, fldAdd1, fldAdd2, fldCity, fldZip,
fldCliNo, fldState, fldGraduationYear, fldOrganizationID
HAVING count(*) 1

------------------------------------------------------------------------
The following code will allow you to filter, give or take a few
modifications.
------------------------------------------------------------------------
private void Form1_Load(object sender, System.EventArgs e)
{
// Query
string queryStr =
"SELECT * FROM View1";

// Create Connection
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "Data Source=(local); Initial Catalog =
TEST; Integrated Security=SSPI";

// Create Command
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryStr;

// Create DataSet
dsClients ds = new dsClients();

// Create DataAdapter
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds, "tblClients");

// Open Connection
cnn.Open();

// DataReader
SqlDataReader dr = cmd.ExecuteReader();

comboBox1.Text = "Select...";
// Populate ComboBox
while(dr.Read())
{
comboBox1.Items.Add(dr.GetString(10));
}

comboBox1.DisplayMember =
ds.tblClients.fldOrganizationIDColumn.ToString();
comboBox1.ValueMember =
ds.tblClients.fldOrganizationIDColumn.ToString();

// Close Reader
dr.Close();
// Close Connection
cnn.Close();
// Create View
dv = new DataView(ds.tblClients);
dataGrid1.DataSource = dv;

}

private void comboBox1_SelectedIndexChanged(object sender,
System.EventArgs e)
{
// Filter
dv.RowFilter = "fldOrganizationID = '" +
comboBox1.SelectedItem + "'";
}

------------------------------------------------------------------------

HTH
JTC ^..^

[JTC ^..^]

Correction :o

private void Form1_Load(object sender, System.EventArgs e)
{
// Query
string queryStr =

"SELECT * FROM vwDuplicates" // NOT view1