Solved

Exception while creating relation in Dataset

Posted on 2009-04-09
31
335 Views
Last Modified: 2013-12-17
Hi Experts,
I am having a dataset which contains two tables.
I am trying establish relation between them,but I am getting an exception

'column' argument cannot be null.
Parameter name: column

What could be the reason?
0
Comment
Question by:johny_bravo1
  • 17
  • 9
  • 4
  • +1
31 Comments
 
LVL 22

Expert Comment

by:p_davis
ID: 24106793
code, please.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106829
Hi p_davis,
that exception is resolved but I am getting another exception now
""These columns don't currently have unique values.""

 dsShipper_master = objShipperInfo.getShipperInfo_Hierarchy(status);
 DataRelation rel = new DataRelation("Root", dsShipper_master.Tables["Table"].Columns["ShipperId"], dsShipper_master.Tables["Table1"].Columns["ShipperId"]);
 dsShipper_master.Relations.Add(rel);

Following is the SP;
  

alter PROCEDURE [dbo].[getShipperInfo_Hierarchy]  

@Status varchar(20)  

AS  

BEGIN  

 

  

 IF @Status = 'Active'  

 BEGIN  

  SELECT  sm.ShipperName,  

    sm.ShipperType,  

    lm.LocationName+' ('+lm.Loc_Type+')' as LocationName,  

    lm.Address1+', '+lm.Address2+', '+lm.City+', '+lm.State+', '+lm.Zip as Address,  

    lm.LocationId,  

    sm.ShipperId  

  FROM dbo.TMSShipper_Master sm,  

     dbo.TMSLocation_Master lm  

  WHERE sm.ShipperId = lm.Loc_ShipperId and  

       sm.IsActive = '1'  

  order by ShipperName  
 

 SELECT  

    lm.LocationName+' ('+lm.Loc_Type+')' as LocationName,  

    lm.Address1+', '+lm.Address2+', '+lm.City+', '+lm.State+', '+lm.Zip as Address,  

    sm_mc.ContactName,  

    sm_mc.Phone,  

    sm_mc.email,  

  sm_mc.ShipperId,

    lm.LocationId

      FROM    dbo.TMSShipper_MasterMoreContact sm_mc,  

   dbo.TMSLocation_Master lm  

  WHERE   lm.LocationId = sm_mc.loc_ShipperId

  

 END  

 ELSE  

 IF @Status = 'Inactive'  

 BEGIN  

  SELECT  sm.ShipperName,  

    sm.ShipperType,  

    lm.LocationName+' ('+lm.Loc_Type+')' as LocationName,  

    lm.Address1+', '+lm.Address2+', '+lm.City+', '+lm.State+', '+lm.Zip as Address,  

    sm_mc.ContactName,  

    sm_mc.Phone,  

    sm_mc.email,  

    lm.LocationId,  

    sm.ShipperId  

  FROM dbo.TMSShipper_Master sm,  

   dbo.TMSShipper_MasterMoreContact sm_mc,  

   dbo.TMSLocation_Master lm  

  WHERE sm.ShipperId = sm_mc.ShipperId and  

    lm.LocationId = sm_mc.loc_ShipperId and  

    sm.IsActive = '0'   

  order by ShipperName  

 END  

 ELSE  

 BEGIN  

  SELECT  sm.ShipperName,  

    sm.ShipperType,  

    lm.LocationName+' ('+lm.Loc_Type+')' as LocationName,  

    lm.Address1+', '+lm.Address2+', '+lm.City+', '+lm.State+', '+lm.Zip as Address,  

    sm_mc.ContactName,  

    sm_mc.Phone,  

    sm_mc.email,  

    lm.LocationId,  

    sm.ShipperId  

  FROM dbo.TMSShipper_Master sm,  

   dbo.TMSShipper_MasterMoreContact sm_mc,  

   dbo.TMSLocation_Master lm  

  WHERE sm.ShipperId = sm_mc.ShipperId and  

    lm.LocationId = sm_mc.loc_ShipperId    

 --   sm_mc.ContactId in (select min(ContactId)  

 --  from dbo.TMSShipper_Master sm,dbo.TMSShipper_MasterMoreContact sm_mc,dbo.TMSLocation_Master lm  

 --  where sm.ShipperId = sm_mc.ShipperId and lm.LocationId = sm_mc.loc_ShipperId  

 --  group by sm_mc.loc_ShipperId)  

  order by ShipperName  

 END  

  

END  

Open in new window

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24106847
the column ShipperId may not exist in either dsShipper_master.Tables["Table"] or
dsShipper_master.Tables["Table1"]
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24106863
change to  

dsShipper_master = objShipperInfo.getShipperInfo_Hierarchy(status);
 

if(dsShipper_master.Tables["Table"].Columns["ShipperId"] != null && 

dsShipper_master.Tables["Table1"].Columns["ShipperId"] != null)

{

 DataRelation rel = new DataRelation("Root", dsShipper_master.Tables["Table"].Columns["ShipperId"], dsShipper_master.Tables["Table1"].Columns["ShipperId"]);

 dsShipper_master.Relations.Add(rel);

}

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106883
It is going inside if and throwing the same exception
"These columns don't currently have unique values."
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24106908
Check both tables either in one of table the column is not the primary column or you have inserted some duplicate values in one of the table.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106941
Please look at the SP.
I have used three different tables.
Check wehn the Status='Active'
0
 
LVL 22

Expert Comment

by:p_davis
ID: 24106946
is shipperId the primary key for the TMSShipper_Master table?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106972
yes
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106989
and even in TMSShipper_MasterMoreContact is set as Foreign Key
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24106997
sorry that is in TMSShipper_MasterMoreContact , shipperId is foreign key
0
 
LVL 22

Expert Comment

by:p_davis
ID: 24107003
what type of object is that key -- is it numeric?
0
 
LVL 22

Expert Comment

by:p_davis
ID: 24107004
like int?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107022
yup,its int.
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 24107024
yep according to the query u wont have unique values for Shipper_id in either of the tables. and hence u cant set a relation between them.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107116
So you want me to put distinct for ShipperId.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107144
Even then it will not return Unique ShipperId as data will be repeated according to Location and contacts.
Any other idea?
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107150
Surprisingly if I bind only tables.I mean If i have three selects for three tables.
I am able to create relation between them.
0
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 500 total points
ID: 24107152
this wont be possible if u have multiple locations for a shipper. if u need a master table then u will need another table in the data set which will list all the shipper and u will have relations like
ShipperTable - > Table
ShipperTable -> Table1
u cant set direct relations between Table and Table1
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107189
Ya one shipper may have multiple location.
So what needs to be changed?
0
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 500 total points
ID: 24107252
u will need another table to list the shippers. u will need to change the proc to add a new select which will be
  SELECT   sm.ShipperId  
  FROM dbo.TMSShipper_Master sm,  WHERE  sm.IsActive = '1'  
this is for the active section..like wise u have to add for other sections
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107275
Ok I have included one more select statement in the procedure,for getting shipperid.Now I am establishing relation between Table<-->Table1,Table<-->Table2
This relation works.
But the problem is that I am binding this to Farpoint and it is adding one more Hierarchy.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107294
Thanks Experts for ur valuable comments.
I will be able to check it tomorrow so If you post any comment I will try it tomoroow.
Again thanks for ur help.
If u post any suggestions I will be able to work on it tomorrow.Thanks.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24107303
that is the only way u can establish relations considering ur situation
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107322
Ohh ok!
I will try something different or try to change the display structure.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24107331
Thanks gauthampj.I am not closing this question,hoping that I may need some more guidance from you.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24107347
sure..:-)..always a pleasure
0
 

Expert Comment

by:mulpuri05
ID: 24120028

Use distinct to below queary. It should return "shipperId" unique vlaue like "1,2,3,4" .I sould not contain duplicate values than only you can have your relation.Do you want this solution for reapeater.

SELECT  sm.ShipperName,  
    sm.ShipperType,  
    lm.LocationName+' ('+lm.Loc_Type+')' as LocationName,  
    lm.Address1+', '+lm.Address2+', '+lm.City+', '+lm.State+', '+lm.Zip as Address,  
    lm.LocationId,  
    sm.ShipperId  
  FROM dbo.TMSShipper_Master sm,  
     dbo.TMSLocation_Master lm  
  WHERE sm.ShipperId = lm.Loc_ShipperId and  
       sm.IsActive = '1'  
  order by ShipperName  
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24124785
I m using this for FarPont and not for repeater.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 24125702
even if u use distinct since u have multiple locations for a shipper, shipper id will be duplicated
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24129445
Hii
can I create relation something like this,I am missing some syntax here,
DataRelation rel = new DataRelation("Root", dsShipper.Tables["Table"].Columns["ShipperId","LocationId"], dsShipper.Tables["Table1"].Columns["ShipperId","LocationId"]);
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 47
Visual Studio - "Windows Form Designer generated code" 2 39
Error on link 14 37
Variable Event ? 3 22
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now