filtering with data relations

  • Thread starter Thread starter Blue man
  • Start date Start date
B

Blue man

Hello
I created a relation between 2 tables and that worked perfectly , but when i
added some parameters to select commands the relation didn't worked anymore.
here is the code befor adding that returns : " This constraint cannot be
enabled as not all values have corresponding parent values." what's wrong
with my code?
string comm="SELECT PN, FirstName, SurName, Gener FROM PersonalInfo WHERE
FirstName LIKE @FirstName OR SurName LIKE @SurName OR PN LIKE @PN";

string comm2="SELECT TableID, PN, Status, Type, Dosis, Time FROM
Medication";


adapter2.SelectCommand=new SqlCommand(comm2,myconn);

adapter.SelectCommand = new SqlCommand(comm, myconn);


adapter.SelectCommand.Parameters.Add("@FirstName",
SqlDbType.Char,50).Value="arash";

adapter.SelectCommand.Parameters.Add("@SurName",
SqlDbType.Char,50).Value="afaghi";

adapter.SelectCommand.Parameters.Add("@PN",SqlDbType.BigInt,8).Value=8989123
;



adapter2.Fill(ds,"Medication");

adapter.Fill(ds,"PersonalInfo");


ds.Tables["PersonalInfo"].PrimaryKey = new DataColumn[]
{ds.Tables["PersonalInfo"].Columns["PN"]};

ds.Tables["Medication"].PrimaryKey = new DataColumn []
{ds.Tables["Medication"].Columns["PN"]};


DataColumn PinfoPN = ds.Tables["PersonalInfo"].Columns["PN"];

DataColumn MedicPN = ds.Tables["Medication"].Columns["PN"];



DataRelation test = new DataRelation("Medication",PinfoPN,MedicPN);

ds.Relations.Add(test);

adapter.Fill(ds,"PersonalInfo");

adapter2.Fill(ds,"Medication");


dataGrid1.SetDataBinding(ds,"PersonalInfo");



Thanks in advance
 
Hi

This is just a thought, but can't you do it using a
filter on the datatable?, as far as I'm concerned, you
can do with filters pretty much the same as using SQL.

Ravikanth
 
Hi Ravikanth

good but how? you mean use some parameters to select from datatable? i am
aware that is not possible with dataset , but not sure about datatable ,do
you have any solution?
thanks

Ravikanth said:
Hi

This is just a thought, but can't you do it using a
filter on the datatable?, as far as I'm concerned, you
can do with filters pretty much the same as using SQL.

Ravikanth

-----Original Message-----
Hello
I created a relation between 2 tables and that worked perfectly , but when i
added some parameters to select commands the relation didn't worked anymore.
here is the code befor adding that returns : " This constraint cannot be
enabled as not all values have corresponding parent values." what's wrong
with my code?
string comm="SELECT PN, FirstName, SurName, Gener FROM PersonalInfo WHERE
FirstName LIKE @FirstName OR SurName LIKE @SurName OR PN LIKE @PN";

string comm2="SELECT TableID, PN, Status, Type, Dosis, Time FROM
Medication";


adapter2.SelectCommand=new SqlCommand(comm2,myconn);

adapter.SelectCommand = new SqlCommand(comm, myconn);


adapter.SelectCommand.Parameters.Add("@FirstName",
SqlDbType.Char,50).Value="arash";

adapter.SelectCommand.Parameters.Add("@SurName",
SqlDbType.Char,50).Value="afaghi";

adapter.SelectCommand.Parameters.Add ("@PN",SqlDbType.BigInt,8).Value=8989123
;



adapter2.Fill(ds,"Medication");

adapter.Fill(ds,"PersonalInfo");


ds.Tables["PersonalInfo"].PrimaryKey = new DataColumn[]
{ds.Tables["PersonalInfo"].Columns["PN"]};

ds.Tables["Medication"].PrimaryKey = new DataColumn []
{ds.Tables["Medication"].Columns["PN"]};


DataColumn PinfoPN = ds.Tables["PersonalInfo"].Columns ["PN"];

DataColumn MedicPN = ds.Tables["Medication"].Columns ["PN"];



DataRelation test = new DataRelation ("Medication",PinfoPN,MedicPN);

ds.Relations.Add(test);

adapter.Fill(ds,"PersonalInfo");

adapter2.Fill(ds,"Medication");


dataGrid1.SetDataBinding(ds,"PersonalInfo");



Thanks in advance


.
Click to expand...
Click to expand...
 
When you added the DataRelation, you implicitly created a
ForeignKeyConstraint in your DataSet so that all child rows had
to have corresponding parent rows. When you added parameters to
the parent query, you retrieved only a subset of rows from the
parent table. However, you're still retrieving all rows from the
child table. Thus, you get the exception.

To keep the exception from occurring, and to improve the
performance of your application, you need to change your query to
retrieve just the child rows for the parents you retrieved in the
initial query. Here are two possible ways to write the child
query.

SELECT Medication.TableID, Medication.PN, Medication.Status,
Medication.Type, Medication.Dosis, Medication.Time
FROM Medication, PersonalInfo
WHERE Medication.PN = PersonalInfo.PN AND
(PersonalInfo.FirstName LIKE @FirstName OR
PersonalInfo.SurName LIKE @SurName OR
PersonalInfo.PN LIKE @PN)

OR

SELECT TableID, PN, Status, Type, Dosis, Time
FROM Medication WHERE PN IN
(SELECT PN FROM PersonalInfo
WHERE FirstName LIKE @FirstName OR
SurName LIKE @SurName OR PN LIKE @PN)

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top