Solved

Exception while creating relation in Dataset

Posted on 2009-04-09
31
337 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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
 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 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