Datarelation handling

31 Oct 2005 6:02 PM

Rob W via DotNetMonster.com

Hi

I've created a datarelation between two tables in a dataset which works fine.
What I need to be able to handle is if there are entries in the child table
that don't match those in the parent then ignore them (the children).

In the code below if no records match in the custSQL query then the
datarelation will fail...

Any ideas would be great.

Dim custSQL As String = " Select distinct(a.retail_customer_Id), a.
retailer_id, Forename, Surname" & _
                                " from web_contact_history a, web_customers b
" & _
                                " where a.retail_customer_id = b.
retail_customer_id " & _
                                " and a.retailer_id = " & rtID & _
                                " and source_system_id = 'CVO' " & _
                                " and list_id = " & cvoID & _
                                " order by a.retail_customer_id "

        Dim vehSQL As String = " select retail_customer_id, Vin, model,
cvo_retailer_id " & _
                                " from web_contact_history " & _
                                " where retailer_id = " & rtID & _
                                " and source_system_id = 'CVO' " & _
                                " and list_id = " & cvoID & _
                                " order by retail_customer_id "

        Dim orphDS As DataSet = New DataSet

        Dim rlConn As New OleDbConnection(connDB)
        rlConn.Open()

        Dim custDA As OleDbDataAdapter = New OleDbDataAdapter(custSQL, rlConn)

        custDA.Fill(orphDS, "Customers")

        Dim vehDA As OleDbDataAdapter = New OleDbDataAdapter(vehSQL, rlConn)
        vehDA.Fill(orphDS, "Vehicles")

        Dim orphDR_cust2veh = New DataRelation("cust2veh", orphDS.Tables
("Customers").Columns("retail_customer_id"), orphDS.Tables("Vehicles").
Columns("retail_customer_id"))
        orphDS.Relations.Add(orphDR_cust2veh)

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200510/1

31 Oct 2005 11:17 PM

Robbe Morris [C# MVP]

You'll need to (and probably should) manage that in
your sql statement for retrieving children. Only
retrieve those that have a parent in table 1.

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net

"Rob W via DotNetMonster.com" <u12359@uwe> wrote in message

news:56abf81f9a1e8@uwe...
Show quote

> Hi
>
> I've created a datarelation between two tables in a dataset which works
> fine.
> What I need to be able to handle is if there are entries in the child
> table
> that don't match those in the parent then ignore them (the children).
>
> In the code below if no records match in the custSQL query then the
> datarelation will fail...
>
> Any ideas would be great.
>
> Dim custSQL As String = " Select distinct(a.retail_customer_Id), a.
> retailer_id, Forename, Surname" & _
>                                " from web_contact_history a, web_customers
> b
> " & _
>                                " where a.retail_customer_id = b.
> retail_customer_id " & _
>                                " and a.retailer_id = " & rtID & _
>                                " and source_system_id = 'CVO' " & _
>                                " and list_id = " & cvoID & _
>                                " order by a.retail_customer_id "
>
>        Dim vehSQL As String = " select retail_customer_id, Vin, model,
> cvo_retailer_id " & _
>                                " from web_contact_history " & _
>                                " where retailer_id = " & rtID & _
>                                " and source_system_id = 'CVO' " & _
>                                " and list_id = " & cvoID & _
>                                " order by retail_customer_id "
>
>
>        Dim orphDS As DataSet = New DataSet
>
>
>        Dim rlConn As New OleDbConnection(connDB)
>        rlConn.Open()
>
>        Dim custDA As OleDbDataAdapter = New OleDbDataAdapter(custSQL,
> rlConn)
>
>        custDA.Fill(orphDS, "Customers")
>
>        Dim vehDA As OleDbDataAdapter = New OleDbDataAdapter(vehSQL,
> rlConn)
>        vehDA.Fill(orphDS, "Vehicles")
>
>
>        Dim orphDR_cust2veh = New DataRelation("cust2veh", orphDS.Tables
> ("Customers").Columns("retail_customer_id"), orphDS.Tables("Vehicles").
> Columns("retail_customer_id"))
>        orphDS.Relations.Add(orphDR_cust2veh)
>
>
> --
> Message posted via DotNetMonster.com
> http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200510/1

1 Nov 2005 5:54 AM

Cor Ligthert [MVP]

Rob,

This should be in my opinion a one time operation. In a relational database
relation can parents have no children, however children can never have no
parents.

So I am a little bit curious about why this is needed, this not curious for
me, however to understand better your problem.

Cor

1 Nov 2005 9:56 AM

Lostdante via DotNetMonster.com

Cor Ligthert [MVP] wrote:

>Rob,
>
>This should be in my opinion a one time operation. In a relational database
>relation can parents have no children, however children can never have no
>parents.
>
>So I am a little bit curious about why this is needed, this not curious for
>me, however to understand better your problem.
>
>Cor

Thanks for the replies..

The problem I have is that I report of two tables, a customer table and a
contacts table. I get parent data from the customer table where there is an
entry in the contacts table. Then children from the contacts table where they
meet criteria. What's happened is that there are contact entries that meet
the criteria which have yet to have customer data added. So I have the
situation of children with no perants. Unfortunately I have no control over
the data integrity and the business want to see all the contact entries
regardless of the customer data.

If there's no way of catching exceptions for a data relation then i'll work
something out in the sql.

--
Message posted via http://www.dotnetmonster.com

1 Nov 2005 10:14 AM

Cor Ligthert [MVP]

Than you can not use a datarelation direct.

You can try to get a distinct table from your children (only the keys).

Than you can probably make a datarelation from that distinct table to what
you call your parents and to the childtable.

I hope that this gives an idea.

Cor

1 Nov 2005 3:18 PM

Lostdante via DotNetMonster.com

Cor Ligthert [MVP] wrote:

>Than you can not use a datarelation direct.
>
>You can try to get a distinct table from your children (only the keys).
>
>Than you can probably make a datarelation from that distinct table to what
>you call your parents and to the childtable.
>
>I hope that this gives an idea.
>
>Cor

Thanks for the replies. I managed to solve this using an outer join in the
parent query so there are no longer any orphaned records and the datarelation
now works.

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200511/1