Solved

Exception while creating relation in Dataset

Posted on 2009-04-09
31
332 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
Comment Utility
code, please.
0
 
LVL 8

Author Comment

by:johny_bravo1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Please look at the SP.
I have used three different tables.
Check wehn the Status='Active'
0
 
LVL 22

Expert Comment

by:p_davis
Comment Utility
is shipperId the primary key for the TMSShipper_Master table?
0
 
LVL 8

Author Comment

by:johny_bravo1
Comment Utility
yes
0
 
LVL 8

Author Comment

by:johny_bravo1
Comment Utility
and even in TMSShipper_MasterMoreContact is set as Foreign Key
0
 
LVL 8

Author Comment

by:johny_bravo1
Comment Utility
sorry that is in TMSShipper_MasterMoreContact , shipperId is foreign key
0
 
LVL 22

Expert Comment

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

Expert Comment

by:p_davis
Comment Utility
like int?
0
 
LVL 8

Author Comment

by:johny_bravo1
Comment Utility
yup,its int.
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Author Comment

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

Author Comment

by:johny_bravo1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
that is the only way u can establish relations considering ur situation
0
 
LVL 8

Author Comment

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

Author Comment

by:johny_bravo1
Comment Utility
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
Comment Utility
sure..:-)..always a pleasure
0
 

Expert Comment

by:mulpuri05
Comment Utility

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
Comment Utility
I m using this for FarPont and not for repeater.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

11 Experts available now in Live!

Get 1:1 Help Now